Does Sql Browser Service Is Need To Be On

Overview of the SQL Server Browser service

Past: | Updated: 2010-02-xix | Comments (28) | Related: More > Database Administration

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 utilise 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 information technology is an extension of SQL Server Resolution Protocol (SSRP) of SQL Server 2000. Like SSRP information technology plays an important role while working with more than one SQL Server instance installed on the aforementioned car. The bones purpose of the SQL Server Browser service is to provide instance and port information to incoming connectedness requests.

To configure a ameliorate and safety admission mechanism for SQL Server, a DBA should have proper understanding of the SQL Server Browser service.

Below I will hash out the admission mechanism with reference to the TCP/IP protocol.

How to access SQL Server Browser service

SQL Server Browser service can exist configured during installation or after installation it tin be accessed in number of means. 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 Managing director (SQL Server 2005/2008)
Through Surface Expanse configuration (SAC, SQL Server 2005 only)

It should be noted that SAC just provides access to outset/stop or to change the startup type for the SQL Server Browser service. For management of all other parameters like ports, paths you accept to use SQL Server Configuration Managing director. Also SQL Server Browser service tin can be started by using control prompt.

What if SQL Server Browser service is stopped?

The SQL Server Browser service is installed with installation of SQL Server 2005/2008. It as well serves earlier versions installed on the aforementioned car if in that location whatever. The SQL Server Browser service has dissimilar impact in different situations, such as:

If y'all take 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 deviation in your connection parameters.
If there are more than ane instances running on the same auto, in that example either you have to start SQL Server Browser service or provide the port number forth with IP (or server name) and instance name, to admission any other instance than default.
If SQL Server Browser service is stopped and IP along with port number is non provided then connexion will exist refused.
If SQL Server case is configured using dynamic ports then browser service is required to connect to correct port number.
Too your named instances will non 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 admission to your SQL Server instances, hence the security of installed instances. At one end, the most security witting arroyo may be to use customized static ports for your instances and access SQL Server with fully qualified connectedness parameters. Information technology 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.

Information technology would be a secure configuration compared to having the SQL Server Browser service running and serving the incoming connection requests with instance and port data. If you take no problem in using fully customized connection strings (IP + Name + Port), then yous can stop the browser service permanently and admission the instance through a fully qualified connection string.

In another type of configuration, if the instance is configured to employ dynamic ports each fourth dimension, and so SQL Server Browser service should be running, otherwise there would exist no way to keep track of the dynamic ports that are assigned.

If you have simply a default instance installed (with default port) on your machine so SQL Server Browser service may be stopped to avoid overhead.

As function of best practices, e'er run SQL Server Browser service with a minimum privileged account. Co-ordinate to BOL any Windows user having the following rights would exist capable to run the SQL Server Browser service.

Deny access to this reckoner from the network
Deny logon locally
Deny logon as a batch chore
Deny logon through Terminal Services
Log on equally a service
Read and write the SQL Server registry keys related to network communication (ports and pipes)

Yous tin alter the default account of the browser service (local system), to whatever other user having the to a higher place mentioned rights on the machine.

Limitations and further considerations

SQL Server Browser service operates at the Bone level. You tin 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 connexion requests.
Aforementioned concept may exist 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 practise not forget to include UDP port 1434 in the exception listing. UDP port 1434 is a port used by the SQL Server Browser service.
If you are running SQL Server 2000 case adjacent along with SQL Server 2005/2008, then make sure to utilize service pack 3 for the SQL Server 2000 instance. SP3 is required to work correctly with the SQL Server Browser service.
It would exist amend if you accept SQL Server Browser service with aforementioned settings on both development and production servers. It would forestall whatever problems due to differences of connection cord data.

Testing the configurations

I have a named instance with the following customized static port every bit shown in TCP/IP properties of instance in SQL Server Configuration Managing director.

Customized static port for SQL Server instance

If SQL Server Browser service is not running then first information technology through the command prompt or any of the other ways mentioned above.

command prompt

When the browser service is running, I tin can access the instance without a specifying the port. I tin can successfully login with the following connection parameters (IP + InstanceName).

ip instance name

Now end the SQL Server Browser service with net stop sqlbrowser command in the command prompt or one of the other ways that were mentioned higher up. Also exit SSMS and reopen it. Try to login again with the same parameters every bit before. This time you volition get a message like the following.

connect to server

The reason is that the browser service is not running and the customized static port is besides missing in the connection parameters. To login successfully nosotros accept to provide the customized port number in the following way. The server name field would demand IP\InstanceName,PortNumber.

database engine

Hopefully this has given you enough information to understand how the browser service runs and when and when you do not need to take this service running.

Next Steps

Before making any changes to the SQL Server Browser service make sure that connexion strings in existing code are changed to run across the inverse configurations, otherwise you may accept some connection issues.

Click hither to learn more most how to showtime and stop the SQL Server Browser service through Windows services MMC and control prompt.
Click here to read further nigh network protocols and libraries of SQL Server.
Click hither to further read about assigning a static port to SQL Server instance.
Click hither to read further about adding SQL Server Browser service in exception list of the Windows firewall