SQL Server Agent cannot be started after adding an "Alias Local Host Server"

  • Article

We might find ourselves in the following scenario: for a SQL Server 2005 edition, you accidentaly specify a wrong "Alias Local Host Server" in Management Studio. Now, what happens is that, when the SQL Server Agent starts, it tries to connect to this ALIAS and if the connection fails, the service doesn't start. What we see in the SQL Server Agent Logs can be similar to:

"2008-08-22 17:02:11 - ! [298] SQLServer Error: 53, Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 08001]
2008-08-22 17:02:11 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2008-08-22 17:02:12 - ! [298] SQLServer Error: 53, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2008-08-22 17:02:12 - ! [000] Unable to connect to server 'test'; SQLServerAgent cannot start
2008-08-22 17:03:08 - ! [298] SQLServer Error: 53, Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 08001]
2008-08-22 17:03:08 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2008-08-22 17:03:08 - ! [298] SQLServer Error: 53, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2008-08-22 17:03:08 - ! [382] Logon to server 'test' failed (DisableAgentXPs)
2008-08-22 17:03:09 - ? [098] SQLServerAgent terminated (normally)" *

* Here, my alias was called test.

We have at least two ways out of this one.

1. We can create a valid alias with this name.

2. We can modify the "Alias Local Host Server" property. The only problem is that since the SQL Server Agent doesn't start, we cannot open the Properties window for it in Management Studio and therefore cannot alter the settings. So, here is the Registry Key that corresponds to this property:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent\ServerHost