Blog Post

Getting list of protocols enabled in a SQL Server instance


protocols enabled in a SQL Server instance

There are number of ways you can find out what protocols are enable in a SQL Server instance. The obvious first one is the SQL Server Configuration Tool on the server where the instance is installed. Then you can also look into the sql server error log when the instance first starts up.  These two I think a DBA might be the most familiar with. 

Then, you could even query the error log using the sp_readerrorlog tsql command or use some text searching tool to find that information. The query method however I would say is not a precise one.

But then it's not like you really really need to know this information. After all, the usual TCP/IP and Named Pipe protocols required to connect remotely are "generally" enabled by default, even the Shared Memory for local connections, depending on the version of SQL Server and the configuration options you choose during the installation, see Default SQL Server Network Protocol Configuration for more information.

The point is, historically and even today if you are installing a default instance of SQL Server, chances are one or two protocols are already enabled using their default configuration, which in case of TCP/IP is the TCP port 1433 and for Named Pipe it is \.pipesqlquery. Both are well-known to the us humans, and to the client tools and the API libraries these tools use so all you have to provide to connect to the SQL Server is the server name, and optionally (I might add, less often if not rarely) the specific protocol you want to use for connection.

So why should anybody care and go through the trouble of looking up that information? If the need does arises, which sometimes it does, it is to troubleshoot connection issues and maybe for security reasons.  However, I also often see old applications that are still not aware that you can now have multiple instances of SQL Server on the same server, by way of installing the additional instances as Named instance, that relies on the SQL Server Browser service to automatically map the instance name to the respective port number each SQL instance is configured for. So such apps do need to know the TCP port number in order to connect to any sql instance not running on the age old default port 1433.

Ok, a caveat is worth mentioning here. the support for Named SQL Server instances have been around since, what 2005? So, I don't think that the developers of such apps, which are often third party vendors, have never heard of it. It maybe that they are now too small of an organization to afford to have enough budget to rewrite their code, while it's customers are still stuck using it for lack of viable alternatives.

Whatever maybe the case,  for me, it is often a necessity than a mere "nice to know", or only to troubleshooting some issues.

So that being said, I can think of many ways to find protocol configuration. But to be frank, even if I tried,  I can't say exactly how many ways you can get this information, especially if you also throw in various  programming languages and APIs etc. The method that I do want to discuss here though involves reading the information from the windows registry, using the PowerShell, basically relying on it's *Item* cmdlets that have been part of PowerShell from the beginning. So I am hoping that the PowerShell statements here would work on any and all versions. In essence, there is no dependency on SQLPS or SQLServer modules, or any other for that matter. Except, if you want to export the results out to an Excel file later.

But first let me share couple queries as well. The first one will show the state of standard protocols i.e. TCP/IP, Named Pipes and Shared Memory.

;with cte as
@@SERVERNAME [sql_server],
casewhen right(registry_key, 2) = 'Np' then 'Named Pipe'
when right(registry_key, 2) = 'Sm' then 'Shared Memory'
when right(registry_key, 3) = 'Tcp' then 'TCP Ip'
end [protocol],
value_name [property],
value_data [property_value],
case value_data when 0 then 'Disabled'
when 1 then 'Enabled'
end [status]

from sys.dm_server_registry
where registry_key like '%SuperSocketNetLib%'
select * from cte
where [protocol] in ('Named Pipe', 'Shared Memory', 'TCP Ip')
and [property] = 'Enabled'

And to get the TCP Port number:

FROM sys.dm_server_registry
WHERE registry_key like '%SuperSocketNetLib%'
  AND registry_key not like '%AdminConnection%'
  AND value_name in ('TcpDynamicPorts','TcpPort')
  AND value_data IS NOT NULL
  AND value_data != ''
  AND value_data != '0'

Both of these queries you can run as a Multi Server Query against all your registered SQL Servers in a one go.

Using the PowerShell

Caveat: In the PowerShell statements that follows, I am making an educated and informed, guess on where the SQL Server registry entries are placed by the SQL Server installer. These locations per se are well documented but can change or be different in future versions or in some old versions of SQL Server.

