May 20, 2010 at 8:55 am
We have an SSIS package that exports to a package we do not manage. we have a service account to that database server and that user and password is setup in connection manager.
we ran into a problem others have encountered saying the password de cryption failed when the SSIS is run from a job. I have come to understand that the password encryption is based on the current login context. as such I had to modify the SSIS while logged in as the SQL Agent user and update the SSIS so the password was saved under the correct login context. Then of course the job ran fine and the passwrod error went away. I have done quite a bit of searching on this problem and I have seen that this is not a unique problem and I have read quite a bit of complaints about this but what I have not seen is if anyone has found a way to work around this problem.
Any thoughts or random rants are welcome. I will be happy to rant on this myself as I think this will be most inconvient to have to always edit this package as the SQL agent account.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 20, 2010 at 10:23 am
Most people solve this by using package configurations.
I personally NEVER save any passwords into the package.
I have seen some hybrid methods where an encrypted password is passed by the job step, stored in a dtsConfig file or stored in a table and then decrypted at run-time. I have also seen the passwords in clear text in those same three locations. In the case of config files they are usually stored in a location with limited access by anyone other than the SQL account and the DBA team.
Most often I recommend using trusted connectivity as much as possible since it does away with the need to store passwords at all.
CEWII
May 20, 2010 at 10:57 am
Elliot, Thanks for the responce. I had given some consideration to setting up a config file. certainly the best option would be to add a trusted connection but unfortunatley the people managing that server are very set against that idea. I wish I had more influence over that process but that is the way it goes.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 20, 2010 at 2:15 pm
It would be interesting to know their rationale for resistance to trusted connections, since it simplifies the whole process and is usually a lot more secure.
CEWII
May 20, 2010 at 2:40 pm
It can be summed up by simply saying it is a result of legacy ideas.
I don't know who else has to deal with such things but I often hear "That is not how we did that on the Main Frame"
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply