RE: Providing a password to an SSIS package

SSC Guru

Points: 99126

May 23, 2018 at 4:35 am

#1991580

MikeRen - Wednesday, May 23, 2018 4:20 AM
Thom A - Wednesday, May 23, 2018 2:29 AM
I have to ask, but why why is your company's policy to not use SSISDB? It is far superior to file system and MSDB deployment methods.
The short answer is - Legacy code.
The longer answer is that my company doesn't have a dedicated DB team and so the role of DBA is shared amongst a team of analysts and programmers. Since there is no 'expert' we try to keep the expectation on the team low. We had a DTS environment which was generally understood, but switching to SSIS meant new knowledge and the simplest path was to go down the file system route. If there are good reasons to use SSISDB then I could make a case for the switch but it'd require some pain on the team.
It's for this reason that I'm looking for the current best practice for storing passwords as I know that we'll be stuck with it for some time.

Might be worth mentioning then that when you deploy to the SSISDB the protection level is removed (not from the project, but the deployed forms). The packages themselves on the server are encrypted, so you can't then query the database to get the sensitive data out.

I realise you say you've not got the expertise, however, SSISDB is a huge improvement on the old filesystem and msdb deployment models. I really recommend you consider it and training is that high either ; really just just the initial set up that needs some knowledge (and make sure you keep a backup of your SSISDB key!!!). After it's set up, the developers will find deploying much more of a "joy" than using a filesystem method. Agent jobs are much easier to manage as well; as the parameter mapping is vastly easier. I really can't think of any cons to using SSISDB over MSDB or filesystem deployment for SSIS.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk