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.
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 \\.\pipe\sql\query. 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 ( select @@SERVERNAME [sql_server], case when 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:
SELECT TOP 10 * 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:\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2022AG01\MSSQLServer\SuperSocketNetLib" | 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:\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2022AG01\MSSQLServer\SuperSocketNetLib" | Select-Object -ExpandProperty ProtocolList}
To get the tcp port configured for the instance:
Invoke-Command -ComputerName 'SQLVM01' -ScriptBlock { Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\SQL2019AG01\MSSQLServer\SuperSocketNetLib\Tcp" -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:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib\Tcp" ` -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:USERPROFILE\Documents\server_inventory.txt" ) -ScriptBlock { Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib\Tcp" ` -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:
<#
DISCRIPTION:
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 https://github.com/dfinke/ImportExcel. I decided to only display a warning if the module is not available, rather than throwing an ugly error. VARIABLES: 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:USERPROFILE\Documents\sql_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:USERPROFILE\Documents\sql_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:USERPROFILE\Documents\sql_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:USERPROFILE\Documents\sql_server_enabled_protocols.csv" [bool]$export_to_excel = $true [string]$excel_file_path = "$env:USERPROFILE\Documents\sql_server_enabled_protocols.xlsx" Function Get-sql-protocols { Param ( [string]$instance_name ) $computer_name = $env:COMPUTERNAME $sql_registry_root = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' $installed_sql_instances = (Get-Item 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').GetValueNames() if ($instance_name -notin ('', $null)) { # VALIDATE THE INSTANCE NAME $instance_name = $instance_name.ToUpper() if($installed_sql_instances.Contains($instance_name)) { $installed_sql_instances = $instance_name } else { 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:\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' } else { $sql_instance_registry_path = Join-Path -Path $sql_registry_root ` -ChildPath "$installed_sql_instance\MSSQLServer" } $sql_instance_SuperSocketNetLib_path = "$sql_instance_registry_path\SuperSocketNetLib" $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) } } } } $my_custom_object } 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' } else { $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 '*' } else { Write-Warning "Warning:Function Export-Excel not found. Skipping export to Excel..." } } }
Download this script from GitHub:
Get List of Protocols Enabled in SQL Server
I hope you find this article helpful, and maybe something you can adapt to your needs if ever needed.