none
SQL Server Agent fails to start when run under NT Service\SQLSERVERAGENT

    Question

  • new SQL2012 installation

    for SA account using standard AD account...

    for Agent left default NT Service\SQLSERVERAGENT

    After reading about use of right account with appropriate permissions I would like to ask:

    shouldn't SQLSERVERAGENT account created by SQL installation have appropriate rights for running smoothly.

    Sure the simplest way I can run Agent under the same standard AD account that is member of local admin. MS says that it is not recommended but there are some agent tasks that will require Local Admin rights. So to simplify the life I can use the same account that will be pretty secure.

    Just wanted to know why NT Service\SQLSERVERAGENT cannot do the things right. What is the goal of this account if it is incapable to do AGENT's job. Is there a way to add missed by installation needed permissions for this account?

    For now I have this:


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Wednesday, February 22, 2017 7:37 PM

Answers

  • The account does not need right on sql server and does not need to be an admin account.

    Which account are you talking about, Hilary? Not 'NT Service\SQLSERVERAGENT' account, right?

    pob579:
    Can you verify the SQLAgent Service startup account has execute privilege to this file path & executable?
    <MSSQLPATH>\MSSQL\Binn\sqlagent.exe

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by pob579 Wednesday, March 1, 2017 2:51 PM
    Thursday, February 23, 2017 7:03 PM
  • For 'NT Service\SQLSERVERAGENT' account, you don't need to enter a password, just leave it blank.

    Just right-click the <MSSQLPATH>\MSSQL\Binn\ folder where sqlagent.exe resides, then choose > Properties > Security, then check to make sure that 'NT Service\SQLSERVERAGENT' account has execute permission within the folder.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, February 23, 2017 8:13 PM
    • Marked as answer by pob579 Wednesday, March 1, 2017 2:51 PM
    Thursday, February 23, 2017 8:12 PM