To  be fair, I also think using PowerShell is bit complicated than what we all are used to as a DBA, I might even say it is a hassle.  And, if you think about the feature and behavior changes you might encounter among different versions, not to mention the Dependency Hell, it can be a nightmare!

But, I do think it is more versatile, that comes handy when managing a mid to large number of SQL Servers.  So, lets jump right in.

If you are already logged into the server, you can run the following in the PowerShell and it will return the list of SQL Server protocols enabled on a given SQL instance, which I have lighted in the cmdlet:

Get-ItemProperty "HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerSQL2022AG01MSSQLServerSuperSocketNetLib"  | Select-Object -ExpandProperty ProtocolList

In the following example, the sql instance has tcp and named pipes enabled:

And to get the same information remotely, you can use the same command with Invoke-Command cmdlet:

Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock {
Get-ItemProperty "HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerSQL2022AG01MSSQLServerSuperSocketNetLib" | 
    Select-Object -ExpandProperty ProtocolList}

To get the tcp port configured for the instance:

Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock {
Get-ItemProperty "HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerSQL2019AG01MSSQLServerSuperSocketNetLibTcp" -Name TcpPort} |
    Select-Object TcpPort

And suppose you want to get the tcp port number for all SQL instances installed on a remote computer:

Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock {
Get-ItemProperty "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server*MSSQLServerSuperSocketNetLibTcp" `
    -Name TcpPort -ErrorAction SilentlyContinue | 
    select @{n='Instance' ;e={$_.PSPath.split('')[5]}}, TcpPort, PSPath} | 
    Format-Table -Property PSComputerName, Instance, TcpPort

Now here, things start to get more interesting in PowerShell because you can pass multiple servers to the -ComputerName parameter above, and not have to worry about providing in it a specific SQL instance name, which often is different on different host servers.

You can even pass your entire server inventory to it from a plain text file, with no headers and only a single value per row:

Invoke-Command -ComputerName (Get-Content -Path "$env:USERPROFILEDocumentsserver_inventory.txt" ) -ScriptBlock {
Get-ItemProperty "HKLM:SOFTWAREMicrosoftMicrosoft SQL Server*MSSQLServerSuperSocketNetLibTcp" `
    -Name TcpPort -ErrorAction SilentlyContinue | 
    select @{n='Instance' ;e={$_.PSPath.split('')[5]}}, TcpPort, PSPath} | 
    Format-Table -Property PSComputerName, Instance, TcpPort

Here is the final script, as complete of a script as I could make it at this moment without adding too much complexities to it. It already contains some explanation notes, which I hope helps:

The script will return the enabled protocols in a SQL instance on a 
remote server and their pertinent properties. You do have to give it 
a server name. You can even provide multiple servers or even a text 
file with list of all your servers. The SQL instance is an optional 
variable, in which case the script will return protocol information 
on all sql instances installed in the given server/s. A nice thing 
about this script is that it returns this information as a PowerShell 
object, an array object, to be specific. That makes it easier not 
only to display results on the screen, it also allows you to pipe 
the results to a Comma  Separated Values file (CSV) or even Microsoft 
Excel if the required module, ImportExcel,  for it is available on 
the computer where you are running this script from. You can install 
the module from  I decided to 
only display a warning if the module is not available, 
rather than throwing an ugly error.
1.   $server_name
     A value for this variable is required
     There are 3 ways you can assing it a value
     a.  A single server name
         $server_name = 'MySQLServer'
     b.  Multiple server names as an array
         $server_name = @('MySQLServer', 'MySQLServer2', 'MySQLServer3')
     c.  Import server names from a plain text file
         $server_name = Get-Content -Path "$env:USERPROFILEDocumentssql_servers.csv"         
2.   $instance_name
     Name of the SQL Server instance. For the default sql instance, 
     the value should be MSSQLSERVER, for example: $instance_name  = 'MSSQLSERVER'
     If $instance_name is omitted or set to $null, the script will return protocols
     information for all installed sql instances
     You cannot specify $instance_name if the $server_name contains multiple servers.
     This limitation can be overcome, like some others, but I decided not to at this point.
