If you are new to SQL Server administration, understanding SQL Server ports is essential for managing and configuring your SQL Server instances. In this article, we will explore the different types of SQL Server ports and methods to check the currently configured ports.

Types of SQL Server Ports

SQL Server can have two types of ports:

• Static Port: A static port is always bound to a specific service or application and does not change even after a service or system restart. By default, SQL Server uses the static TCP port number 1433 for the default instance MSSQLSERVER. If you configure SQL Server to use a static port other than the default port, you need to communicate it to the clients or application owners to specify in the connection string.
• Dynamic Port: SQL Server can also be configured to use a dynamic port. In this case, you specify port number zero in the network configuration. When SQL Server restarts, it requests a free port number from the operating system and assigns it to SQL Server. The assigned dynamic port number is then written in the Windows registry, which allows applications to connect to SQL Server using that dynamic port.

Methods to Check SQL Server Ports

Now that we understand the different types of SQL Server ports, let’s explore some methods to check the currently configured ports:

Method 1: SQL Server Configuration Manager

The SQL Server Configuration Manager is the most common method to find the SQL Server port number. Follow these steps:

1. Open SQL Server Configuration Manager from the start menu.
2. Go to Network Configuration and click on the SQL instance for which you want to check the SQL port.
3. Right-click on TCP/IP and select Properties.
4. Scroll down to the IPAll group under IP Addresses. Here, you can see the TCP dynamic SQL ports and the TCP port.

Note: If you want to use a static port, remove the dynamic port value and specify a unique static port in the TCP port field. Remember to restart SQL Services for the changes to take effect.

Method 2: SQL Server Error Logs

SQL Server logs an entry in the SQL Server Error logs on each restart of SQL Services. You can use the extended stored procedure xp_readerrorlog to filter the error log using a specific keyword. Execute the following query to check for the “Server is listening on” keyword:

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

This query will provide you with the SQL Server port on which the current SQL Server instance is configured.

Method 3: Get SQL Server Port from the Registry using xp_instance_regread

You can use the extended stored procedure xp_instance_regread to retrieve the SQL Port value from the registry. Execute the following code in SSMS to get the dynamic port for the currently connected SQL instance:

DECLARE @portNumber NVARCHAR(10);
EXEC xp_instance_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
    @value_name = 'TcpDynamicPorts',
    @value = @portNumber OUTPUT;
SELECT [Port Number] = @portNumber;

You can use @Value_name=’TcpPort’ to retrieve details about the static port.

Method 4: Get SQL Server Port using sys.dm_exec_connections DMV

The sys.dm_exec_connections DMV can be used to check the TCP port of the connected SQL Server instance. Use the following query, replacing the session_id with the current session SP ID:

SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Method 5: Get SQL Server Port from the Windows Event Viewer

You can also check the SQL Server Port details from the Windows Event Viewer. After restarting SQL Services, an entry for the SQL Port will be logged in the Event Viewer. Follow these steps:

1. Go to Run and type “eventvwr” to open the Windows Event Viewer console.
2. Click on “Filter Current Log” and specify the event ID 26022.
3. The Event Viewer will display events for all installed SQL Servers. You can select a specific instance and check for the port number.

By following these methods, you can easily check the SQL Server ports and make any necessary changes to the static or dynamic port configuration.

If you have any comments or questions, feel free to leave them in the comments below.