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.