We ran into a situation when SQL browser service was not running on the server, we followed the following steps to reach a state of getting browser service to run.
Windows Server 2003
SQL Express 2005
Service Pack: SP2
· We followed the KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
· SQL browser service was already set to “automatic”.
· When we try to start the service ,we got this error :
TITLE: Surface Area Configuration
The service did not respond to the start or control request in a timely fashion, you need administrator privileges to be able to start/stop this service. (SQLSAC)
· The application event logs had the following errors :
The configuration of the SQL instance MSSQLServer is not valid.
The configuration of the AdminConnection\TCP protocol in the SQL instance <instance name> is not valid.
The SQLBrowser service was unable to establish SQL instance and connectivity discovery.
The SQLBrowser is enabling SQL instance and connectivity discovery support
To get more information on reason for service failure, you can start the SQL Browser as a console application
· When we try starting SQL browser as a console application we got the following errors :
C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c
SQLBrowser: starting up in console mode
SQLBrowser: starting up SSRP redirection service
SQLBrowser is successfully listening on 0.0.0.0
SQLBrowser: failed starting SSRP redirection services — shutting down.
What is SSRP Redirection?
SQL Server Resolution Protocol (SSRP) was developed to listen on port 1434, beginning with SQL Server 2000. This was how we managed to have multiple SQL Server instances on the same machine. The SSRP redirector as the name suggests “redirected” the client requests to the appropriate instance based on the Instance Name or Port # or Named Pipe.
· We found a key under SQL server registry hive:
· This is from an older default installation of SQL server 2000. This should not exist anymore since SQL Browser is a replacement for the SSRP mechanism.
· If above key exists, SQL Browser during startup tries to connect to this instance (non-existent) and hence fails
· So we renamed above key it to CurrentVersion_Old and tried to restart the browser services, this time we were able to do so.
1. Please check for the existence of this key under the wow6432node key as well.
2. Please make sure that you do not have any SQL Server 2000 instances running anymore before making the registry change.
SE, Microsoft SQL Server
TL, Microsoft SQL Server