SQL Server Browser Service and UDP Port 1434

The SQL Server Browser Service and UDP Port 1434

by

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.

 

What Does the SQL Server Browser Service Do?

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.

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Example Related to the Use (or not) of the SQL Server Browser Service

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?“.

 

Discussion

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.

 

Featured Online Courses:

 

Read Also:

 

Related SQL Server Administration Articles:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub



Views: 14,287