3.   $export_to_csv
     This is a $true/$false value. If $true then the script will export the results to 
     a CSV file.
4.   $csv_file_path
     Path and name of the CSV file. 
     Default value is "$env:USERPROFILEDocumentssql_server_enabled_protocols.csv"
5.   $export_to_excel
     This is a $true/$false value. If $true then the script will export the results to
     an Excel file only if the Export-Excel is available.   
6.   $excel_file_path
     Path and name of the Excel file. 
     Default value is "$env:USERPROFILEDocumentssql_server_enabled_protocols.xlsx"

# Required variables
[string]$server_name     = 'SQLMV01'
[string]$instance_name   = $null # 'MSSQLSERVER'
# Export options
[bool]$export_to_csv     = $false
[string]$csv_file_path   = "$env:USERPROFILEDocumentssql_server_enabled_protocols.csv"
[bool]$export_to_excel   = $true
[string]$excel_file_path = "$env:USERPROFILEDocumentssql_server_enabled_protocols.xlsx"
Function Get-sql-protocols
$computer_name = $env:COMPUTERNAME 
$sql_registry_root        = 'HKLM:SOFTWAREMicrosoftMicrosoft SQL Server'
$installed_sql_instances = (Get-Item 'HKLM:SOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL').GetValueNames()
if ($instance_name -notin ('', $null))
    $instance_name = $instance_name.ToUpper()
        $installed_sql_instances = $instance_name
        THROW "Error: SQL instance name $instance_name is invalid."
$my_custom_object = @()
foreach($installed_sql_instance in $installed_sql_instances)
    if($installed_sql_instance -eq 'MSSQLSERVER')
        $sql_instance_registry_path = 'HKLM:SOFTWAREMicrosoftMSSQLServerMSSQLServer'
        $sql_instance_registry_path = Join-Path -Path $sql_registry_root `
                                       -ChildPath "$installed_sql_instanceMSSQLServer"
    $sql_instance_SuperSocketNetLib_path = "$sql_instance_registry_pathSuperSocketNetLib"
    $protocols = Get-ChildItem $sql_instance_SuperSocketNetLib_path
    foreach ($protocol in $protocols)
        foreach($protocolp in $protocol.GetValueNames())
            $my_custom_object += [PSCustomObject]@{
                    computer_name    = $computer_name
                    sql_instance     = $installed_sql_instance
                    protocol_name    = $protocol.PSChildName
                    property_name    = $protocolp
                    property_value   = $protocol.GetValue($protocolp)
if($server_name.GetType().Name -ne 'String' -and $instance_name -notin ('', $null))
    THROW 'Error: A value of named instance in $instance_name is not compatible with an array for the $server_name'
    $sql_protocols = Invoke-Command -ComputerName $server_name   `
                                    -ScriptBlock ${Function:Get-sql-protocols} `
                                    -ArgumentList $instance_name
    $sql_protocols | Format-Table  computer_name, sql_instance, protocol_name, property_name, property_value
    # Export to a CSV file
    if ($export_to_csv)
        Write-Information 'Exporting to CSV file....'
        $sql_protocols | Select-Object PSComputerName, sql_instance, protocol_name, property_name, property_value | 
                         Export-Csv -Path $csv_file_path -Force -NoTypeInformation
    # Export to Excel file
    if ($export_to_excel)
        if (Get-Command -Name Export-Excel -ErrorAction SilentlyContinue)
            Write-Information  'Exporting to Excel file....'
            $sql_protocols | Select-Object PSComputerName, sql_instance, protocol_name, property_name, property_value | 
                             Export-Excel -Path $excel_file_path -WorksheetName "SQLProtocols" `
                             -TableName "SQLProtocols" -TableStyle Light9 -AutoSize -NoNumberConversion '*'
            Write-Warning "Warning:Function Export-Excel not found. Skipping export to Excel..."

I hope you find this article helpful, and maybe something you can adapt to your needs if ever needed.


Original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating