How to find SQL Server running TCP IP port

Finding the SQL Server Port Number

Recently we have installed a second SQL Server named instance on a server. Usually, we used to connect the named instance with hostname\server name. Example: xxxxxxx\dev. Now I was curious to check the dynamic assigned port for the second SQL Server named instance on a server. After a few google searches, I found various methods to find the same.

Using the SQL Server error log

If you want to check what TCP/IP ports are being used by SQL Server, you can use the T-SQL command line. But you can also find the same information in the error log also.  Connect to  SQL Server Management Studio, then connect to your SQL Server instance. From the Object Explorer, expand the server node, expand Management, and then click SQL Server Logs. In the SQL Server Logs dialog box, select the check box for Current and Previous. Select the check box for Include lower-level messages to view all available information. In the results pane, scroll to the bottom and look for the text Server is listening on followed by the port number.

Using the SQL Server error log

If you want to find out what Process ID (PID) is using a particular port number, you can use the T-SQLnetstat command line. To do this, open a Command Prompt window and type netstat -ano | find PORTNUMBER. This will return the PID that is using that port number.

Using TSQL to find the same from SQL Server error log

Use below TSQL script

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

Using TSQL to find the same from SQL Server error log

Using SQL Server Configuration Manager

To find the port that SQL Server is running on: -Open the SQL Server Configuration Manager tool -Expand the SQL Server Network Configuration node in the left-hand pane -Select the Protocols for option in the right-hand pane -In the Protocols for window, look for the TCP/IP protocol – it should be enabled and listed first -The port number for the TCP/IP protocol will be listed in the right-hand column, next to the IP Address field

Using SQL Server Configuration Manager

Finding the SQL Server Port Number in the Registry

If you need to know the port number that SQL Server is using, the most reliable way is to look in the registry. The registry is a central repository for information about all the software installed on a Windows system. To access the registry, go to Start > Run and type regedit. Once in the registry, expand the HKEY_LOCAL_MACHINE branch. Under this branch, expand SOFTWARE. Next, expand Microsoft. Finally, expand Microsoft SQL Server. Under the Microsoft SQL Server branch, you’ll see a key for each installed instance of SQL Server. The name of each key will be something like MSSQL$INSTANCENAME. Under each of these keys, you’ll see a key called Parameters. If you expand this key, you’ll see a list of all the parameters that were set when the instance was installed. One of these parameters will be tcp port, and the value for this parameter will be the port number that SQL Server is using.

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;
GO

Finding the SQL Server Port Number using DMV

We can also use sys.dm_exec_connections DMV to find out the TCP port.

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

Conclusion

The most straight-forward way is to check the SQL Server error log, but this requires that you have access to the server itself. – If you don’t have direct access to the server, you can check the Windows Event Viewer for information about which ports SQL Server is using. – Finally, you can use the netstat command to list all the active TCP/IP connections on a machine, which will include the port that SQL Server is using.