locked
Change SQL Server Agent service account RRS feed

  • Question

  • How can I change the SQL Server Agent service account from NT SERVICE\SQLSERVERAGENT to a domain user? and most importantly is what permissions I have to give that domain user?

    I know that when SQL Server setup, the installer gives the domain user (that we choose to run a SQL Server service) the least privileges required, but my case is that I chose NT SERVICE\SQLSERVERAGENT and now I want to change it to domain user so I can run successfully jobs that execute something on another SQL Servers.

    If I JUST change the service account in the SQL Server Agent Properties window to a normal domain user, this is not enough and non of the jobs (even that execute local jobs) will work ... 

    So what privileges I should give that domain user to be able to run ALL jobs successfully?

    Thursday, April 30, 2015 9:44 AM

Answers

  • yes Andreas, I used SQL Server Configuration manager.

    I tried to use a normal domain user without giving him any special permissions, then I tried to run a job that executes OPENROWSET qurey, but it didn't work.

    I tried to use a sysadmin domain user as service account for SQL Server agent, and that job with openrowset worked fine.

    Do I have to add that domain user as a login to the SQL Server ? do I have to add him to any SQL Server roles?... in other words do I have to do anything else other than changing the service account to a domain user (that is what I did) in the SQL Server Configuration manager


    So we are talking about OPENROWSET, and not the job-system itself.

    Openrowset opens a connection to another server, and of course SQL Server Agent needs to authenticate somehow. Your local Agent account is sysadmin on the local SQL Server instance. But on the remote instance he may not even be a login.

    Without seeing your connection string I can only guess.

    But for sure it's not the Agent who is the problem.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    • Marked as answer by Butmah Sunday, May 3, 2015 3:39 AM
    Thursday, April 30, 2015 10:48 AM
  • How come Erland! Please enlighten me... The error is talking about the [job owner domain account], NOT [agent service account].

    But the error says that someone is unable to find information about the job-owner account. That someone is reasonably the service account for SQL Server.

    Both accounts can access almost every machine on the network. Even more, [job owner domain account] is sysadmin on the local and target sql servers

    Well, that matters very little, since job-owner is not trying to execute everyhing.

    What you know is that the service account is not able to find information about the job-owner account, and you know that the error that a certain file or device cannot be opened. What come closest at hand is the AD.

    I'm sure a person like you has a solution for my issue.... Thanks in advance :)

    Nah, I know a bit about SQL Server, but this error appears to ultimately be a Windows problem. Active Directory is something I have very little knowledge of.

    Then you also ran into this error when you set sa as the job owner:

    Executed as user: CCCAE\RASO_SSAgent. Login failed for user 'MYDOMAIN\AgentDomainUser'. [SQLSTATE 28000] (Error 18456)  Cannot open database "DB_on_other_Server" requested by the login. The login failed. [SQLSTATE 42000] (Error 4060).  The step failed.

    This is clearly an SQL Server error. The implication of the message is one of:

    1) DB_on_other_Server does not exist.
    2) The account MYDOMAIN\AgentDomainUser has not have rights to access that database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Butmah Sunday, May 3, 2015 3:39 AM
    Friday, May 1, 2015 10:41 AM

