Determine SQL Server Network Protocol Information using T-SQL and DMVs

By:   |   Comments (2)   |   Related: > Functions System


You need to determine the network protocols and the enabled/disabled status in your SQL Server instances, but for whatever reason, you cannot access SQL Server configuration manager, or maybe you just want to know that information without having to login to the server, so you need a quick, reliable way to determine this information using T-SQL.  Also, you may need to know that information for many servers, and it could require a lot of work to have to log in to each server and gather the information manually.


Using the Dynamic Management View sys.dm_server_registry and some T-SQL formatting you can achieve this.

The DMV sys.dm_server_registry was introduced in SQL Server 2008, and it returns configuration information stored in the Windows registry for the current SQL server instance.

The values that this DMV returns are these:

  • registry_key: registry key name, used in our queries to filter the required key.
  • value_name: name of the property related to the key.
  • value_data: the value we want to retrieve.

I will show you how to determine the network protocols in your SQL server instance, and how to obtain the TCP port configured.

Determine Network Protocols – raw version

The network configuration is stored on HKLM\Software\Microsoft\Microsoft SQL Server\<yourinstance>\MSSQLServer\SuperSocketNetLib\ registry key, so if you want to obtain all the raw data with your network protocols you can just run this query:

SELECT registry_key, value_name, value_data 
FROM sys.dm_server_registry
WHERE registry_key LIKE '%SuperSocketNetLib%'

This query will return all the information:

all the information is displayed with the simple query

But if you want this information in a more concise and cleaner way, you can follow the other options below.

Determine Network Protocols – formatted version

We need to filter the registry key value and value name, we can achieve this by filtering the query at the protocol level:

  • np - Named Pipes
  • sm - Shared Memory
  • tpc - TCP/IP

Using IIF and UNION logic to give it a more user-friendly format:

SELECT 'Named Pipes' AS [Protocol], iif(value_data = 1, 'Yes', 'No') AS isEnabled
FROM sys.dm_server_registry
WHERE registry_key LIKE '%np' AND value_name = 'Enabled'
SELECT 'Shared Memory', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%sm' AND value_name = 'Enabled'
SELECT 'TCP/IP', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%tcp' AND value_name = 'Enabled'

We obtain the following information:

our instance protocols and the enabled status.

But, what if we want to obtain the TCP port configured?

Determine TCP Port

We filter at the register_key level, using the IPALL text, then filter at value_name level to obtain the port number, static or dynamic, using the TcpPort, TcpDynamicPortsproperties.

We use the following T-SQL, if a static port is configured, it will show the TCPPort value, if a dynamic port is configured, the value 0 will show in the TcpDynamicPortsproperty.

SELECT 'TCP Port' as tcpPort, value_name, value_data 
FROM sys.dm_server_registry 
WHERE registry_key LIKE '%IPALL' AND value_name in ('TcpPort','TcpDynamicPorts')

For a static port, it will show this info:

static tcp port info

And for a dynamic port:

dynamic tcp port info
Next Steps
  • You can customize the query to be able to determine all the information in one batch.
  • To be able to use this DMV you need the VIEW SERVER STATE permission on the instance.
  • If you have multiple IP addresses configured for your server, you must change the IPALL for your IPn where n is the number you want to retrieve.
  • You can check the Microsoft official documentation for more info about this DMV.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, July 6, 2023 - 2:42:32 PM - Eric Blinn Back To Top (91363)
This was exactly what I needed. Exactly. Thank you!

Monday, August 6, 2018 - 10:29:12 AM - Ameena Lalani Back To Top (77017)

 Hi Eduardo,

Good information that will be useful to many of us.

get free sql tips
agree to terms