Connection Manager in SSIS 2017 Package NOT Saving the Password

Question

We're using Visual Studio 15.7.2 and using SSIS on SQL 2017 (14.0.3029.16).

We built a connection manager to a 3rd-party, vendor managed SQL Server 2008 R2 (10.50.6529.0). Our only allowed connections to this server are via SQL Login, so Windows Authentication is a no-go.

We can run the package inside of Visual Studio/SSDT just fine, the package executes and completes successfully as we'd expect.

When we open the package in SSDT we're met with error immediately:

Upon looking at Connection Managers we're met with the following icon:

When we open the connection manager, this is what we see (password blank, should be *********):

The package is configured to run (via job) at 8:45 pm Eastern and it fails every time. We get a plethora of errors that basically state we can't connect to the 3rd-party server. Investigation shows that the password isn't being saved so we get denied at the front door!

Has anyone ran into this before? What options do I have to rectify? I'm somewhat new to SSIS, so I'm learning a lot of hard lessons fast.

Answer 1

By default SSIS saves and encrypts sensitive data (passwords, etc.) with something called UserKey. This means that no-one else is able to use the connection with saved password other than the person who previously saved the SSIS-package.

This can be changed through the SSIS package properties -> ProtectionLevel. You should select "EncryptAllWithPassword" and set a password to "PackagePassword" property and save the package. Now when you open the package next time, SSDT will ask you to enter the password. When entered, connection should be working just fine.

Answer 2

The password for a SQL Login is Sensitive so it will be omitted from a connection string and thus not saved. If you deploy your package to the SSIS catalog you can use an environment variable with the Sensitive property for your password, then map this to the corresponding property in the connection manager. See the following article from Andy Leonard for more details on how to implement this for a SQL Login. If you want to configure the SSIS catalog see Level 18 of this series. You also mentioned that you're relatively new to SSIS, so I'd recommend working through this stairway as well.

http://www.sqlservercentral.com/articles/Stairway+Series/121490/

Answer 3

The problem is different if you are planning on a package deployment. You will need to use a configuration file to provide various inputs and settings which might change from one development, testing or deployment environment to another.

When using package deployment password saving is only enabled if a UserKey or Password is used to encrypt the package. UserKey is for spies or those who want to deploy using a secure identity that carries through from one environment to the next, but it's usage is clunky. Password encryption is a little less clunky, but it would imply that the package would have to be managed to ensure that secure Production passwords would not be available to say, developers not authorized to know them, after the package is first promoted.

One of the problems here is its not clear at which level the password is stored. One would assume that its an encrypted property of the connection manager, but if that were the case, I dont know why it keeps attempting with a bad password ..