Overview of the SQL Server Browser service
Problem
A new service, SQL Server Browser, was introduced with SQL Server 2005 and is also
used with SQL Server 2008. In this tip I will cover what this is and how to use this
service to improve your SQL Server environment.
Solution
The SQL Server Browser service introduced in SQL Server 2005, runs as a Windows
service and it is an extension of SQL Server Resolution Protocol (SSRP) of SQL Server
2000. Like SSRP it plays an important role while working with more than one SQL
Server instance installed on the same machine. The basic purpose of the SQL Server
Browser service is to provide instance and port information to incoming connection
requests.
To configure a better and safe access mechanism for SQL Server, a DBA should have
proper understanding of the SQL Server Browser service.
Below I will discuss the access mechanism with reference to the TCP/IP protocol.
How to access SQL Server Browser service
SQL Server Browser service can be configured during installation or after installation it
can be accessed in number of ways. Here are couple of common ways to access the
SQL Server Browser service.
Through Windows Services MMC. Access Windows services through the control
panel or enter services.msc in the run box.
Through a command prompt
Through SQL Server Configuration Manager (SQL Server 2005/2008)
Through Surface Area configuration (SAC, SQL Server 2005 only)
It should be noted that SAC just provides access to start/stop or to change the startup
type for the SQL Server Browser service. For management of all other parameters like
ports, paths you have to use SQL Server Configuration Manager. Also SQL Server
Browser service can be started by using command prompt.
What if SQL Server Browser service is stopped?
The SQL Server Browser service is installed with installation of SQL Server 2005/2008.
It also serves earlier versions installed on the same machine if there any. The SQL
Server Browser service has different impact in different situations, such as:
If you have just one instance installed on machine and it is running on default
port 1433, then status of SQL Server Browser service does not make any
difference in your connection parameters.
If there are more than one instances running on the same machine, in that case
either you have to start SQL Server Browser service or provide the port number
along with IP (or server name) and instance name, to access any other instance
than default.
If SQL Server Browser service is stopped and IP along with port number is not
provided then connection will be refused.
If SQL Server instance is configured using dynamic ports then browser service is
required to connect to correct port number.
Also your named instances will not be published in the list of SQL Server
instances on the network (which could be a good thing)
Best practices for using SQL Server browser service
The way you use SQL Server Browser service affects the access to your SQL Server
instances, hence the security of installed instances. At one end, the most security
conscious approach may be to use customized static ports for your instances and
access SQL Server with fully qualified connection parameters. It would include IP +
InstanceName + Port. In this configuration your instance would not be exposed to the
network also more parameters are required in the connection string.
It would be a secure configuration compared to having the SQL Server Browser service
running and serving the incoming connection requests with instance and port
information. If you have no problem in using fully customized connection strings (IP +
Name + Port), then you can stop the browser service permanently and access the
instance through a fully qualified connection string.
In another type of configuration, if the instance is configured to use dynamic ports each
time, then SQL Server Browser service should be running, otherwise there would be no
way to keep track of the dynamic ports that are assigned.
If you have just a default instance installed (with default port) on your machine then SQL
Server Browser service may be stopped to avoid overhead.
As part of best practices, always run SQL Server Browser service with a minimum
privileged account. According to BOL any Windows user having the following rights
would be capable to run the SQL Server Browser service.
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)
You can change the default account of the browser service (local system), to any other
user having the above mentioned rights on the machine.
Limitations and further considerations
SQL Server Browser service operates at the OS level. You can not have the
browser service started for one instance and stopped for others on the same
machine. If SQL Server Browser service is started, it would be serving all
incoming connection requests.
Same concept may be applied in case of clustering. You have to separately
install and start the browser service on each node of the cluster.
If you are using any type of firewall then do not forget to include UDP port 1434
in the exception list. UDP port 1434 is a port used by the SQL Server Browser
service.
If you are running SQL Server 2000 instance side by side along with SQL Server
2005/2008, then make sure to apply service pack 3 for the SQL Server 2000
instance. SP3 is required to work correctly with the SQL Server Browser service.
It would be better if you have SQL Server Browser service with same settings on
both development and production servers. It would prevent any problems due to
differences of connection string information.
Testing the configurations
I have a named instance with the following customized static port as shown in TCP/IP
properties of instance in SQL Server Configuration Manager.
If SQL Server Browser service is not running then start it through the command prompt
or any of the other ways mentioned above.
When the browser service is running, I can access the instance without a specifying the
port. I can successfully login with the following connection parameters (IP +
InstanceName).
Now stop the SQL Server Browser service with net stop sqlbrowser command in the
command prompt or one of the other ways that were mentioned above. Also exit SSMS
and reopen it. Try to login again with the same parameters as before. This time you will
get a message like the following.
The reason is that the browser service is not running and the customized static port is
also missing in the connection parameters. To login successfully we have to provide the
customized port number in the following way. The server name field would need IP\
InstanceName,PortNumber.
