|   »

SQL Server 2005 Browser Service

SQL Server Browser service was introduced in version 90 or SQL Server 2005. On my first encounter I thought it was service for SQL Server Management Studio (SSMS)…Wrong.

What Browser Service is for:

If you want to get a list of SQL Servers on your subnet in other words browse the list of available servers. Browser service is the one which will help you get that. BTW you can the list of SQL Servers listening on your subnet by running

sqlcmd -L

SQL Server by default listens to port 1433. The name of that default instance is same as the name of host it is running on. Browser service does not play any role in resolving the default instance because port 1433 was assigned to SQL Server by Internet Assigned Numbers Authority (IANA). Its official and fixed. Any extra SQL Server instances (named instances) running on a machine are allocated dynamic port by default. Which means the port might change whenever the named instance is stopped/started. This behavior can be changed by assigning fixed port to that named instance and default port 0f 1433 can be changed as well. The down side of assigning fixed port to named instance or changing default port is that all the client must be updated to include the port number.

SQL Server Browser starts and claims UDP port 1434 (since it uses UDP it can listens to all broadcasts) Browser service reads the registry, identifies all instances of SQL Server on the computer, and notes the ports that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. When client application instantiate connection to a named instance by hostname\instance_name. The client network library sends a UDP message to the server using port 1434. SQL Server Browser responds by returning the TCP/IP port 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 of the desired instance.

Prior to Microsoft SQL Server 2000, only one instance of SQL Server could be installed on a computer. Multiple instance support was introduced in SQL Server 2000. SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434 and repond to client requests by returning the names of the installed instances, and the ports or named pipes used by the instance. SQL Server 2005 introduced Browser service as a replacement for SSRP.

If Browser service is not running:

  1. Client must specify full connection parameters including the TCP/IP port or else it would not be able to connect.
  2. Browser service help connecting to dedicated administrator connection (DAC) endpoints without it DAC to named or default instance will not work.
  3. sqlcmd -L or enumerating servers any where will not work.
  4. Analysis redirector service will not work.

Security Consideration:

Since the Browser service accepts unathuenticated request it is recommended to run it under low privileged account. For SQL Server 2005, HideInstance flag can be set to stop SQL Server Browser service from responding with information about that server instance.

Cluster:

Browser service is not cluster aware so must run on each node of cluster. SQL Server Browser listens on any IP. As a cluster has multiple IPs when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters.

Summary:

Any remote connection error specially for named instance or OLAP redirector may have to do with browser service. For named instance make sure it is not configured to use fixed TCP/IP port. If it is using fixed port then make sure client connection is providing that port number when connecting to the named instance.

Posted on July 5, 2008 at 3:26 am in Technology   |  RSS feed |   Reply   |   Trackback URL

Tags: , , , ,

4 Comments to “SQL Server 2005 Browser Service”

    
  1. Harriett
    March 13, 2017 at 12:48 pm

    I do not know if it’s just me or if everybody else encountering issues with your blog.

    It seems like some of the text within your
    posts are running off the screen. Can someone else please provide feedback and
    let me know if this is happening to them as well?
    This might be a issue with my internet browser because I’ve had this happen previously.
    Thanks

    Reply
  2. Luke
    December 9, 2014 at 2:08 am

    Hi, I wanted to speak to you regarding your site dbgeek.wordpress.com, give me a call 877-405-8518. – Luke Goodman

    Reply
  3. Ashish Kaushal
    August 11, 2008 at 9:49 pm

    Hi,

    To change a dynamic port to static port.
    1. From SQL Server 2005 group (in programs) select “Configuration Tools” > “SQL Server Configuration Manager”
    2. Expand “SQL Server 2005 Network Configuration” (by clicking on (+) sign)
    3. It will display all the instances installed in that machine. Click on instance which needs port change and on the right side you will see protocols and their status
    4. Double click on TCP/IP then select IP Addresses tab page.
    5. “IP All” will display the dynamic port used by instance, remove “TCP Dynamic Port” and assign “TCP Port”
    About security:
    One can argue that static port (other than default) is more secure as it is not apparent to others. But it is not all that secure if you look at the nature of attacks, e.g. sql injection etc.
    Maintenance of static ports (with browser service disabled) can be cumbersome each each client applications’ connection string must be changed to include the port number.

    Hope it helps.
    Ashish

    Reply
  4. Chandra
    August 11, 2008 at 6:11 pm

    Hi,

    Could you please tell me what is the procedure to change from dynamic port to static port? Also please confirm that, for security reasons, it is not recommended to run the SQL Browser.

    Thanks in advance

    Reply
    

Leave a comment