Connection Manager in SSIS 2017 Package NOT Saving the Password

Question

Post reply

Connection Manager in SSIS 2017 Package NOT Saving the Password

Sean Perkins

Ten Centuries

Points: 1377
More actions
August 30, 2018 at 3:50 pm

#372246

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.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

You make the login and password parameters and set them in the job.

Sean Perkins Ten Centuries Points: 1377 More actions · Answer 2

Joe Torre - Thursday, August 30, 2018 4:08 PM
You make the login and password parameters and set them in the job.

Thank you for the reply, Joe!

I'm pretty much a n00b when it comes to SSIS, so I'm not sure how to implement what it is that you stated.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.

Smendle Hall of Fame Points: 3969 More actions · Answer 3

Sean Perkins - Thursday, August 30, 2018 3:50 PM
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.

Doing a google search on that error reveals your answers or at least things to try.
Since you state you get this error upon loading the package into Visual Studio yet it runs find in Visual Studio I can only say that you can ignore the error message safely. However assuming you meant that it runs fine in Visual Studio and you get the error when you try to run it via a SQL Agent Job or sqlcmd command line you will need to pass the /DECRYPT parameter in order for the Agent/sqlcmd command to have access to the encrypted information inside your package (in your case the password to the SQL Server in your connection manager)

If I am wrong and in fact you are getting this error message upon loading into Visual Studio then its probably an app. domain permission issue in which case try running Visual Studio as an administrator and see if you still get the error upon loading.