Overview of the SQL Server Browser service

By:   |   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 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.

Customized static port for SQL Server instance

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

command prompt

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).

ip instance name

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.

connect to server

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.

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 have this service running.

Next Steps

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

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, March 30, 2020 - 3:35:59 AM - Sandeep Kumar Back To Top (85219)

Very helpful, thanks Much.

Thanks,

Sandeep Kumar

Wednesday, January 3, 2018 - 12:41:02 AM - Diana R. Back To Top (74691)

 Hi I am getting this message from ATT. I am hosting a web application on the server. Do I have to stop the  Sql Server BRowser in order not to get this message?   I do not think that I need the service since I am only hosting a web application.

 

AT&T has received information indicating that one or more hosts using your Internet connection are running an MS-SQL Server Resolution Service (MC-SQLR) that is open to the Internet.


The IP address  xxx.xx.xx  was observed with publicly accessible MC-SQLR on December 31, 2017 at 11:44 PM CST. Our records indicate that this IP address was assigned to you at this time. 

MC-SQLR can be abused to reveal details about your computers, and also for denial-of-service attacks against other computers. Please consider using a firewall to restrict access to port 1434 to specific IP addresses; or disabling MC-SQLR on your computer(s) if it is not needed. Detailed information about MC-SQLR abuse and how to mitigate it is available at http://tools.cisco.com/security/center/viewAlert.x?alertId=37132. 

 

 

 


 

Monday, October 17, 2016 - 1:01:24 AM - Redge Shepherd Back To Top (43573)

Thank you, this post addressed the issues I was having to connect to my database using Go with the go-mssqldb API.

 

 

Tuesday, March 22, 2016 - 1:00:04 AM - Aarti Parmar Back To Top (41021)

 Thank you so much sir..

i was stuck on this error 26. i tried many ways to solve out this error but ur solution IP\InstanceName,PortNumber worked for me.

 

Thursday, September 10, 2015 - 3:26:19 AM - Abdul Waheed Back To Top (38632)

 

Excellent Article about SQL Server Browser.

Thank You Atif!

Thursday, August 27, 2015 - 8:34:58 AM - Ponraj Back To Top (38550)

its very useful. Thank you

Friday, February 13, 2015 - 9:13:53 AM - Azhar Back To Top (36232)

Thank you Atif.

I had installed SQl Server 2008 and 2012 on the same sytem and i was unable to login to SQL Server 2012. This post really helped me to solve this issue. This post is explained in a simple and perfect manner. 

Once again Thanks

Friday, December 12, 2014 - 4:38:31 AM - Atif Shehzad Back To Top (35589)

Hi Dinesh,

The mentioned issue points towards domain authentication problem. You may try restart of browser service. If not helpfull the verify the domain and login status of processes that are generating this error.

 

 

Thursday, December 11, 2014 - 7:22:10 AM - Dinesh Vishe Back To Top (35567)

Hi,

My DB production server is there and getting following error.

 

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed.

 

Need to restart sql browser services .what wil affect currenty request ??

Thursday, September 18, 2014 - 8:13:53 AM - Hari Back To Top (34587)

Really nice article. Clears any ambiguity on browser service. A quick questions - In my .net web application iam using 127.0.0.1 to connect to sql server and it works fine for some time. After some time i keep noticiing errors like the below

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

After that i noticed that the browser service is disabled. Is it possible that this is causing this error to happen. 

Wednesday, May 28, 2014 - 6:46:45 AM - Andrew Back To Top (31970)

I had stopped SQL Browser, but I could connect to sqlinstances from remote computers if I wrote static ports in protocols. 

Saturday, March 30, 2013 - 11:48:42 AM - RichS Back To Top (23088)

You need to set the SQL Browser service to Autostart, it defaults to OFF.  Not only does distributed/Subscription SSRS reporting need it but also there are Powershell advantages/requirements to have it running.  If you install SSRS by itself on one machine and use the DB engine/Agent on a second machine, SQL Browser is required to allow full capabilities of SSRS.

http://msdn.microsoft.com/en-us/library/ms143736(v=sql.100).aspx

“SQL Server Agent is required if you want to use the Reporting Services background processing application for subscriptions and scheduled reporting. SQL Server Agent should be running in the instance of the Database Engine that hosts the report server database. You can specify SQL Server Agent in the Server Configuration page when you install Database Engine.”

“SQL Server Browser is required if you are installing an instance of the Database Engine that will be used to host a report server database for a report server instance that is on a different computer. You can specify SQL Server Browser in the Server Configuration page when you install Database Engine.”

 

