Determine SQL Server Network Protocol Information using T-SQL and DMVs
By: Eduardo Pivaral | Updated: 2018-08-06 | Comments (1) | Related: More > 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.
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
UNION logic to give it a more user-friendly
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:
- 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.
Last Updated: 2018-08-06
About the author
View all my tips