Problem with agent service accounts?

  • Hey guys. Ive never had this problem with SQL2008 /R2. But I cant get the SQL Agent to log in with anything but local system or a local user that has admin rights on the server. Generally, I would make a SQL_svc_agent_user and grant no rights and and using the sql installer it would grant any permissions including log on a service and add NT SERVICE\SQLSERVERAGENT as SA.

    Now even with all that in place this user cannot log on in 2012. Even if I granted SQL_svc_agent_user SA rights in Management Studio this still does not work. Seems like something changed in 2012? What am i missing here? The service account for SQL runs just fine but not the agent. This is a local account, but the machine is connected to a domain. Its driving me nuts!:hehe:

  • if you look at services in which account SQL Agent is running under?

    Regards
    Durai Nagarajan

  • What happens when you try setting the SQL Agent to a domain account in SQL Server Configuration Manager? Does it succeed or does it give you an error? If it produces an error can you post it?

    Joie Andrew
    "Since 1982"

  • durai nagarajan (8/7/2013)


    if you look at services in which account SQL Agent is running under?

    Currently its running under LocalSystem. But I have made a local account for it and entered during setup. The service account stuck but the agent account crapped out. Process monitor did give me some access denied entries when I try to start it but I was under the impression the sql configuration manager would take care of it. Unless this is something new in SQL Server 2012 does the agent account need to belong to the admin group?

    Regarding the domain account. I don't have one this is a client system we are assisting with. I may end up leaving it as LocalSystem if its too much of a hassle. For what its worth this is also on Server 2012 and I already disabled user account control. The installation was performed with an admin added to the administrator group. Not the main local Admin.

  • When you try and start the SQL Agent service under a local account and it fails do you get any logon failures in the security log in Event Viewer? If so can you post them? Also, can you verify that the local account that you want to start the service as has the "Log on as a Service" right? This should have been set when you configure the account in SQL Server Configuration Manager, but it is possible that this did not happen.

    Joie Andrew
    "Since 1982"

  • Joie Andrew (8/7/2013)


    When you try and start the SQL Agent service under a local account and it fails do you get any logon failures in the security log in Event Viewer? If so can you post them? Also, can you verify that the local account that you want to start the service as has the "Log on as a Service" right? This should have been set when you configure the account in SQL Server Configuration Manager, but it is possible that this did not happen.

    It used to say access denied. It now says: The SQL Server Agent (MSSQLSERVER) service failed to start due to the following error:

    The service did not respond to the start or control request in a timely fashion. Think that is result of adding full access to user NT SERVICE\SQLSERVERAGENT in path data\MSSQL11.MSSQLSERVER\MSSQL\Binn

    Just confirmed User Rights Assignment > Log on as Service . The user SQL_agent_svc_usr does exist on the list.

  • Process Monitor also shows a few ACCESS DENIED entries as I attempt to start the service. Here is one directly related to the agent:

    Process Name: SQLAGENT.EXE

    Operation: CreateFile

    Path: D:\SQLData\data\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlsvc.dll

    Result: ACCESS DENIED

    Desired Access:Read Data/List Directory, http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gifExecute/Traverse, Synchronize

    Disposition:Open

    Options:Synchronous IO Non-Alert, Non-Directory File

    Attributes:n/a

    ShareMode:Read, Delete

    AllocationSize:n/a

  • does NT AUTHORITY\NETWORK SERVICE has access to msdb with all sqlagent permissions.

    Regards
    Durai Nagarajan

  • durai nagarajan (8/7/2013)


    does NT AUTHORITY\NETWORK SERVICE has access to msdb with all sqlagent permissions.

    I have granted NT AUTHORITY\NETWORK SERVICE the sysadmin server role. This should give it rights to everything.

    However, it still will not start and gives the same ACCESS DENIED in process monitor to the path D:\SQLData\data\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlsvc.dll

  • Two questions:

    1) Does the SQL Server error log show a login failure for that login when you try to start the Agent?

    2) The "Access Denied" error you received looks more like a file system permissions issue. Have you checked file systems permissions?

    Cheers!

  • Jacob Wilkins (8/8/2013)


    Two questions:

    1) Does the SQL Server error log show a login failure for that login when you try to start the Agent?

    2) The "Access Denied" error you received looks more like a file system permissions issue. Have you checked file systems permissions?

    Cheers!

    SQL error log shows nothing.

    I have tried setting that local user full rights to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent - did nothing

    I have tried setting D:\SQLData\data\MSSQL11.MSSQLSERVER\MSSQL\Log full rights to that local user - did nothing

    For what its worth I have tried this on a second Server 2012 machine and it behaves the same. Is anyone here actually running this combo successfully ? Windows Server 2012 + SQL 2012 ? Something must have changed but I cant figure out what.

  • I have some Windows server 2012 + SQL Server 2012 combinations both in the lab and in production, and they work without issues (well, without this issue :-D).

    I actually just created a new local user on the lab machine, left it as a member only of the Users group, stopped the Agent, changed the Agent to use the new user, and started the Agent with no issues.

    Just to make sure I'm clear on your situation:

    1) You are currently only receiving the generic "Service failed to start in a timely fashion" error?

    2) Does this occur no matter what account you specify for the Agent?

    In the meantime I'll see if I can recreate your issue, but I've had no success so far.

    Cheers!

  • Jacob Wilkins (8/9/2013)


    I have some Windows server 2012 + SQL Server 2012 combinations both in the lab and in production, and they work without issues (well, without this issue :-D).

    I actually just created a new local user on the lab machine, left it as a member only of the Users group, stopped the Agent, changed the Agent to use the new user, and started the Agent with no issues.

    Just to make sure I'm clear on your situation:

    1) You are currently only receiving the generic "Service failed to start in a timely fashion" error?

    2) Does this occur no matter what account you specify for the Agent?

    In the meantime I'll see if I can recreate your issue, but I've had no success so far.

    Cheers!

    1) Yes that is correct

    2) Just created a user called testagent. Same issue . Service failed to start in a timely fashion.

    I agree with you and Ive done this on many times on other servers. I don't know why its not working here. Argg.. But this is the first environment i work with thats 2012.

  • Using a domain user account for SQL Agent works fine in SQL 2012 (and SQL 2005, 2008, 2008R2, 2014), you just have to give it the correct rights.

    It needs:

    Act as part of the operating system

    Adjust memory quotas for a process

    Bypass traverse checking

    Log on as a batch job

    Log on as a service

    Replace a process level token

    It does not need to be a member of the windows Administrator group unless you enable the 'Automatically start SQL Server / SQL Agent if it fails'.

    The Agent account will also need to be a Sysadmin account in SQL Server.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • EdVassie (8/12/2013)


    Using a domain user account for SQL Agent works fine in SQL 2012 (and SQL 2005, 2008, 2008R2, 2014), you just have to give it the correct rights.

    It needs:

    Act as part of the operating system

    Adjust memory quotas for a process

    Bypass traverse checking

    Log on as a batch job

    Log on as a service

    Replace a process level token

    It does not need to be a member of the windows Administrator group unless you enable the 'Automatically start SQL Server / SQL Agent if it fails'.

    The Agent account will also need to be a Sysadmin account in SQL Server.

    Thanks but I am not using a domain account. Using a local user.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply