PRB: SQL Server Agent does not start and displays error 18456

Article translations Article translations
Article ID: 237604 - View products that this article applies to.
Expand all | Collapse all

Symptoms

The SQL Server Agent Service does not start and you may see the following error message in the Windows Event Viewer:
SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).
Additionally, if you attempt to start the SQL Server Agent Services from a command line (for example, C:\MSSQL7\BINN\>sqlagent -c -v) you may see the following error messages in the C:\MSSQL7\LOG\SQLAGENT.OUT file:
7/13/99 2:07:43 PM - ? [129] SQLServerAgent starting under Windows NT service control
7/13/99 2:07:43 PM - ! [298] SQLServer Error: 18456, Login failed for user 'DomainName\UserName'. [SQLSTATE 28000]
7/13/99 2:07:43 PM - ! [000] Unable to connect to server; SQLServerAgent cannot start
7/13/99 2:07:44 PM - ? [098] SQLServerAgent terminated (normally)

Cause

The BUILTIN\Administrators login does not have access to SQL Server or the BUILTIN\Administrators login has been removed from SQL Server, and the SQLAgent service is starting under the LocalSystem account.

The issue does not occur if the SQLAgent service is starting under a domain or computer account that is listed separately in SQL Server Enterprise Manager and that has System Administrators (sysadmin) privileges.

Workaround

To put the BUILTIN\Administrators account back into SQL Server, start the SQL Enterprise Manager. Go to \Servername\Security\Logins and grant access to the BUILTIN\Administrators user.

If you really want to deny access to SQL Server by Windows NT administrators, you can remove the BUILTIN\Administrators account. First add the login account you are using for the SQL Agent Service as a SQL Server login and assign the System Administrator (sa) role to that account. Then either revoke the System Administrators (sysadmin) role permissions from the BUILTIN\Administrators account, or delete the BUILTIN\Administrators account completely from the Logins in SQL Server Enterprise Manager.

For additional information, see the SQL Server Books Online topic "Viewing and Modifying Role Memberships," which is available at the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/aa905209.aspx

More information

Note that when you run the service by clicking Control Panel and then choosing Services that there are not any helpful error messages. You have to check the Windows Event Viewer or start the service from the command prompt to see them.

Properties

Article ID: 237604 - Last Review: June 22, 2014 - Revision: 3.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbsqlsetup kbprb KB237604

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com