Fixing Startup Failure of SQL Server Agent Service in SQL Server 2005

I recently set up a new SQL Server 2005 + service pack 2 installation in a Windows 2003 Server environment. The services were configured to start using the network service account (NT AUTHORITY\NetworkService). All of the services started successfully, except for SQL Server Agent.

Symptoms

A warning dialog will appear with the following error message:

The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.


In Event Viewer, an error event is logged with the following description:

SQLServerAgent could not be started (reason: Error creating a new session).


The following appears in the SQL Server Agent log file (located at C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT on my system, but this will depend on the particulars of your installation):

2008-01-17 11:04:40 – ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)
2008-01-17 11:04:40 – ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object ‘sp_sqlagent_has_server_access’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-01-17 11:04:40 – ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object ‘sp_sqlagent_get_startup_info’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000]
2008-01-17 11:04:40 – ! [298] SQLServer Error: 229, The INSERT permission was denied on the object ‘syssessions’, database ‘msdb’, schema ‘dbo’. [SQLSTATE 42000]
2008-01-17 11:04:40 – ! [000] Error creating a new session
2008-01-17 11:04:41 – ? [098] SQLServerAgent terminated (normally)

Cause

The account under which the SQL Server Agent runs does not have the permissions required to start the service successfully.

Solution

On my system, SQL Server Agent is configured to run as NT AUTHORITY\NetworkService. You will need to identify the user that runs SQL Server Agent on your system. To do this, open the Services administrative tool, right click on the SQL Server Agent (MSSQLSERVER) service, and choose properties. Click on the Log On tab, and note the user:


I gave this account the sysadmin server role in SQL Server. To do this, open SQL Server Management Studio and log in to the database engine. Drill down into Security, and then Logins. Right click on the user that runs SQL Server Agent, and choose properties. Under Select a page in the top left corner of the properties window, click on Server Roles. Enable the sysadmin role by clicking on the check box in the Server Roles pane, and then click on OK.


You should now be able to start SQL Server Agent successfully.

Advertisements