The SQL Server Browser Service listens on incoming connections for SQL Server services on SQL Server machines and provides information about SQL Server instances installed on the machine. This article discusses about the SQL Server Browser Service and UDP Port 1434, as well as what its use means throughout a simple example.
As per Microsoft’s documentation, the SQL Server Browser service contributes to the following actions:
- Browsing a list of available servers
- Connecting to the correct server instance
- Connecting to dedicated administrator connection (DAC) endpoints
- When upgrading an installation.
- When installing on a cluster.
- When installing a named instance of the Database Engine including all instances of SQL Server Express.
- When installing a named instance of Analysis Services.
Due to all the above offerings, as security best practice, the SQL Server Browser service is disabled by default, as reduces the attack surface area by not having a specific UDP port open. This UDP port is port 1434.
There are however cases, where the SQL Server Browser service must be up and running in order to properly use SQL Server services on a machine. Below we will discuss such an example.
Consider the following example: You are setting up a new SQL Server named instance (i.e. instance named “SQLInstance1”) on a new server on a corporate environment. That means that there is a firewall and, oh well, firewall rules everywhere!
So for added security, you change the default port and you set a new one for your new SQL Server instance. You then send a request to your network administrator and include in your request the following:
- The source IP (i.e. the client(s) that will be connecting to the SQL Server instance),
- The destination IP (that is, the IP of the SQL Server instance), and
- The destination port (in this case you provide the port on which you set the new SQL Server instance to be listening to).
After the relevant firewall rule(s) is implemented, you then try to access the SQL Server instance (i.e. try to connect to machineName\SQLInstance1) and guess what; you get the sweet error message: 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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) (learn more about this error message).
You then think to yourself: “But I have requested the relevant firewall rule(s) and I can ping the server. What’s wrong?“.
So, what do you do? Well, the way I see it, you have two options:
- Option 1: You explicitly specify the named SQL instance’s port in every client connection and in every connection string (which, in some cases can be acceptable and in some other can be annoying), or
- Option 2: You make sure that the SQL Server Browser service is running on the SQL Server machine, and that you request another firewall rule to be implemented, that will be allowing traffic from the client(s) (source) to the SQL Server machine on UDP port 1434, that is the port the SQL Server Browser service listens to.
So, if you go for Option 2, for connecting to SQL Server, you will just need to specify the instance name without any port number, that is have an “easier” connection string, and the SQL Server Browser service will take care of the rest (it will actually let the client know in the background, the actual port number of the SQL Server instance, so that the client connection to be able to “auto-specify” in the background the correct port number.
As a last note, regarding resource utilization, the SQL Server Browser service is quite lightweight, that is it does not make heavy use of any resources on the SQL Server machine.
I’m curious; what option would you prefer? Drop me a line and let’s further discuss.
Did you find this article useful and interesting? Find hundreds of useful SQL Server administration articles in Artemakis’s eBook: “Administering SQL Server (Second Edition)“.
Check our other related SQL Server Administration articles.
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© 2018 SQLNetHub