Programmatically find SQL Server TCP ports
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'
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
Content creator: David Barajas – Software Engineer, Microsoft
Pedro Lopes (@sqlpto) – Senior Program Manager