July 8, 2010 at 7:40 am
[p]Hi,
I have a package that is having "protectionLevel" property value "EncryptSensitiveWithUserKey". This package is having to two ODBC connection managers. If I run this package either in SQL Server 2005 or 2008, It is working perfectly.[/p][p]Now I am changing "protectionLevel" property value to "DontSaveSensitive", because I am not only the user who is using/opening this package. After changed to "DontSaveSensitive" I am receiving error [Execute SQL Task] Error: Failed to acquire connection "Target". Connection may not be configured correctly or you may not have the right permissions on this connection. Here "Target" is one of the ODBC connection manager that I'm using in this package. The same error is coming for other connection manager.[/p][p]I have googled to resolve this error, but I don't get any help. Can anybody help me?[/p]
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
July 8, 2010 at 9:09 am
What kind of login / permissions are your connection managers using?
July 8, 2010 at 11:36 pm
[p]I have attached a screen shot of Target Connection manager. Kindly check that let me know if any other information is required. [/p]
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
July 9, 2010 at 4:00 am
That's your problem right there. You cannot save the password for that login when using "DontSaveSensitive". The point of that protection level is that the package does not save passwords. Since your connection manager is trying to use a specific user name and password, that protection level will cause problems.
Best solution? Create a windows account with least needed permissions, map it to SQL Server. Change the owner of the job that runs this package to that windows user. Make sure the connection managers to use Windows authentication. Leave the package protection level at "DontSaveSensitive".
Or, alternately, use the "EncryptSensitiveWithPassword" or "EncryptAllWithPassword" settings for the protection level. Then you just need the password in order to run the package.
But I prefer the first solution. Many less headaches.
July 11, 2010 at 11:57 pm
[p]Thank you for your reply Brandie, I am following your suggestions.[/p]
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
July 12, 2010 at 5:47 am
Glad I could help. Let me know if you have any more questions regarding this issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply