none
sql agent not able to run ssis unless sql agent account is owner of job

    Question

  • I have a server that is sql 2008 R2 running SSIS.

    I have a sql server agent job that is on the same server as SSIS.

    I made the sql server agent account a domain account called it "Bob"

    This "Bob" account is in the local administrators windows group.

    If I create a job and leave the owner as myself ("Tom") the job doesn't run. 

    If I change the owner of the job to Bob then the job runs correctly.

    Also if I change the owner of job to TOm and change the Sql Agent Service Account to run under Tom then the job runs correctly.

    I know I'm missing some setup because I have another server 2008 R2

    and the owner of the job doesn't have to be the same user under the Sql Server Agent Service.

    And both server don't have proxy setup.

    I want to be able to run SSIS thru sql agent without changing job owners and without setting up proxy

    I thought as long as the SQL Server Agent Service account was in the local admin group it should have rights to run the pkg.


    Wednesday, September 25, 2013 6:58 PM

Answers

All replies

  • What protection level are you using for the ssis package? If encrypted with user key it could cause you issues. See http://technet.microsoft.com/en-us/library/dd440760(v=sql.100).aspx for more info.
    Wednesday, September 25, 2013 8:33 PM
  • well I can get the pkg to run if the owner of the sql agent job is set to the same user that runs sql server agent service.

    here is the error..

    Message
    Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.

    but both users Bob adn Tom are in local admin group and in the Sysadmin sql server role.

    like I mentioned above if the owner of the job is the same as the sql agnet service account it runs...

    Wednesday, September 25, 2013 8:42 PM
  • You need a domain proxy account to run the step in the Agent job that call the package or change the job owner to SA account.
    • Edited by Kranthi K Wednesday, September 25, 2013 8:53 PM
    Wednesday, September 25, 2013 8:50 PM
  • Sorry to post yet another link but this explains sql agent security well. http://technet.microsoft.com/en-us/library/ms190926(v=sql.105).aspx
    Wednesday, September 25, 2013 8:53 PM
  • Situation appears to be the same what MS has mentioned in articlet http://support.microsoft.com/kb/2216489/en-us. Please review and see if that helps.


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.

    Wednesday, September 25, 2013 8:56 PM
  • I added the Built In\Administrator group to the sysadmin sql role....now I'm able to run sql job not owned by the account running sql agent service
    Thursday, September 26, 2013 12:36 AM
  • I made the sql server agent account a domain account called it "Bob"

    This "Bob" account is in the local administrators windows group.

    If I create a job and leave the owner as myself ("Tom") the job doesn't run. 

    If I change the owner of the job to Bob then the job runs correctly

    I think this is the desired behavior. When you create the job, you make sure that job runs under sql server agent acct i.e "bob". We do not run jobs under anyone else's acct as a best practice.


    Thanks, hsbal

    Thursday, September 26, 2013 1:37 AM