March 9, 2007 at 9:58 am
This is an interesting issue,
I have an SSIS package that is scheduled to run nightly via a SQL Agent Job. I have set up a proxy credential to execute the package as, so under the SQL Job, it "Runs As" these credentials.
The odd thing is, the job will run fine ONLY if I keep a session logged on as the user who saved the package (this could be remote or on the console)
The Package is set to encrypt sensitive data with the user key.
My problem is, I don't want to have to keep a session logged in just to make sure the Job runs, does anyone know a fix for this? Thanks.
March 11, 2007 at 8:32 pm
Have you tried using server storage for encryption rather than a user-specific registry entry? Set your package encryption to 'server storage' when you design the package. Deploy it to a SQL Server and check 'rely on server storage for encryption' when you deploy the package. That should get you round any reliance on user keys, which are only valid for the user who saved the package. I'm assuming you must have a suitable SQL server because you're using credentials and a SQL Server job.
You'll also need to give to the database login which matches your credential used in the 'run as' the db_dtsoperator role in msdb so that it can load and run the package. For finer-grained security (e.g. if several applications have packages stored in the same msdb), give your application user access to both db_dtsoperator role and an application-specific role, then set the package's reader role to the application-specific role. That means that user will be the only one allowed to load and run that package, and the rights granted via db_dtsoperator take care of being able to read the tables where the package is stored and run the system stored procedures to do the loading and running of the package.
An even more basic question (sorry!) is, do you actually have sensitive information in your package at all? Try setting the package encryption to 'do not save sensitive', save, build and deploy it, then try running as some other user (i.e. not the one who saved the package) and if you're not prompted for any info at deploy or run time, chances are you didn't have any sensitive information anyway. By default SSIS appears to assume you have and that you want to secure it via a user-specific key, which isn't a lot of help if you're going to either share the package with other developers and/or deploy it into a production environment. The user key gave me a lot of grief in my early attempts to use SSIS, hence my preference for using SQL Server as the storage location (plus which, your package will get backed up every time you back up msdb). Hope this helps.
Julie
March 12, 2007 at 1:31 pm
Thanks for the reply Julie.
I will definitely be trying the server storage and I will post an update here if it was successful.
March 12, 2007 at 3:18 pm
Hi,
Have you had any luck with this?
I've actually had the same problem - whereby I need to run an SSIS package, via an agent job, as a specific user - I need it to load details from the users profile executing the job - but it only loads the correct profile if the user has a session logged on to the server at the time of execution - other wise it just loads the 'Default User' profile.
I have had no luck in solving the problem.
If you, or anyone else, have any ideas, please post...Any help would be much appreciated..
cheers...
March 12, 2007 at 3:31 pm
What type of information do you need it to load from the users profile?
March 12, 2007 at 6:21 pm
The job uses a 3rd party FTP program, WS_FTP, to transfer files. WS_FTP stores information in the Users Profile about the sites (address, username, password etc). So when I use the WS_FTP command line utility to intitate the request it needs to load this info from the profile...
March 13, 2007 at 3:03 pm
Ok,
Saving the package in "Server Storage" mode worked great for me.
I suggest you try it Scott, but you will have to use a proxy-account to run the package in the scheduled job.
March 14, 2007 at 1:06 am
Hi,
See my post at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=349385. It should help you get through your issue.
Let me know if that helps.
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply