Configuring Ports in SQL Server

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.

Default Ports

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:

Dynamic Ports

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.

Static Ports

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:

portqry

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:

netstat –an

and we should see an output similar to the following:

netstat

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s