Saving Passwords in SSIS Packages

Browsing the Microsoft TechNet forums under SQL Server Integration Services, I constantly find posts from users with a common thread: “Why won’t my package run once deployed to the server”. A lot of the issues have to do with the saving of passwords in the Connection Managers. This narrative may help to shed some light on what is going on, and what you can do to fix it.

 DISCLAIMER: While the T-SQL scripts included here are fairly simple, there is no guarantee that they won’t do something unexpected on YOUR system that they didn’t do on MINE. Please read through and understand what is going on with each script before running it. Shame on you if you blindly copy a script off the internet and run it without first checking what it does.

Connection Strings

Every time you add a Connection Manager (whether from a Shared Data Source or not), it has a Connection String associated with it. The connection string may be as complex as the example below: 

Data Source=Server_Name;User ID=MyUser;Password=AbC123xYz;Initial Catalog=Db_Name;Provider=SQLNCLI.1;Persist Security Info=True;

or it may be as simple as a path to a text file: “C:\My_Folder\Text_Data.txt”

In any case, the information stored in the Connection String tells SSIS how and where to connect to get the data you want. In many cases, ‘non SQL’ databases such as Oracle, AS400, or other legacy systems may require a user name and password because those systems do not support (Windows) integrated security.

Passwords at Design Time

So you, as a SSIS designer, dutifully configure your SSIS Data Sources and Connection Managers to save usernames and passwords because, like all of us, you don’t want to keep inputting the password every time you go to test run the package. AND, you make the common assumption (like I did early in my SSIS days) that by clicking the box that says, “Save my password”, the password you supply will magicaly persist with the life of the package, no matter where it goes or where is is deployed.

Such is not always the case. The password will stay with the package, but only under certain conditions.

Package Protection Levels

One of the Package properties is one called Package Protection Level, the default of which is “Encrypt Senstive With user Key”. (Note that I have added the spaces to the names and settings for clarity and readability here.) This default setting basically says this: “SSIS will encrypt passwords and as long as YOU are the one running the package, then SSIS will know how to DECRYPT those passwords because it is the same USER who saved them.” The trouble comes when you deploy this package (with its saved passwords) to the server, and it gets run under the context of some other user.

If you just look at the setting choices above, it is easy to see that there is no really good choices, as “Encrypt All With Password” or “Encrype Sensitive With Password” means that you need to supply a password in order to save, deploy, and run the package. And since we have enough passwords floating around already, nobody wants to start another set just for their SSIS Packages. So that leaves many users to try “Server Storage” but get an error.

The other question that may not have been asked yet is, “What happens if the password changes? Do I need to re-design and re-deploy all my packages?”

The answer lies in the dialog box presented to the user when executing the package from within SSIS or using the DTExec or DTExecUI command, or when scheduling a packag to run via SQL Server Agent. Each of these GUI’s has a tab titled “Data Sources” which lists the Connection Managers and their Connection String WITHOUT any saved password info, regardless of the Protection Level you used when saving the package. The thing to do is check off any Connection Manager in this list that requires a password and manually edit the Connection String to include it.

Additional Disclaimer

Some kind readers of this blog have responded with the comment that when following these steps above, the SQL Agent did not save the changes they made in the DataSources tab. I do remember seeing some similarly strange bahavior a while back, but was never able to pinpoint the cause. I am certainly not discounting those responders who made the claims. I can only offer that I THINK you will have a better chance of success if you do the edits to the DataSources as the LAST thing you do before saving and closing the dialog box. If any reader can verify or discount these suggestions, I would love to hear from them.

Advanced Steps

If you want to take this a step further, review my blog at

http://toddchitt.wordpress.com/2008/06/27/SSIS_configs

This will walk you through the steps to create a Configuration Database in SQL Server that will eliminate the need to manually edit DataSource entries for every job you create.

Good luck, and happy coding!

Todd