SQL Server Port – Details Guide

In this SQL Server tutorial, we will learn about the SQL Server port numbers and how to change the SQL server 2019 default port number. We will learn how the port configurations work in SQL Server 2019 with the help of SQL Server Management Studio.

• SQL Server port number
• How to change SQL Server Port number
• SQL Server port number query
• SQL Server port number in the connection string
• How to check if SQL Server is listening on port 1433
• SQL Server port 1433
• SQL Server port 1434

SQL Server port number

In this section, we will learn about the allocation of port numbers in SQL Server 2019.

There are two types of ports allocation in SQL Server 2019:

• Static Port:
• Dynamic Port

Static Port:

In static port allocation, a user can allocate any port of his choice, which is free i.e. not allocated to any other application or process to the SQL Server instance. If the specified port number is already in use by some other application or process, you will face an error.

So, before assigning any port number to any SQL Server instance, make sure that the port number is not in use already. We will discuss later in the section that how we can assign a port number to the SQL Server 2019 instance.

Dynamic Port:

Dynamic port allocation means that the SQL Service will assign a port for the SQL Server instance dynamically. This means that whenever you will start the SQL Service, it will request the operating system for a free port number.

If any port is free, the SQL Service will assign it to the SQL Server instance. We will learn in the upcoming sections about how we can use dynamic ports for SQL Server 2019.

Read: How to install sql server 2019 express on windows 10 step by step

How to change SQL Server Port number

Now, let us see, how to find sql server port number and how to change sql server port number.

Sometimes, you will need to know the port number so that you can connect to the SQL Server instance. In this section, we will learn how we can find the port number of a SQL Server 2019 instance with the help of SQL Server Management Studio.

• To find the SQL Server 2019 instance port number, open the Start menu and click on SQL Server 2019 Configuration Manager under the Microsoft SQL Server 2019 folder as shown below in the image.

• Once you have opened the SQL Server 2019 Configuration Manager window, open Protocols for SQLEXPRESS under the SQL Server Network Configuration. Then double click on TCP/IP protocol name to open the TCP/IP Properties window.

• Make sure to enable the protocol. If it is not enabled, enable it and restart the the SQL Service.

• You can restart the SQL Service in the SQL Server Configuration Manager. Open SQL Server Services and then right click on the SQL Server (SQLEXPRESS) and click Restart.

• Once you have enabled the TCP/IP Protocol, open the TCP/IP Properties again and click on the IP Addresses tab.

• Navigate to the IPALL section and you can see the port number allocated to the SQL Server 2019 instance.
• The default TCP port number used by SQL Server is 1433.
• You can specify your own specific port number in the TCP Port column. But, make sure the port you are going to specify is free and not used by any other application.
• You can also set your TCP port allocation to be dynamically allocated.
• If you want dynamic allocation for TCP port, you can do it by specifying 0 as the port number in the TCP Dynamic Ports column.
• After specifying the port number, click on Apply and then click OK.
• Make sure to restart the SQL Server service again because only then the changes will be reflected.

SQL Server port number query

You can find the port number of SQL Server 2019 instance with the help of query also.

• sys.dm_exec_connections is a table in the SQL Server database that contains information about the connections of the database.
• There is a column in the table named local_tcp_port containing the port information
• You can execute the following query to retrieve the local IP address and the local TCP port of the same instance

select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null

SQL Server port number in connection string

You can also connect to the database engine with the port number in the connection string.

• Either you can use the IP address of the machine on which the SQL Server 2019 instance is running or you can use the name of the computer.
• The connection string consists of one of the above two i.e IP address or computer name along with the port number
• The syntax is:
  • IP address/Computer name,port number

• For example, if you want to connect to a SQL Server instance running on a machine with the IP address fe80::15f3:358c:e860:3a61%22 and port number 1433, the connection string will be

fe80::15f3:358c:e860:3a61%22,1433

How to check if SQL Server is listening on port 1433

In this section, we will know how we can cehck if the SQL Server port is listening on port 1433

• Run SQL Server Management Studio.
• Click on Connect under the Object Explorer Window and then click on Database Engine.

• You will see a window where you can enter the credentials to connect to the SQL Server Instance

• Enter the IP address of the machine on which the SQL Server is hosted, followed by the port number 1433 with ‘comma’ as the separator, and click on Connect.
• If the server is listening on port number 1433, you will be able to access the database, otherwise, it will show an error.
• If it is showing an error, you can run the SQL Server Configuration Manager utility and check or change the SQL Server instance port number. The steps are explained in the above sections.
• If you are using the SQL Server on your own machine, you can know the IP address by the ipconfig command in the command prompt.
• Open the command prompt from the start menu or by pressing Windows Key+R and type cmd and run the command prompt.
• Type the command ipconfig and you will find a list of networks where you can find your local IP address.

Read: How to create functions in SQL Server Management Studio

SQL Server port 1433

SQL Server port 1433? This is the default TCP port that client systems use to connect to the SQL Server Database engine if any TCP port number is not assigned by the user.

When you first time install the SQL Server 2019, you are not asked to specify a port number for the SQL Server. Therefore, a default instance is created and a default port number is assigned to that newly created instance. This is the default TCP port that the SQL Server listens to.

SQL Server port 1434

What is SQL Server port 1434? The port number 1434 is also a default port number, but it is the default UDP port number. It is used for SQL Server Browser Services. This port is assigned to the SQL Server named instance so that it can listen to UDP requests. The clients can use this UDP port number to connect to SQL Server if any UDP port is not assigned to the SQL Server

Thus, in this tutorial, we covered various topics related to the SQL Server 2019 port. We also discussed how the ports are allocated to the SQL Server 2019 instance.

• SQL Server port number
• How to change SQL Server Port number
• SQL Server port number query
• SQL Server port number in the connection string
• How to check if SQL Server is listening on port 1433
• SQL Server port 1433
• SQL Server port 1434