Saving Passwords in SSIS Packages

This article explains saving passwords in SSIS Packages when using package deployment model.

When you add a Connection Manager to a SSIS package, it has a Connection String associated with it that looks similar to this:

Data Source=My_Server;User ID=test_user;Initial Catalog=Test_Database;Provider=SQLNCLI10.1;Persist Security Info=True;

or it can be a path to a text file:


The information stored in the Connection String tells SSIS how and where to connect to the data you need.
When you configure your SSIS Data Sources and Connection Managers to save usernames and passwords, even if you click the box that says, “Save my password”, the password will stay saved in the package file only in some cases.

In the Package properties, there is a property called „Package Protection Level“.

If you choose "Don't Save Sensitive" as the Package Protection Level, sensitive information is not written to the package file when you save the package.

The default is “Encrypt Senstive With user Key”. Sensitive information will be encrypted with the user key of the user who created the package.

There is an option “Encrypt All With user Key”, which encrypts the whole package with the user key of the user who created the package, not only passwords.

It means that when the same user who created the package is the one running the package, then SSIS will know how to decrypt the password. When the package runs under the context of some other user (for example in a job step), then there is a problem with decrypting the password, because the User Key would not be the same as the one that created it.

There are options: “Encrypt All With Password” or “Encrypt Sensitive With Password” that mean that you need to supply a password that will be used to encrypt and decrypt sensitive information in the package.

When you import the package, you can change the protection level of the package:

When you want to run the package that has sensitive information encrypted with password as a job step, you need to provide a valid password to decrypt sensitive information in the package. Go into the CommandLines tab and click „Edit the command line manually“ and put in /DECRYPT with the password in the command line. DECRYPT option is shown later in the CommandLines window, but there is no password visible, which means that it is hidden from the UI.

When you right click on the job and script it as CREATE to a new query window, it shows the DECRYPT option with the password you specified when you created the Job Step.

You can edit the command line manually to set the password for the user in the Connection String. The password is saved when you click OK, but it is not shown in the UI later.

When you right click on the job and script it as CREATE to a new query window, it shows the connection password you specified when you created the Job Step.

You can change the SSIS Package ProtectionLevel property to ServerStorage (Rely on server storage and roles for access control) when you save the package to the msdb database from the File System. This option stores the package with all sensitive information and allows access control through SQL Server database roles. If the package was created with the protection level “Encrypt All With Password” or “Encrypt Sensitive With Password”, you need to provide the password that was used to encrypt the data when you are importing the package.

If the post helped you, please share it:
Pin It

4 comments to Saving Passwords in SSIS Packages

  • Fulgenzio Siciliano

    Pretty nice post. I just stumbled upon your blog and wished to say that I have really enjoyed browsing26 your blog posts. In any case I'll be subscribing to your feed and I hope you write again soon!…

  • Perfectly written written content, Really enjoyed studying.

  • Srihari Melika

    Its good post very easy to undestand.

    I have some query If we have 10 different connection managers are available in one package with differnt passwords, do we need to pass them through command line, is there any way to save them in SSIS config table.

  • Den

    Perfect and simple post. Just what I needed. The trick with checking password in a script saved me a ton of nerves and time

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">