patternsqlMinor
Connection Manager in SSIS 2017 Package NOT Saving the Password
Viewed 0 times
thepackagessissavingpasswordmanager2017notconnection
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#216350, answer score: 3
Revisions (0)
No revisions yet.