All replies

  • ...

    If I JUST change the service account in the SQL Server Agent Properties window to a normal domain user, this is not enough and non of the jobs (even that execute local jobs) will work ... 

    So what privileges I should give that domain user to be able to run ALL jobs successfully?

    That should work just like that. Unless ALL of your jobs access resources that this account really does not have permissions for. For running normal T-SQL jobs nothing special is needed. A backup of course may go to a remote drive.

    So what errors do you get?

    I suppose you used SQL Server Configuration Manager and not Services.msc, right?


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, April 30, 2015 9:51 AM
  • well, depending on what the jobs are doing.. you need to give permission to the domain account to access the folders/location.

    you can also use proxy account for the jobs,if you do not want give sql agent domain account, to have access to all folders, and give that proxy account to access to the folders.

    a typical example: you have txt files in a remote folder and your ssis package, which exectues as sql server agent jobs..needs to import those txt files, then your sql server agent service account should have access to that folder location

    if you use  proxy account for the ssis jod and used - run as "proxy account"- you need to ggive the proxy login permission to access the txt files location.

    so, there is some work for you to set them up. just by changing the service account to domain account does not do any good for job to run except if it is admin account.which does not oobey least privillege rule


    Hope it Helps!!

    Thursday, April 30, 2015 9:57 AM
  • Hi,

    >So what privileges I should give that domain user to be able to run ALL jobs successfully?

    BOL has a created a document stating what all rights should be there can you please refer to it

    SQL Server Agent:1

    (All rights are granted to the per-service SID. Default instance: NT Service\SQLSERVERAGENT. Named instance: NT Service\SQLAGENT$InstanceName.)

    Log on as a service (SeServiceLogonRight)

    Replace a process-level token (SeAssignPrimaryTokenPrivilege)

    Bypass traverse checking (SeChangeNotifyPrivilege)

    Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Thursday, April 30, 2015 10:09 AM
  • Hi,

    >So what privileges I should give that domain user to be able to run ALL jobs successfully?

    BOL has a created a document stating what all rights should be there can you please refer to it

    SQL Server Agent:1

    (All rights are granted to the per-service SID. Default instance: NT Service\SQLSERVERAGENT. Named instance: NT Service\SQLAGENT$InstanceName.)

    Log on as a service (SeServiceLogonRight)

    Replace a process-level token (SeAssignPrimaryTokenPrivilege)

    Bypass traverse checking (SeChangeNotifyPrivilege)

    Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)


    ...
    Those are the permissions that SQL Agent indeed needs. But, if you useConfiguration Manager, which is also documented as the preferred method, you will not have to apply those yourself.

    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, April 30, 2015 10:15 AM
  • Those are the permissions that SQL Agent indeed needs. But, if you useConfiguration Manager, which is also documented as the preferred method, you will not have to apply those yourself.
    Ok, so you mean to say if you use a new domain account with no privilege whatsoever and change Service account using SSCM, SQL Server will automatically grant these rights ?

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, April 30, 2015 10:26 AM
  • yes Andreas, I used SQL Server Configuration manager.

    I tried to use a normal domain user without giving him any special permissions, then I tried to run a job that executes OPENROWSET qurey, but it didn't work.

    I tried to use a sysadmin domain user as service account for SQL Server agent, and that job with openrowset worked fine.

    Do I have to add that domain user as a login to the SQL Server ? do I have to add him to any SQL Server roles?... in other words do I have to do anything else other than changing the service account to a domain user (that is what I did) in the SQL Server Configuration manager


    • Edited by Butmah Thursday, April 30, 2015 10:40 AM
    Thursday, April 30, 2015 10:39 AM
  • Those are the permissions that SQL Agent indeed needs. But, if you useConfiguration Manager, which is also documented as the preferred method, you will not have to apply those yourself.

    Ok, so you mean to say if you use a new domain account with no privilege whatsoever and change Service account using SSCM, SQL Server will automatically grant these rights ?

    ...

    Indeed. That is standard procedure.

    Once in a while you see a server where this goes wrong, and there you reapply manually. But I personally never had to do that since SQL Server 2008


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, April 30, 2015 10:45 AM
  • yes Andreas, I used SQL Server Configuration manager.

    I tried to use a normal domain user without giving him any special permissions, then I tried to run a job that executes OPENROWSET qurey, but it didn't work.

    I tried to use a sysadmin domain user as service account for SQL Server agent, and that job with openrowset worked fine.

    Do I have to add that domain user as a login to the SQL Server ? do I have to add him to any SQL Server roles?... in other words do I have to do anything else other than changing the service account to a domain user (that is what I did) in the SQL Server Configuration manager


    So we are talking about OPENROWSET, and not the job-system itself.

    Openrowset opens a connection to another server, and of course SQL Server Agent needs to authenticate somehow. Your local Agent account is sysadmin on the local SQL Server instance. But on the remote instance he may not even be a login.

    Without seeing your connection string I can only guess.

    But for sure it's not the Agent who is the problem.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    • Marked as answer by Butmah Sunday, May 3, 2015 3:39 AM
    Thursday, April 30, 2015 10:48 AM
  • yes Andreas, I used SQL Server Configuration manager.

    I tried to use a normal domain user without giving him any special permissions, then I tried to run a job that executes OPENROWSET qurey, but it didn't work.

    I tried to use a sysadmin domain user as service account for SQL Server agent, and that job with openrowset worked fine.

    Do I have to add that domain user as a login to the SQL Server ? do I have to add him to any SQL Server roles?... in other words do I have to do anything else other than changing the service account to a domain user (that is what I did) in the SQL Server Configuration manager


    what  credentials are mentioned in there? or does it use trusted connection or sql authetication or specific windows account? what is the error message on the job history

    Hope it Helps!!

    Thursday, April 30, 2015 10:52 AM
  • Thanks Andreas ... yes it seems that my problem is not what I was thinking.

    Stan210, thank you also :) .... my connection is trusted and the following is the error I'm getting:

    The job failed.  Unable to determine if the owner (MYDOMAIN\JobOwnerUser) of job MyJOB has server access (reason: Could not obtain information about Windows NT group/user 'MYDOMAIN\JobOwnerUser', error code 0x6e. [SQLSTATE 42000] (Error 15404)).

    if I change the owner of the job to sa I get the follwoing error:

    Executed as user: CCCAE\RASO_SSAgent. Login failed for user 'MYDOMAIN\AgentDomainUser'. [SQLSTATE 28000] (Error 18456)  Cannot open database "DB_on_other_Server" requested by the login. The login failed. [SQLSTATE 42000] (Error 4060).  The step failed.

    if I keep the owner of the job "sa" and add the 'MYDOMAIN\JobOwnerUser' in 'Run as user' of the step, I get the following error:
    'EXECUTE AS USER' failed for the requested user 'MYDOMAIN\JobOwnerUser' in the database 'master'.  The step failed.

    please note that 'MYDOMAIN\JobOwnerUser' is a sysadmin on both SQL Servers 


    • Edited by Butmah Thursday, April 30, 2015 12:12 PM
    Thursday, April 30, 2015 12:11 PM
  • and your sql server agent is running as mydomain\jobowneruser ??

    I typically get - could not get info about WindowsNT group when I am not on the domain-like if work on office laptop from home and try modifying some settings that requires authentication.  it is valid, because i was not really on domain

    in your case, are you on domain, can you verify that?


    Hope it Helps!!

    Thursday, April 30, 2015 12:17 PM
  • yes I am on domain ... but note that:

    • the MYDOMAIN\JobOwnerUser is different than MYDOMAIN\AgentDomainUser
    • SQL Server Agent service is running using MYDOMAIN\AgentDomainUser
    • and that MYDOMAIN\JobOwnerUser is a sysadmin on the local and target SQL Servers, but MYDOMAIN\AgentDomainUser is not even a login on any of the SQL Servers


    • Edited by Butmah Thursday, April 30, 2015 12:28 PM
    Thursday, April 30, 2015 12:25 PM
  • Since you are trying to solve two problems adding domain access to SQL Agent account (which is as already explained in the previous replies).  

    Are you working with linked servers for OPENROWSET? If so you have to make some changes to the linked server security config. 


    Please click "Mark as Answer" if the post solves your problem - Thanks

    Thursday, April 30, 2015 5:43 PM
  • The job failed.  Unable to determine if the owner (/MYDOMAIN\JobOwnerUser/) of job/MyJOB/ has server access (reason: Could not obtain information about Windows NT group/user '/MYDOMAIN\//JobOwnerUser/', error code 0x6e. [SQLSTATE 42000] (Error 15404)).

    0x6e = 110, and "NET HELPMSG 110" gives:

       The system cannot open the device or file specified.

    My interpretation is that the service account for agent does not have sufficient permission to access Active Directory.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 30, 2015 9:25 PM
  • How come Erland! Please enlighten me... The error is talking about the [job owner domain account], NOT [agent service account]. Both accounts can access almost every machine on the network. Even more, [job owner domain account] is sysadmin on the local and target sql servers (I mentioned this before) .... Please look at my other two tries and analyse them all together... Please re-read my third and fourth posts, you will get a full picture of my case ... I'm sure a person like you has a solution for my issue.... Thanks in advance :)
    Friday, May 1, 2015 5:15 AM
  • How come Erland! Please enlighten me... The error is talking about the [job owner domain account], NOT [agent service account].

    But the error says that someone is unable to find information about the job-owner account. That someone is reasonably the service account for SQL Server.

    Both accounts can access almost every machine on the network. Even more, [job owner domain account] is sysadmin on the local and target sql servers

    Well, that matters very little, since job-owner is not trying to execute everyhing.

    What you know is that the service account is not able to find information about the job-owner account, and you know that the error that a certain file or device cannot be opened. What come closest at hand is the AD.

    I'm sure a person like you has a solution for my issue.... Thanks in advance :)

    Nah, I know a bit about SQL Server, but this error appears to ultimately be a Windows problem. Active Directory is something I have very little knowledge of.

    Then you also ran into this error when you set sa as the job owner:

    Executed as user: CCCAE\RASO_SSAgent. Login failed for user 'MYDOMAIN\AgentDomainUser'. [SQLSTATE 28000] (Error 18456)  Cannot open database "DB_on_other_Server" requested by the login. The login failed. [SQLSTATE 42000] (Error 4060).  The step failed.

    This is clearly an SQL Server error. The implication of the message is one of:

    1) DB_on_other_Server does not exist.
    2) The account MYDOMAIN\AgentDomainUser has not have rights to access that database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Butmah Sunday, May 3, 2015 3:39 AM
    Friday, May 1, 2015 10:41 AM