SQL 2008R2 - SSIS package password issues

Posted on 2011-05-04
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
    LVL 9

    Accepted Solution

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now