Monday, March 18, 2013 - 2:14:25 AM - Atif Shehzad Back To Top (22841)

Hi Latha,

It looks that previous version installation is pending in registry. To clear the registry values please try the steps as mentioned by WeiLin in following forum link on msdn

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/eb98e589-e147-4168-9da0-4a0527b3a6fa

 

Thanks

 

Thursday, March 14, 2013 - 2:41:11 AM - latha Back To Top (22791)

Hi,

I had the following error when installing SQL server and all which are related to SQL Browser:

 

TITLE: Microsoft SQL Server 2008 Setup

------------------------------

 

 

1. SQL Browser could not be started. Reason: SQL Browser could not be started. Reason:Service 'SQLBROWSER' start request failed.

 

2. Service 'SQLBrowser' start request failed.

 

3. SQL Server Browser configuration for feature 'SQL_Browser_Redist_SqlBrowser_Cpu32' was cancelled by user after a previous installation failure. The last attempted step: Starting the SQL Server Browser service 'SQLBrowser', and waiting for up to '900' seconds for the process to complete. .

 

Can you please give some ideas on how to resolve this.

Besides I've given the following shots as part of trouble shooting

1. deleted MSXML 4.0 SP2, as this could be blocking the sql browser installation

2. uninstalled and reinstalled sql server by deleteing all the traces of sql using regedit

 

 

Tuesday, March 12, 2013 - 3:15:34 AM - Liyakat Ali Back To Top (22727)

Well explained.... :)

Thursday, January 31, 2013 - 4:06:49 PM - Sajal Bagchi Back To Top (21853)

Very well explained. I always had some confusion regarding the browser service, you made it clear now. Thanks a lot :)

Thursday, January 31, 2013 - 10:41:23 AM - Jer Back To Top (21843)

Nice explanation.  Thanks.

Thursday, January 31, 2013 - 8:53:48 AM - Gopi Back To Top (21834)

Nice Article.

Friday, December 7, 2012 - 3:53:45 PM - Umair Back To Top (20813)

Refreshed my concepts. thanks.

Wednesday, December 5, 2012 - 3:56:29 AM - ashok Back To Top (20755)

 

Hi Atif Shehzad,

 

 

 

Thank you for your tips,

 

 

 

I'm a regular follower for this site, It was nice explanation, it'll use lot for me.

 

 

 

Thank you,

 

 

Wednesday, October 3, 2012 - 7:41:40 AM - abid tabassum Back To Top (19770)

realy nice article, keep it up

Thursday, September 27, 2012 - 10:01:03 AM - S Back To Top (19708)

Nice explantaion.  Helped a lot.

Wednesday, June 20, 2012 - 8:41:42 PM - William Back To Top (18142)

Excellent explanation!

Wednesday, April 18, 2012 - 1:11:05 AM - Atif Back To Top (16982)

@Nagesh, Please try with following two points.

  1. Make sure SQL browser service is running
  2. Also check under 'Network Servers' tabl along with 'Local Servers' tab

Then let us know the result.

Thanks

Tuesday, April 17, 2012 - 6:44:43 AM - Nagesh Back To Top (16961)

Dear All,

Iam facing an issue after installed SQL Server 2008 standard edition 64 bit with sp3..

The problem is after i installed the default instance iam unable to browse the instance(management studio) but i can connect through dot(.) or local or manual entry of server name. So after that i had installed sp3 and tried so...same above way only i can connect.

 

Could you pls help me from this situation to get it resolve!

Nagesh

mail: [email protected]

Monday, February 22, 2010 - 4:38:38 AM - @tif Back To Top (4930)

 @Divya. Thanks for appreciation. If you have following very security concious policy and also could manage the ports numbers and IPs in application code for all of your instances then you may stop the SQL Server browser service and use customized connection string.

But if that is not the case then keep browser service stated and let it serve you for port info about your instances.

 

Regards


Friday, February 19, 2010 - 6:10:16 AM - divya Back To Top (4924)

Nice Article. I was having less knowledge of Browser service. I thought its just for making our server accessible across network. But, your post has cleared it can be accessed through port number if the service is not started.

By the way, which should be a better option for production having huge number of servers.? 

 

Friday, February 19, 2010 - 4:47:18 AM - --cranfield Back To Top (4923)

our standard SQL Server build process always disables this service. We dont require it on our 300 servers as they are all default instance.














get free sql tips
agree to terms