April 12, 2011 at 5:46 am
Hi all,
Background:
I've developed data warehouse etl processes on a local machine using the package security EncryptSensitiveWithUserKey.
I'm now a the stage where I need to deploy these to a test (then live) server and schedule the package routines.
These packages have been deployed to the file system.
The Problem:
I can successfully scheduled a package that does not contain execute package tasks and it executes successfully, run under a service account proxy, which works fine.
However if I have a package that does contains execute package tasks, then job fails, due to Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information.
what's the best way to cope with the package security? - should I be using EncryptSensitiveWithPassword on all packages and storing the package password in the configuration table (I'm using an SSIS SQL configuration table for things like data connections etc)?
Your thoughts and suggestions would be appreciated
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 12, 2011 at 5:57 am
You definately should move away from protection levels that have user key in their names 🙂
Either use the DontSaveSensitive protection level (if you are using windows authentication) or use encryptsensitivewithpassword.
You do not specify the password in the configuration file, but either specify it in the command line of dtexec or in the SQL Server Agent jobstep (http://msdn.microsoft.com/en-us/library/ms139805(v=sql.90).aspx)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 12, 2011 at 6:17 am
how would the don't save sensitive work in that scenario - I assume it would mean defining the connection security details in the job or command line then?
Also, if I went the route of EncryptSensitiveWithPassword, and specified in the SQL Agent job, would that then pass the details on to the child package?
what better, considering I want to make it straight forward when editing the packages.
I take a look at the link though - thanks.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 12, 2011 at 6:21 am
I usually use DontSaveSensitive with package configurations and I haven't had any real problems (yet).
Regarding the child package: I don't know :blush:
Time to test it out 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 12, 2011 at 6:33 am
sorry, but if your using don't save sensitive where is that info being supplied from when it's run from an SQL agen job? - configuration tables?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 12, 2011 at 6:51 am
dave-dj (4/12/2011)
sorry, but if your using don't save sensitive where is that info being supplied from when it's run from an SQL agen job? - configuration tables?
With DontSaveSensitive, most information comes from package configurations (aka configuration tables). Note that I usually use Windows Authentication, so there is no need to store logins and passwords.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 12, 2011 at 7:03 am
cool. I won't need to change much at all then 😀
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply