Configuring ports in SQL Server should always be a fundamental part of the role of the DBA. It should be a routine part of installing and configuring new SQL Server instances and because of that, the different options for configuring ports should be firmly understood. In this brief article, I’m going to run through the different types of ports that SQL Server can listen on. I’ll also look at the role of SQL Browser and how it relates to named instances.
By default, SQL Server default instances listen on port 1433 for incoming connections. That means port 1433 needs to be open in the firewall of the SQL Server and associated firewall rules for both services need to be configured to allow communication on the port.
Using the default port for production environments is not usually seen as best practice due to the high probability of a hacker targeting the well-known port.
Named Instances are much more secure, and they operate on one of two port options:
A port which changes dynamically, and is automatically re-assigned by SQL Server on each service restart.
This is often seen as the most secure option, but can be difficult to manage when using firewalls. SQL Server listens for connections to its named instances on UDP port 1434 via the SQL Browser service, which directs the client to the correct TCP port.
Both the TCP port and the UDP port need to be opened in the firewall by the network team.
A port which is assigned on installation and never changes, unless changed manually. Static ports are more secure than using a default port, and easier to manage when using firewalls because the port number remains constant. SQL Server still listens for connections to its named instance on UDP port 1434 via the SQL Browser service, then directs the client to the correct TCP port, unless the port is explicitly specified in the connection string.
Both the static TCP port and the UDP port need to be opened in the firewall.
Role of SQL Browser Service
As stated above, wherever a Named Instance is being used in SQL Server, the Browser Service must be enabled and started. The browser server talks on UDP port 1434 and handles incoming connections to non-default static and dynamic ports when the instance name is used in the connection string without specifying the port.
Talking to SQL Browser
To get a better understanding of the role of SQL Browser, we can use a free, lightweight port scanning application like PortQry
Download, extract and open PortQryUI.exe
Enter the IP or DNS name of the server you want to talk to.
Select Manually input query ports and enter 1434 in the Ports to query box. Change the protocol to UDP.
Click Query. You should receive a message from SQL browser specifying information about the SQL Server instances on the destination server, as follows:
You can see on my test box I have two instances returned, running on ports 50101 and 50105.
I use PortQry a lot to diagnose lots of different problems. It’s also a good starting point to check whether ports are listening or not and that firewall rules appear to be set correctly.
All we’d need to do is query port 50101 and change the protocol to TCP in order to check that communication with that port is possible.
Obviously we could also use netstat on the server itself to see which ports the server is listening on:
In a command prompt, we can just type:
and we should see an output similar to the following:
I’m planning another, more in depth article about the trials and tribulations of investigating connection errors, so I’ll look at netstat and PortQry in a little more detail very shortly.