Saturday, August 3, 2013

SQL Server: Browser Service Started and Then Stopped

This error message is commonly encountered: “The SQL Server Browser service on your local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.” This is how explore and fix this SQL Server issue.
If the SQL Server Browser service is not started properly while connecting to SQL Server, you might get this error.
provider: SQL Network Interfaces, error: 26 – Error Locating Server/ Instance Specified (Microsoft SQL Server)
The SQL Server Browser service on Local Computer started and then stopped_6
The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
Resolution
To solve the SQL server Browser service on Local Computer started and then stopped error, we need to edit a registry value to disable the SsrpListener service.
64-bit Operating Systems (x64)
[+] HKEY_LOCAL_MACHINE 
     [-] Software 
         [-] Wow6432Node
             [-] Microsoft  
                [-] Microsoft SQL Server 
                   [-] 90
                     [-] SQL Browser
                         [*] SsrpListener = 0

32-bit Operating Systems (x86)
[+] HKEY_LOCAL_MACHINE 
     [-] Software 
             [-] Microsoft  
                [-] Microsoft SQL Server 
                   [-] 90
                     [-] SQL Browser
                         [*] SsrpListener = 0

Here are the steps needed to correct this issue. 64-bit pictures are used for reference, but the 32-bit registry location is included as well.
1. Hit Win + R to bring up the Run dialog box. Enter regedit, and click OK.
The SQL Server Browser service on Local Computer started and then stopped_4
2. For x64 systems, navigate to the keyKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser. For x86 systems, navigate to the keyHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL Browser.
3. Right-click on SsrpListener and modify and set the Value data to 0. Click OK.
The SQL Server Browser service on Local Computer started and then stopped_1
4. Now, open the Run dialog box again as we did in Step 1, enter services.msc, and click OK. Then navigate to the SQL Server Browser service.
5. Right-click, and select Start. Also, set the Startup type as Automatic from Properties. After you modify the registry value SsrpListener, the SQL Server Browser service will start properly from now on.
The SQL Server Browser service on Local Computer started and then stopped_3