March 10, 2011 at 8:33 am
I have a package that takes information from on DB to another DB, both are SQL Servers. In my connection manager I have setup the Authentication as SQL Server Auth, with a generic user to the server. In addition to that, I have the pack Security property Protection Level set to EncryptSensitiveDataWithUserKey. So when I deploy to the server, the package throws an error stating that it cannot decrypt the password. Correctly so because I have the protection level set to encrypt on the user key.
So the big question is, what is the best Protection Level to use when deploying to a server, utilizing this setup.
March 10, 2011 at 11:31 am
Ideally you should have the package use Windows Authentication to reach both SQL Servers and set the ProtectionLevel to DontSaveSensitive.
If that is not an option and you must use a SQL Server login...it's been a while but if memory serves you can use EncryptSensitiveWithPassword or EncryptAllWithPassword and provide the package password to the SQL Server Agent job when scheduling the package.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 11, 2011 at 8:29 am
I second the suggestions from opc.three, but there's another alternative:
the protection level Server Storage.
It's there for a reason 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply