MSSQL: Finding out TCP/IP ports a MSSQL Server Instance is running under.

Finding out what ports a MSSQL Server Instance is Configured.
May 5, 2019

1: SELECT DISTINCT local_net_address, local_tcp_port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL

2: Execute below script if SQL Server is configured with dynamic ports.
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID


3: Execute below script if SQL Server is configured with static port number

DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
  @rootkey    = ‘HKEY_LOCAL_MACHINE’,
  @key        =
‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’,
  @value_name = ‘TcpPort’,
  @value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo

Any one of the above will get you the port information.

Prakash

Leave a Reply

Your email address will not be published. Required fields are marked *