Using SQL Server Browser
SQL Server Browser runs as a Windows service on the server. SQL Server Browser listens for incoming requests for SQL Server resources and provides information about SQL Server instances that are installed on the computer. SQL Server Browser contributes to three actions:
- Browsing a list of available servers
- Connecting to the correct server instance
- Connecting to Dedicated Administrator Connection (DAC) endpoints
For each instance of the Database Engine, the SQL Server Browser service (sqlbrowser) provides the instance name and the version number. SQL Server Browser is installed with SQL Server 2005 and provides assistance for previous versions of SQL Server that are running on that computer, starting with SQL Server 7.0.
By default, the SQL Server Browser service is not enabled for SQL Server Express. SQL Server Browser can be initially configured using the Surface Area Configuration Tool and managed using SQL Server Configuration Manager.
Background
Before SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on port 1434. This listener service responded to client requests with the names of the installed instances and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 provides the SQL Server Browser service as a replacement for SSRP.
How SQL Server Browser Works
When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port or pipe will be used by that specific instance to exchange data with client applications. During installation, port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator by using SQL Server Configuration Manager. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including SQL Server Express. By default, named instances are configured to use dynamic ports, so an available port is assigned when SQL Server starts. A specific port can be assigned to a SQL Server instance. When connecting, clients can specify the desired port. However, if the port is dynamically assigned, the port number can change any time SQL Server is restarted, so the correct port number is unknown to the client.
On startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all SQL Server instances on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser will return all ports enabled for SQL Server. SQL Server 2005 and SQL Server Browser support ipv6 and ipv4.
When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.
Using SQL Server Browser
If the SQL Server Browser service is not running, you can still connect to SQL Server if you provide the correct port number or named pipe. For example, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.
The following connections will not work:
- Any component that tries to connect to a named instance without fully specifying all the parameters, such as the TCP/IP port or a named pipe.
- Any component that generates or passes server or instance information that could later be used by other components to reconnect.
- Connecting to a named instance without providing the port number or pipe. This includes Data Mirroring to a named instance and clustering a named instance.
- Dedicated Administrator Connections to a named instance, or the default instance if not using TCP/IP port 1434.
- The OLAP redirector service.
- Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.
If you are using SQL Server in a client-server scenario, for example, when your application is accessing SQL Server across a network, if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number. This approach has the following problems:
- You must update and maintain client application code to make sure it is connecting to the proper port.
- The port you choose for each instance might be used by another service or application on the server, causing the SQL Server instance to be unavailable.
Side-by-Side Installation with SQL Server 2000
In SQL Server 2000, the identification of the server connection endpoints was performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server on a computer that is also running SQL Server 2000 or MSDE, they must be upgraded to SP3 or later. Versions earlier than SP3 do not properly share port 1434 and might not make your SQL Server instances available to requesting client applications. Although you can change the startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, the recommended resolution is to update all older versions of SQL Server to the latest service pack.
When an instance of SQL Server 2000 that is not updated to at least service pack 3a is installed on the computer, if SQL Server Browser is not running, the SQL Server 2000 listener service starts. If SQL Server Browser starts after the listener service, it waits five seconds for SQL Server 2000 to give up port 1434. If that does not occur, SQL Server Browser will not start. To resolve this problem with versions of SQL Server 2000 earlier than SP3a, stop SQL Server 2000, start SQL Server Browser, then restart SQL Server 2000. The SQL Server 2000 listener service will continue to try to start on port 1434, so the SQL Server 2000 instance should be upgraded to SP3a or later as soon as possible.
SQL Server 7.0 has no conflicts with SQL Server Browser.
Installing, Uninstalling, and Running from the Command Line
By default, the SQL Server Browser program is installed at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.. If SQL Server 2000 is installed on the computer, but not upgraded to SP 3, and if the SQL Server 2000 instance is not being upgraded, then SQL Server 2000 should be stopped during SQL Server 2005 installation. If SQL Server 2000 is running, SQL Server Browser will not be able to start and claim port 1434, and the installation might fail due to lack of connectivity.
The SQL Server Browser service is uninstalled when the last instance of SQL Server 2005 is removed. Instances of SQL Server 2000 will resume their original behavior.
SQL Server Browser can be started from the command line for troubleshooting, by using the -c switch.
<drive>\<path>\sqlbrowser.exe -c
Security
Account Privileges
SQL Server Browser listens on a UDP port and accepts unauthenticated requests using SQL Server Resolution Protocol (SSRP). SQL Server Browser should be run in the security context of a low-privileged user to minimize exposure to a malicious attack. By default, SQL Server Browser starts using the Local System account. The logon account can be changed by using the Windows Services program. The minimum user rights for SQL Server Browser are as follows:
- Deny access to this computer from the network.
- Deny logon locally.
- Deny logon as a batch job.
- Deny logon through Terminal Services.
- Log on as a service.
- Read and write the SQL Server registry keys related to network communication (ports and pipes).
Default Account
Setup configures SQL Server Browser to use the account selected for services during setup. Other possible accounts include:
- Any domain\local account
- The local service account (not available on W2K platforms)
- The local system account (not recommended as has unnecessary privileges)
Hiding SQL Server
Hidden instances are SQL Server instances that support only shared memory connections. For SQL Server 2005, the HideInstance registry key indicates that SQL Server Browser should not respond with information about this server instance. SQL Server Browser also supports the SQL Server 2000 method of hiding instances that are enabled by setting the HideServer option through the server network utility. For more information, see How to: Hide an Instance of SQL Server Database Engine in SQL Server 2005 Books Online.
Using a Firewall
To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434 in addition to the TCP port used by SQL Server (for example, 1433). For more information, see How to: Configure a Firewall for SQL Server Access in SQL Server 2005 Books Online.
See Also
Tasks
How to: Start and Stop the SQL Server Browser Service (SQL Server Express)