SQL SSMS Ports to Connect to SQL Server

Problem

By default, SQL Server Management Studio (SSMS) connects to a Microsoft SQL Server on TCP port 1433. However, a SQL Server is not necessarily listening on that port. How do you connect SSMS to an instance of SQL Server not using the default port number?

Solution

We’ll take a look at what network ports with a simple analogy, see that most of the time we don’t need to know the port number, and how to specify a different port when it is needed.

What is a Network Port?

A network port is simply a communication endpoint that is bound to an IP address. It is assigned to a particular service running on a server. It can be an integer in the range of 0 to 65535.

Port numbers less than 1024 are privileged and cannot be used by regular services. This table has an abbreviated list of some common privileged port numbers and assigned services.

TCP Port Number	Service
20, 21	File Transfer Protocol (FTP)
23	Telnet
25	Simple Mail Transfer Protocol (SMTP)
80	Hypertext Transfer Protocol (HTTP)
123	Network Time Protocol (NTP)
443	HTTP with Secure Sockets Layer (SSL)

For our topic here, we’re concerned with port 1433 and some random port numbers higher than 1024.

Even though this mode of communication is used less and less now, calling someone at a business that has an automated switchboard is still a good comparison to connecting to a SQL Server with SSMS. In this example the business’ phone number is 123-456-7890. The person we want to speak to is at extension 1433. We call 123-456-7890. At the ‘If you know your party’s extension dial it now’ prompt we dial 1433. Then the phone rings. Now, if we have a server called WIN-N3IT475P1J9 running a SQL Server that is listening on default port 1433, the Windows machine name is analogous to the business’ phone number, 123-456-7890 and port 1433 is analogous to our party’s extension, 1433. So, instead of a human initiating a call to 123-456-7890 x1433, SSMS initiates a connection to WIN-N3IT475P1J9 / Port 1433.

Initiate a Connection to a Default Instance Listening on Default Port 1433

From Object Explorer

Connect
Database Engine…

Graphical user interface, text, application

Description automatically generated

When we enter WIN-N3IT475P1J9 and click Connect to initiate a connection, port 1433 is assumed and our connection is made.

But what if the SQL Server instance we want to connect to isn’t listening on port 1433?

SQL Server Browser

There are a couple of reasons why the SQL Server we want to connect to isn’t listening on port 1433.

The default static port on the default instance was changed
With a Named Instance
- Port numbers are assigned randomly to a free port when the SQL Server starts by default
- Instance could be configured to use a static port number

With any of these scenarios, SSMS will need to know where to connect to, just like you would if you’re making a phone call. This is where the SQL Server Browser Service comes in. Its job is to keep a map of instance names and their assigned port numbers and listen for incoming requests to SQL Server resources to guide them to the correct port. This is analogous to calling 123-456-7890 and searching for the name of the person you’re calling at the ‘Press 2 for a company directory’ prompt where the phone system’s directory is the SQL Server Browser.

Here, we have a named instance called MSSQLSERVER01 running on a Windows Server machine named WIN-N3IT475P1J9. When you initiate a connection from SSMS to WIN-N3IT475P1J9\MSSQLSERVER01, the SQL Server Browser Service on WIN-N3IT475P1J9 points it to the port assigned to the named instance. This allows us to connect without needing to have any idea what the port number is. The service does all that for us.

But what if the SQL Server Browser Service isn’t running or not working correctly for some reason?

Specifying SQL Server Port Numbers

The SQL Server Browser is disabled by default because it’s only needed if the instance is not on port 1433 so therefore is usually not necessary. But if it is used and crashed, won’t start, or is not correctly working some reason and we attempt to connect to WIN-N3IT475P1J9\MSSQLSERVER01 the ‘A network-related or instance-specific error occurred while establishing a connection to SQL Server. Verify that the instance name is correct, and that SQL Server is configured to allow remote connections’ error is displayed as shown here.

Without the SQL Server Browser Service telling SSMS where to connect, it’s trying to connect to the instance on port 1433.

We can specify the port number we want to connect to in the ‘Connect to Server’ Server name box. To do this we need to obtain the port number. You can find ways to obtain it here: Identify SQL Server TCP/IP Port Being Used.. I chose the first option and obtained it from the ERRORLOG. Opened the file in a text editor and searched for the string ‘Server is listening on’ and I see my named instance is listening on port 50152.

Simply append a comma and the port number on the end of the Server name string.

You can do it with or without the instance name. WIN-N3IT475P1J9\MSSQLSERVER01,50152 and WIN-N3IT475P1J9,50152 both resolve to the same machine name / port number.

To continue with our analogy theme, specifying the port is analogous to dialing a direct dial number to an extension, i.e., 123-456-1433.

TCP/IP Protocol Properties

Viewing the TCP/IP protocol properties in SQL Server Configuration Manager for the default and named instances will show how ports are determined. First, looking at the default instance (i.e. mssqlserver) we see that it will always be 1433 every time the instance starts as indicated by the ‘TCP Dynamic Ports’ being empty and ‘TCP Port’ = 1433.

And now looking at the TCP/IP Properties for the named instance we see ‘TCP Dynamic Ports’ = 0 ‘TCP Port’ is empty. This tells us the named instance will be assigned a random port number each time the instance is started.

Next Steps

Here are some links with further information on SSMS and SQL Server port numbers.

Resources

Community

Subscribe