By default, SQL Server listens on port 1433 for TCP/IP connections and 1434 for UDP broadcasts. However, there may be instances where you need to find the specific listening port for a named instance or if the default port has been changed. In this article, we will explore different methods to determine the listening port of a SQL Server instance.

Method 1: Windows Application Event Log

Each time a SQL Server instance starts, it records its listening port(s) in the Windows Event Viewer. To find this information, follow these steps:

1. Open the Windows Event Viewer.
2. Select the ‘Application’ event log.
3. Filter on Event ID 26022.
4. Look for the event that displays the listening port in the message body.

For example, if you see the message “Server is listening on [ 127.0.0.1 49527]”, it means that the named instance ‘JOHNSQLSERVER’ is listening on port 49527.

Method 2: SQL Server Configuration Manager

Another easy and effective way to find the listening port is by using SQL Server Configuration Manager:

1. Open SQL Server Configuration Manager.
2. Expand ‘SQL Server Network Configuration’ in the left-hand pane.
3. Select the protocol menu item for the desired instance.
4. Right-click ‘TCP/IP’ and choose ‘Properties’.
5. Go to the ‘IP Addresses’ tab.
6. Scroll to the bottom and locate the ‘IPAll’ section.
7. The ‘TCP Port’ setting will display the listening port, unless the instance is set up to listen dynamically. In that case, the ‘TCP Dynamic Ports’ setting will contain the listening port.

Method 3: Windows Registry (via TSQL)

The Windows Registry also stores the listening port information. You can retrieve it using the following TSQL code:

DECLARE @InstName VARCHAR(16)
DECLARE @RegLoc VARCHAR(100)

SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'
  BEGIN
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
  END
 ELSE
  BEGIN
   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

This code will return the listening port(s) in a result set.

Conclusion

There are multiple ways to determine the listening port of a SQL Server instance. Whether you need to connect to a named instance or verify the correct port for troubleshooting purposes, these techniques will help you find the information you need. By using the Windows Event Viewer, SQL Server Configuration Manager, or querying the Windows Registry, you can confidently connect to the correct port and ensure smooth communication with your SQL Server instance.