All replies

  • I think error 5: Access is denied means a file system error. Can you find the exact error message in Windows System Event log and post the full error here. That should tell us what drive/directory the account is being denied access to.

    BTW, 'NT Service\SQLSERVERAGENT' is a member of Sysadmin, fixed server role in the MSSQLSERVER instance, right?

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Wednesday, February 22, 2017 8:40 PM
  • Phil,

    thanks for pointing me out to open Evenvwr :)

    I was so sure that it is SQL related, that did not look in event viewer... Instead checked ERRORLOG and started by very first message when started the Agent from confman :

    "The request failed or the service didn't respond in a timely fashion..." 

    First search of this message brought couple of solutions related to SQL 2008 - all of them were related to account permissions running Agent service.

    That is why I asked here about NT Service\SQLServerAgent... And you just pointed out that it is sysadmin so should not be a problem in SQL2012.

    So when simply checked Eventvwr I found event 7000. Made quick search... you will not believe but the issue is in VMWARE setting. I cannot not try it today but I am pretty sure it is it.


    For now here is link were the problem described:

    http://jonmorisissqlblog.blogspot.ca/2013/04/event-id-7000-sql-serverservice-failed.html

    and here is the screamshot of my error that is the exact in the blog. Will update after trying.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Wednesday, February 22, 2017 10:53 PM
    Wednesday, February 22, 2017 10:50 PM
  • Right click on SQL Server agent in SQL Configuration Manager and change the account to a windows one that you know the password for. This account will be granted all the rights it needs to run as a service and does not need to be a login in SQL Server.
    Thursday, February 23, 2017 12:15 AM
  • The error message 0x80070005 is error access denied. This is a permissions issue. Configuring SQL Server agent to use the domain or a local machine account should give it all the rights it needs. Post back here if it does not work.
    Thursday, February 23, 2017 6:17 PM
  • so, the advise that helped to many (even some skeptical) to solve the exact same problem Event 7000 by adjusting HOTPLUG in VWWare VM didn't help me...

    http://jonmorisissqlblog.blogspot.ca/2013/04/event-id-7000-sql-serverservice-failed.html

    I followed Hilary's advise and change SQLSERVERAGENT account to AD standard account that is in Local admin group and is sysadmin.

    The problem solved.

    But a "little" question is there why NT Services\SQLSERVERAGENT account that is default for running Agent service is not able to run. And as mentioned by Phil it is sysadmin...

    I really don't mind and don't think that is a big deal. I read MS doesn't recommend to use Admin rights for Agent,

    but at the same time mentions that there are cases when Admin rights are necessary for Agent.

    Does somebody has default SQLSERVERAGENT account for Agent Service?

    ************************************

    And just in case if somebody can/wants to look in full Event 7000 log when running under SQLSERVERAGENT and fails:

    Log Name:      System
    Source:        Service Control Manager
    Date:          2017-02-23 11:06:44
    Event ID:      7000
    Task Category: None
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      Computer_Name
    Description:
    The SQL Server Agent (MSSQLSERVER) service failed to start due to the following error:
    Access is denied.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" />
        <EventID Qualifiers="49152">7000</EventID>
        <Version>0</Version>
        <Level>2</Level>
        <Task>0</Task>
        <Opcode>0</Opcode>
        <Keywords>0x8080000000000000</Keywords>
        <TimeCreated SystemTime="2017-02-23T16:06:44.457139800Z" />
        <EventRecordID>10299</EventRecordID>
        <Correlation />
        <Execution ProcessID="560" ThreadID="3908" />
        <Channel>System</Channel>
        <Computer>Computer_Name</Computer>
        <Security />
      </System>
      <EventData>
        <Data Name="param1">SQL Server Agent (MSSQLSERVER)</Data>
        <Data Name="param2">%%5</Data>
        <Binary>530051004C005300450052005600450052004100470045004E0054000000</Binary>
      </EventData>
    </Event>


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Thursday, February 23, 2017 6:29 PM
    Thursday, February 23, 2017 6:27 PM
  • The account does not need right on sql server and does not need to be an admin account.

    In fact it is a security risk to give it these escalated permissions. Changing SQL Server Agent account using SQL Server Configuration Utility will give it all of the rights it needs.

    Now, you might want to run it under an elevated account, but the correct way is to use a generic user account and then use job proxies to give individual jobs the permissions they need to do work on SQL Server, machine resources, or network resources.

    Your question as to why this failed is a good one. It was a permissions issue as we see from the 0x80070005  error message. How this happened and what the issue was is difficult to detect at this point.

    Thursday, February 23, 2017 6:39 PM
  • :) you read my deleted post where I mentioned the error when tried to change SQLSERVERAGENT to a standard AD account...

    Looks simple as you explain, but I felt that standard account is not capable at all to do things and services even not allowed to assign it.

    Could it be some corruption?

    As you see I posted detailed 7000 event log. Is something there?

    And what can you tell about SQLSERVERAGENT account created by SQL? Isn't it suppose to work smoothly?


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 23, 2017 6:49 PM
  • The account does not need right on sql server and does not need to be an admin account.

    Which account are you talking about, Hilary? Not 'NT Service\SQLSERVERAGENT' account, right?

    pob579:
    Can you verify the SQLAgent Service startup account has execute privilege to this file path & executable?
    <MSSQLPATH>\MSSQL\Binn\sqlagent.exe

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by pob579 Wednesday, March 1, 2017 2:51 PM
    Thursday, February 23, 2017 7:03 PM
  • "Which account are you talking about, Hilary? Not 'NT Service\SQLSERVERAGENT' account, right?"

    I am quite incorrect here - sorry about that.

    Thursday, February 23, 2017 7:36 PM
  • Phil,

    in order to try Hilary's suggestion I removed NT Services\SQLSERVERAGENT account.

    I tried a standard AD account that didn't work (if I understand correctly Hilary's point it should).

    Then just to make it work I added AD account that is in local Admin group. This works. But sure I would like to have in proper way. Not admin priviliges.

    Now how I can put back SQLSERVERAGENT. I don't know the password for it.

    And please guide me how to check:

    Can you verify the SQLAgent Service startup account has execute privilege to this file path & executable?
    <MSSQLPATH>\MSSQL\Binn\sqlagent.exe

    When right click on sqlagent.exe there are no place for checking privileges. Probably I am looking in wrong place.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 23, 2017 7:55 PM
  • For 'NT Service\SQLSERVERAGENT' account, you don't need to enter a password, just leave it blank.

    Just right-click the <MSSQLPATH>\MSSQL\Binn\ folder where sqlagent.exe resides, then choose > Properties > Security, then check to make sure that 'NT Service\SQLSERVERAGENT' account has execute permission within the folder.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, February 23, 2017 8:13 PM
    • Marked as answer by pob579 Wednesday, March 1, 2017 2:51 PM
    Thursday, February 23, 2017 8:12 PM
  • 1. SQLSERVERAGENT was not there at all. Added it.

    2. changed the account for service (no password worked OK :) )

    3. The event 7000 access denied is always there. I tried to restart the service gradually adding Write, Modify and then Full Control.

    The server was restarted couple of times. As you can see service could not be started.

    I feel we are close to a solution :)

    What would be the next recommendation?

    At least I checked that AD user in Local Admin group can start the service. So it is definitely RIGHTS question.

    Thanks for you time...


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 23, 2017 10:15 PM
  • UPDATE:

    as mentioned, when tried to run the Agent service as AD user with local ADMIN rights the problem was not appear.

    As last straw I opened the case with Microsoft.

    After FULL verification of GPOs and rights, process monitor was launched during service start under acoount with admin rights and under SQLAGENTSERVER account.

    After comparison of two logs, another executable SQLAGENT.exe.config under BINN directory had Denied. When checked, the accound was not included at all on executable permissions.

    And that's after adding FULL rights to BINN folder (very first Phil's recommendation to check rights on BINN folder).

    Added account to the file with the hope that the problem solved. Checked effective permissions - FULL.

    THE PROBLEM was there...

    At this moment I took the BEST :) decision in this situation. Reinstalled all from scratch. Previous server was installed from template. So this time I used ISO.

    Everything is just FINE. The app application is scheduled for tomorrow. So I am fine.

    Actually Phil was on right track... I think there was some corruption on file system level.

    Appreciate the help.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Wednesday, March 1, 2017 2:50 PM