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

Problem

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.

Solution

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:

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'
UNION
SELECT 'Shared Memory', iif(value_data = 1, 'Yes', 'No')
FROM sys.dm_server_registry
WHERE registry_key LIKE '%sm' AND value_name = 'Enabled'
UNION
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:

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:

And for a dynamic port:

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.