SQL Server Agent Service Won't Start Due to Access Denied


By:   |   Updated: 2020-02-20   |   Comments (3)   |   Related: More > SQL Server Agent


Problem

I was working with my test environment and I noticed that SQL Server Agent service was down.  When I attempted to start the service, it fails with the following error: "Failed to initialize SQL Agent log (reason: Access is denied)". In this tip I will show you the steps I took to solve this problem

Solution

As part of my work in writing tips, I have to perform several tests on my environment. Those tests go from the basics of making a set of related tables to creating a sample database to crashing the system databases and finding out how to recover the affected instance.

When I tried to start SQL Server Agent service from SQL Server Management Studio it showed a nasty error message saying "sql server blocked access to procedure sp_set_sqlagent_properties of component 'Agent XPs' because this component is turned off…" that you can see on the screen capture below.

Error message starting SQL Server Agent.

Troubleshooting SQL Server Agent Service Won't Start

The message was telling me that the Agent XPs Server Configuration Option was turned off. This option enables a set of extended stored procedures that allows SSMS to show the SQL Server Agent node. Even though in books online it says that this option is enabled automatically when you start the SQL Server Agent service, this doesn't seems to be the case.

So, I proceeded to check the status and enable it manually with the following code.

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Agent XPs';  
GO
EXEC sp_configure 'Agent XPs', 1;  
RECONFIGURE
GO
EXEC sp_configure 'Agent XPs';  
GO

But when I tried to start the SQL Server Agent service, the same error was shown.

The Event Viewer

I opened the Event Viewer to see the application log, which is the place where applications write errors, to see if I could get more information about this issue.

As you can see in the image below there is a record with an error that says "Failed to initialize SQL Agent log (reason: Access is denied)."

Event Viewer shows the error in depth.

I saw it was a permissions related problem, but permission to what?

Using Sysinternals Process Monitor

In order to answer that question, I decided to use one of the tools provided in the Microsoft Sysinternals Suite. This is like the Swiss Army Knife for Windows, a free set of tools which can be downloaded from here.

Amongst the tools provided, I opted to use the one named Process Monitor (you can download it separately if you don't want to download the full set of tools) which is an advanced monitoring tool that shows real-time file system, Registry and process/thread activity.

After starting Process Monitor it starts capturing events so I switched to SSMS to start the SQL Server Agent service, and when it failed to start, I returned back to Process Monitor and stopped the event capture. This tool captures all events from all processes so I had to filter by process name equal to SQLAGENT.EXE.

As you can see in the next screen capture, the SQLAGENT.EXE process tried to open file "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT" for reading/writing. The SQLAGENT.OUT file is where SQL Server Agent stores its error log. Don't get confused by the fact that the "Operation" column says CreateFile, that is the name of the Windows API function used to create a file handle. A file handler is the identifier used by Windows to reference a file.

Sysinternals Process Monitor shows access denied on SQL Server Agent Log file.

Basically, the SQL Server Agent service was unable to start because it can't access the log file. This is because the SQL Server Agent service account doesn't have write permission to folder "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\".

Checking SQL Server Agent Service Account

In order to check the permissions for the SQL Server Agent service account, first we need to know the account used by SQL Server Agent service. There are several ways to determine that, the easiest is to open SQL Server Configuration Manager and take a look at the value in the "Log On As" column. Remember that you can have more than one SQL Server Agent service in case your system has more than one instance of SQL Server running, so it is wise to check which SQL Server instance host each SQL Server Agent service is using by looking at the text enclosed in parenthesis.

As you can see in the image below, the SQL Server Agent service is using the virtual account NT Service\SQLSERVERAGENT.

Using SQL Server Configuration Manager to see the Agent service account name.

Another option, if you have VIEW SERVER STATE permissions on the server, to find the service account name used you can query the sys.dm_server_services dynamic management view.

SELECT  servicename,
        startup_type_desc,
        status_desc,
        service_account
FROM    sys.dm_server_services;

The next image shows the execution of the query above.

Querying the sys.dm_server_services dynamic management view to see the Agent service account name.

Configure Folder Permission

Now that we know the service account used by the SQL Server Agent service and where the log file location, we need to check if the account has read and write access to the folder used by the Agent to keep its log.

In order to do so, we can take a look at the Security tab for that folder's properties. As you can see in the next image, the SQL Server Agent service account is not listed.

Checking permissions for the Agent account in the log folder.

To add the account, we must click on the Edit button and then add the SQL Server Agent service account. As a reminder, be sure that you select your current server as the Location.

Adding SQL Server Agent service account to the folde rusers.

Finally assign full control to the SQL Server Agent service account and then save the changes.

Granting Full control to the Agent account on the log folder.

Finally, when I started the Agent service it started without any issue.

Next Steps


Last Updated: 2020-02-20


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Comments For This Article




Thursday, February 20, 2020 - 6:25:02 PM - Jimbo99 Back To Top (84682)

Well at least it wasn't one of your co-workera or managing director sabotaging & locking out the administrator's login in the middle of the night.


Thursday, February 20, 2020 - 12:00:33 PM - Randy in Marin Back To Top (84675)

Thanks for the info.  A SQL setup repair will re-establish missing permissions.  However, if something like domain policy is removing the permissions, then it will just repeat.  A sign of this is the service starting and then not restarting later after policy has removed rights.  Or it might be a one time event if the policy has been updated or the machine was moved - after the install - from an "unfriendly" OU to one with policy friendly to SQL Server.  The service SIDs are not well known for SQL instances, but they are the same accross machines - based on the service name.  If the admin creating policy is doing so from a machine where the account is defined, it will be available.  Our dev environment is still zapping virtual accounts...switching to the network service account is a work around.  

Something I wonder about...the documentation indicates that the service sids have specific rights.  Does this mean they must have these rights to be supported?  If we specifiy another account for the service to allow domain resource access, can this account have all the rights required (not ideal) with those rights them removed from the virtual account?  If the service SID is supposed to have the rights, then the DBA needs to instist that policy be defined to support the support requirements.   


Thursday, February 20, 2020 - 10:41:29 AM - Kyle Back To Top (84672)

Hooray !! I understood all of that !!  :) 



download





Recommended Reading

Running a SSIS Package from SQL Server Agent Using a Proxy Account

Querying SQL Server Agent Job Information

Querying SQL Server Agent Job History Data

SQL Server Agent Job Schedule Reporting

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables








get free sql tips
agree to terms


Learn more about SQL Server tools