SQL 2008R2 - SSIS package password issues

I have an established SQL SSIS (2008 R2) ETL package that I am trying to migrate to another hosting location.  In the Connection Manager I have a connection to an external database server.  The user id that I use to log onto the machine that the ETL sits on is also set up as a user in SQL Server - Security with sys admin rights.

If I have a password on this account and try to run a step in the ETL that uses the connection to the external db, the step fails with the error "[Execute SQL Task] Error: Failed to acquire connection "DestinationConnectionOLEDB". Connection may not be configured correctly or you may not have the right permissions on this connection."

When I remove the password on that SQL Server user account and then subsequently do the same on the connection in the Connection Manager in SSIS, the steps and package run fine.

Please help!  Why will it only run with a blank password?  I cannot leave the password blank.

I forgot to add that the Protection Level is set to DontSaveSensitive.
CSC-IMAsked:
Who is Participating?
 
cb1393Commented:
Sounds like it's doing what you are specifying the package to do- it's not saving the sensitive information (connection password). Change the protection level to EncryptSensitiveWithUserKey. This will allow all the sensitive info to be saved with the package (thus enabling the package to be executed) on your local machine.

The package will likely fail on the server when it is uploaded, however. For what I do, I don't need to bother with user keys and such, so I just let SQL Server handle the package security:

1) Select File --> Save Copy of [package name] As...
2) Select/enter the appropriate server/storage information
3) For Protection Level, select Rely on server storage and roles for access control
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.