SQL 2008R2 - SSIS package password issues

Posted on 2011-05-04
Medium Priority
Last Modified: 2013-11-10
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.
Question by:CSC-IM
1 Comment

Accepted Solution

cb1393 earned 500 total points
ID: 35690597
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question