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

By: Daniel Farina   |   Updated: 2020-02-20   |   Comments (6)   |   Related: > 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.

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)."

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.

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.

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.

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.

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.

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

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

Next Steps

• In the following tip you will see the procedure used to enable the Agent XP's configuration option covered with more detail: How to start SQL Server Agent when Agent XPs show disabled.
• It could be the case that you are handling the SQL Server Agent log files as a way of keeping job history. In this tip you will see a more convenient way to do so: Retaining SQL Server Job History.
• If you notice that folder permissions are changing without you being aware or files are being deleted I suggest you enable auditing following the instructions of this tip: Auditing Critical Windows Files and Folders for SQL Server.
• To check the Windows Event Log you can also use PowerShell. Take a look at this tip if you don't know how: Using PowerShell to access event logs for SQL Server.
• In case you need to move the SQL Server Agent logs to another location you can use the following tip as a guide: How to Change the SQL Server Agent Log File Path.
• If you are new to SQL Server and need to expand your knowledge about SQL Server Agent you can start with this tip: Getting Started with SQL Server Agent - Part 1.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips