Share via

Programmatically find SQL Server TCP ports

  • Article

There are many instances in SQL Server management where one person would like to find all the TCP ports that SQL Server is binding to. Finding all the TCP ports that SQL Server is listening on might be for security related auditing, connectivity troubleshooting, or for some form of automation. One such example is used in BPCheck.

In the past, attempting to find what ports SQL Server was a tedious task. Common methods were to either parse the event log (if it was still available since last startup), or scan the registry. You can still find them in several blogs and other sources. In SQL Server 2008 and SQL Server 2008 R2, you would scan the registry using a query like so:

 SELECT MAX(CONVERT(VARCHAR(15),value_data)) FROM sys.dm_server_registry WHERE registry_key LIKE '%MSSQLServer\SuperSocketNetLib\Tcp\%' AND value_name LIKE N'%TcpPort%' AND CONVERT(float,value_data) > 0;

 SELECT MAX(CONVERT(VARCHAR(15),value_data)) FROM sys.dm_server_registry WHERE registry_key LIKE '%MSSQLServer\SuperSocketNetLib\Tcp\%' AND value_name LIKE N'%TcpDynamicPort%' AND CONVERT(float,value_data) > 0;

This is not a very clean way of doing it, but it works for the most part. There is at least one issue with this approach however, if you make changes (either in the registry or in a config tool) without restarting SQL Server, this data will be incorrect. There might also be additional concerns with querying the registry from T-SQL that has someone not willing to run this query.
 
But there is a better way. In SQL Server 2012, a new DMV was added to query the TCP ports that SQL Server is currently using. The DMV can be invoked by querying sys.dm_tcp_listener_states . To get the same information as from the queries above (only IP v4 port, not DAC), you can simply execute:

 SELECT port FROM sys.dm_tcp_listener_states WHERE is_ipv4 = 1 AND [type] = 0 AND ip_address <> '127.0.0.1'

image
This gets both static and dynamic ports in a single query. If you only allow DAC from local connections, filtering by 127.0.0.1 will hide the DAC specific ports.
Here’s the full output for the DMV on a different machine:

 SELECT * FROM sys.dm_tcp_listener_states

clip_image002

Content creator: David Barajas – Software Engineer, Microsoft

Pedro Lopes (@sqlpto) – Senior Program Manager

Comments

  • Anonymous
    April 27, 2017
    When an external user session exists one can use also the following script based on sys.dm_exec_connections DMV (eventually joined with sys.endpoints DMV): SELECT DISTINCT EXC.endpoint_id , EXC.local_net_address , EXC.local_tcp_port FROM sys.dm_exec_connections EXC WHERE EXC.endpoint_id IS NOT NULL AND local_tcp_port IS NOT NULLThe use of Max function in the first script is tricky, because one can configure the SQL Server to listen on different ports on different IP addresses (https://blogs.msdn.microsoft.com/sqlblog/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses/). A SELECT DISTINCT I suppose would at least give the hint that one deals with such a case. The detriment comes in code, that one has to handle multiple entries.