Name already in use
sql-docs / docs / database-engine / configure-windows / sql-server-browser-service-database-engine-and-ssas.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
SQL Server Browser Service (Database Engine and SSAS)
[!INCLUDE SQL Server] The [!INCLUDEssNoVersion]Browser program runs as a Windows service. [!INCLUDEssNoVersion] Browser listens for incoming requests for [!INCLUDEmsCoName] [!INCLUDEssNoVersion] resources and provides information about [!INCLUDEssNoVersion] instances installed on the computer. [!INCLUDEssNoVersion] Browser contributes to the following 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 [!INCLUDEssDE] and [!INCLUDEssAS], the [!INCLUDEssNoVersion] Browser service (sqlbrowser) provides the instance name and the version number. [!INCLUDEssNoVersion] Browser is installed with [!INCLUDEssNoVersion].
[!INCLUDEssNoVersion] Browser can be configured during setup or by using [!INCLUDEssNoVersion] Configuration Manager. By default, the [!INCLUDEssNoVersion] Browser service starts automatically:
When upgrading an installation.
When installing on a cluster.
When installing a named instance of the [!INCLUDEssDE] including all instances of SQL Server Express.
When installing a named instance of [!INCLUDEssASnoversion].
Prior to [!INCLUDEssVersion2000], only one instance of [!INCLUDEssNoVersion] could be installed on a computer. [!INCLUDEssNoVersion] listened for incoming requests on port 1433, assigned to [!INCLUDEssNoVersion] by the official Internet Assigned Numbers Authority (IANA). Only one instance of [!INCLUDEssNoVersion] can use a port, so when [!INCLUDEssVersion2000] introduced support for multiple instances of [!INCLUDEssNoVersion], [!INCLUDEssNoVersion] Resolution Protocol (SSRP) was developed to listen on UDP 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, [!INCLUDEssVersion2005] introduced the [!INCLUDEssNoVersion] Browser service as a replacement for SSRP.
How SQL Server Browser Works
When an instance of [!INCLUDEssNoVersion] starts, if the TCP/IP protocol is enabled for [!INCLUDEssNoVersion], the server is assigned a TCP/IP port. If the named pipes protocol is enabled, [!INCLUDEssNoVersion] listens on a specific named pipe. This port, or «pipe,» is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using [!INCLUDEssNoVersion] Configuration Manager. Because only one instance of [!INCLUDEssNoVersion] can use a port or pipe, different port numbers and pipe names are assigned for named instances, including [!INCLUDEssExpress]. By default, when enabled, both named instances and [!INCLUDEssExpress] are configured to use dynamic ports, that is, an available port is assigned when [!INCLUDEssNoVersion] starts. If you want, a specific port can be assigned to an instance of [!INCLUDEssNoVersion]. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime [!INCLUDEssNoVersion] is restarted, so the correct port number is unknown to the client.
Upon startup, [!INCLUDEssNoVersion] Browser starts and claims UDP port 1434. [!INCLUDEssNoVersion] Browser reads the registry, identifies all instances of [!INCLUDEssNoVersion] on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, [!INCLUDEssNoVersion] Browser returns the first enabled port it encounters for [!INCLUDEssNoVersion]. [!INCLUDEssNoVersion] Browser support ipv6 and ipv4.
When [!INCLUDEssNoVersion] clients request [!INCLUDEssNoVersion] resources, the client network library sends a UDP message to the server using port 1434. [!INCLUDEssNoVersion] 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. SQL Server Browser Service does not provide port resolution for default instances.
Using SQL Server Browser
If the [!INCLUDEssNoVersion] Browser service is not running, you are still able to connect to [!INCLUDEssNoVersion] if you provide the correct port number or named pipe. For instance, you can connect to the default instance of [!INCLUDEssNoVersion] with TCP/IP if it is running on port 1433.
However, if the [!INCLUDEssNoVersion] Browser service is not running, the following connections do 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 named pipe).
Any component that generates or passes server\instance information that could later be used by other components to reconnect.
Connecting to a named instance without providing the port number or pipe.
DAC to a named instance or the default instance if not using TCP/IP port 1433.
The OLAP redirector service.
Enumerating servers in [!INCLUDEssManStudioFull], Enterprise Manager, or Query Analyzer.
If you are using [!INCLUDEssNoVersion] in a client-server scenario (for example, when your application is accessing [!INCLUDEssNoVersion] across a network), if you stop or disable the [!INCLUDEssNoVersion] 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 ensure it is connecting to the proper port.
The port you choose for each instance may be used by another service or application on the server, causing the instance of [!INCLUDEssNoVersion] to be unavailable.
[!INCLUDEssNoVersion] Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, [!INCLUDEssNoVersion] Browser should be installed and turned on for each node of the cluster. On clusters, [!INCLUDEssNoVersion] Browser listens on IP_ANY.
[!NOTE]
When listening on IP_ANY, when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because [!INCLUDEssNoVersion] Browser returns the first IP/port pair that it encounters.
Installing, Uninstalling, and Running from the Command Line
By default, the [!INCLUDEssNoVersion] Browser program is installed at C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe.
The [!INCLUDEssNoVersion] Browser service is uninstalled when the last instance of [!INCLUDEssNoVersion] is removed.
[!INCLUDEssNoVersion] Browser can be started from the command prompt for troubleshooting, by using the -c switch:
[!INCLUDEssNoVersion] Browser listens on a UDP port and accepts unauthenticated requests by using [!INCLUDEssNoVersion] Resolution Protocol (SSRP). [!INCLUDEssNoVersion] Browser should be run in the security context of a low privileged user to minimize exposure to a malicious attack. The logon account can be changed by using the [!INCLUDEssNoVersion] Configuration Manager. The minimum user rights for [!INCLUDEssNoVersion] Browser are the following:
Deny access to this computer from the network
Deny logon locally
Deny Log on as a batch job
Deny Log On Through Terminal Services
Log on as a service
Read and write the [!INCLUDEssNoVersion] registry keys related to network communication (ports and pipes)
Setup configures [!INCLUDEssNoVersion] Browser to use the account selected for services during setup. Other possible accounts include the following:
Any domain\local account
The local service account
The local system account (not recommended as has unnecessary privileges)
Hiding SQL Server
Hidden instances are instances of [!INCLUDEssNoVersion] that support only shared memory connections. For [!INCLUDEssNoVersion], set the HideInstance flag to indicate that [!INCLUDEssNoVersion] Browser should not respond with information about this server instance.
Overview of the SQL Server Browser service
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.
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.
What is Microsoft SQL Server Browser Service?
The Browser Service was introduced in SQL Server 2005 as a simple way to provide SQL Server connection information to client applications on workstations.
Microsoft SQL Server contains multiple components that work together to provide data access to a variety of client applications, such as Acctivate. One of the key components for Acctivate is the SQL Server Browser Service.
The Browser Service was introduced in SQL Server 2005 as a simple way to provide SQL Server connection information to client applications on workstations. The Browser Service responds to client requests by providing a list of SQL Server Instances (e.g., ACCTIVATE) and the corresponding TCP/IP port number. The client application then re-connects to the SQL Server on the given port number.
The Browser Service listens on UDP Port 1434 on each computer running Microsoft SQL Server. You must allow access from Acctivate workstations to the server on UDP 1434. Read our firewall exceptions article for more information.
Microsoft has an article on starting and stopping the SQL Server Browser Service. By default, the Browser Service may be disabled.
For more knowledge on the SQL Browser service, please check out this article on from the Microsoft Docs page.
How SQL Server browser service Works
Some of you may wonder the role SQL browser service plays in the SQL Server instance. In this blog post, I’ll provide an overview of the how SQL Server browser plays crucial role in connectivity and understand the internals of it by capturing the network monitor output during the connectivity with different scenario.
In the diagram below, we can see that an UDP request over 1434 was sent from a local machine (client) to SQL Server machine (server) and response came from server 1434 port over UDP to client port with list of instances and the port in which it is listening:
We can see that client sends 5 requests which ended up with no response from UDP 1434 of server. so connectivity will never be established to the named instance.
There is no call made to the server’s 1434 port over UDP instead connection is directly made to the TCP port specified in the connection string.
We can see that no calls were made to server’s 1434 port over UDP in which SQL Server browser is listening.
We can see that connectivity failed after multiple attempts because client assumes that default instance of SQL Server always listens on TCP port 1433.
You can refer the blog below to see some workarounds to handle this situation here: