October 26, 2008 at 10:13 pm
---My first post as a newbie to this website/forums---
I am looking for some advice on the correct SQL User permissions for running a SSIS package via SQL Agent Job. Security lock down is of vital importance to me as we run many websites on our servers independently of each other using different SQL User profiles.
My process flow is as follows:
1) User logs into Website Admin section (built entirely in Asp vbscript, upgrading to asp.net in the near future).
2) asp web page runs a stored procedure which triggers the SQL Agent Job to run:
exec msdb.dbo.sp_start_job @job_name = 'SQLtoXML'
3) SQL Agent Job runs a File System SSIS package also named SQLtoXML which outputs an XML file on the server.
All of the above is working perfectly but only when i grant 'sysadmin' priveledges to my standard public SQL User attached to my relevant SQL database.
How can i use the sp_start_job system SP without granting sysadmin to a user but still being able to trigger SP from the asp page?
October 27, 2008 at 5:07 am
You will have a couple of security issues. First - the running of the job. The best way to lock this down is as follows:
- Add the login to the msdb database.
- Add them to db_datareader, SQLAgentReaderRole, and SQLAgentUserRole
- Make them to owner of the job you want to execute
That's about as restrictive as you can be with the job agent. The user will only be able to execute the jobs they own. They will be able to create new jobs as well.
On the SSIS package execution, you will need to use a proxy account that can run the package. Look this up in BOL to learn how to create one.
October 27, 2008 at 4:53 pm
Thanks for the point in the right direction. I can follow most of your logic so far with the permissions you mentioned. Still a little confused with the proxy account versus my local user being set up within msdb?
I assume with SQL Proxies, Credentials are also needed to be set up for my windows domain/user? (as per this MS article)
http://msdn.microsoft.com/en-us/library/ms190698.aspx
Sorry to be a pain but are you able to provide more step by step on these proxy set ups and anything i might need to change within the SSIS package execution or SQL Agent Job?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply