SQL Server 2012–SQL Agent Service Cannot Start After Setup

I had a issue with SQL Server Agent Service that cannot start up after creating a new clean installation of SQL Server 2012 with the latest cumulative update (Build: 11.0.2325.0). My first step was to look at trusty old Windows Application Event Viewer to see what messages get logged when I try to start the SQL Server Agent Service.

 

Problem

In Windows Application Event Viewer the following error message is logged:

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason 2).

The problem is that the default account that is created by the SQL Server installation that is used by the SQL Server Agent Service does not have the correct permissions.

Agent Start Errors Auto fix

 

Solution

The easy solution is to use the SQL Server Configuration Manager to change the SQL Agent Service account to Local Service and then the SQL Agent Service will start up successfully.

My preferred approach is to create a dedicated domain account for example: sql_agent_service. Assign this account to the SQL Agent Service via SQL Server Configuration Manager. When you start the SQL Agent Service you will see that the service start up successfully.

If you go back and view the Windows Application Event Viewer you will see the error again, but additional messages are logged where an advance configuration setting has been automatically changed in SQL Server 2012. The Agent XPs configuration option has been enabled which allows the Agent Service to start up successfully.

Auto fix Set Agent XPs

To make sure the error is gone, clear the Windows Application Event Viewer. Stop and start the SQL Server Agent Service and make sure there are no errors logged again.

By default the advance Agent XPs configuration option is disabled on new installations. You should enable it to make sure the SQL Agent Service work correctly.

Here is the TSQL to enable Agent XPs manually:

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

 


Summary


In summary you should define dedicated accounts for each SQL Server Service up front before you install SQL Server 2012. This will allow you to know which account has permission issues when a SQL Server service cannot start up successfully. Always keep your eye on the Windows Application Event Viewer for SQL Server errors!


Cheerio!

Categories: ,

Disqus