When a SQL Server instance supports the TCP/IP protocol, it listens for incoming connections on particular TCP ports. By default, a default instance is configured to use a fixed port 1433, and a named instance is configured to use dynamic ports. However, you can change the fixed port of a default instance, or make the named instance use a fixed port with Configuration Manager.
You cannot connect to a SQL Server instance using TCP/IP protocol without knowing the port number it is listening on. Although SQL Server Browser service introduced in SQL Server 2005 can help translate an instance name to its associated port number, it is not guaranteed SQL Server Browser service is always running. Windows Firewall may block the UDP port 1434 that SQL Server Browser service uses as well. You can find the port number of a SQL Server instance using Configuration Manager. However, Configuration Manager is GUI-based and only works locally. In this article, we will show a script that returns the TCP port used by an instance on a SQL Server host. This script can run locally on the SQL Server host or remotely. In an Enterprise environment, this script can be used to collect an inventory of all SQL Server instances and their port numbers from a central admin server.
In our script, we assume the ListenAll option is enabled on SQL Server 2005 (or later) servers so all the network cards listen on the same port. This is usually the case unless special security concerns and NUMA are taken into consideration. Like SQL Server Browser, our script reads the registry on SQL Server host. TCP port number is stored in a value called TCPPort under the TCP/IP registry key for a SQL Server instance. For different SQL Server products, the location of the TCP/IP registry key is different. Here we list the location for each product.
SQL Server 2000
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP
Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP
SQL Server 2005
There is no distinction between default and named instances. An instance is assigned a number based on the order it was installed. We first need to locate the registry key for the instance, which looks like
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#
# is the number assigned to the instance. The instance name is stored as the default value for this registry key. For a default instance, it is MSSQLSERVER.
Once the registry key for the instance is found, we know the TCP/IP registry key is
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#\MSSQLServer\SuperSocketNetLib\TCP\IPAll
SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll
However, if two SQL Server products exist on one host, then the location of the registry key could differ from above. For example, if SQL Server 2005 or 2008 are installed on the same host after SQL Server 2000, then the TCP/IP registry key of SQL Server 2005 or 2008 will follow the SQL Server 2000 format. Say, if a SQL Server 2000 instance INST2000 and a SQL Server 2005 instance INST2005 are installed on a host sequentially, then the registry key for the TCP/IP protocol of the SQL Server 2005 instance would reside at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INST2005\MSSQLServer\SuperSocketNetLib\TCP, not HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\TCP\IPAll.
The Get-TCPPort.ps1 script is shown below. This script accepts two arguments, a host name and an instance name. Of course, to run this script, you need the permission to query the registry on the host. By default, only members of the Administrators group can access the registry remotely.
# ======================================================================================================================
#
# NAME: Get-TCPPort.ps1
#
# AUTHOR: Yan Pan
# DATE : 7/19/2008
#
# COMMENT: This script queries the registry on a SQL Server host to find the TCP port for an instance on the host.
# =======================================================================================================================
##############################################################################
# Initialize parameters
##############################################################################
param (
[switch]$help,
[string]$hostName = {}, # Name of the SQL Server host.
[string]$instanceName = {} # Name of the SQL Server instance.
)function getTcpPort([String] $pHostName, [String] $pInstanceName)
{
$strTcpPort=””
$reg = [WMIClass]”\\$pHostName\root\default:stdRegProv”
$HKEY_LOCAL_MACHINE = 2147483650
#SQL Server 2000 or SQL Server 2005/2008 resides on the same host as SQL Server 2000
# Default instance
if ($pInstanceName -eq ‘MSSQLSERVER’) {
$strKeyPath = “SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp”
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,”TcpPort”).svalue
if ($strTcpPort) {
return $strTcpPort
}}
# Named instance
else {
$strKeyPath = “SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp”
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,”TcpPort”).svalue
if ($strTcpPort) {
return $strTcpPort
}
}
#SQL Server 2005
for ($i=1; $i -le 50; $i++) {
$strKeyPath = “SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i”
$strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,””).svalueif ($strInstanceName -eq $pInstanceName) {
$strKeyPath = “SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\SuperSocketNetLib\tcp\IPAll”
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,”TcpPort”).svalue
return $strTcpPort
}
}
#SQL Server 2008
$strKeyPath = “SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,”TcpPort”).svalue
if ($strTcpPort) {
return $strTcpPort
}return “”
}##############################################################################
# Main Program
##############################################################################
if ( $help ) {
“Usage: Get-TCPPort -hostName <string[]> -instanceName <string[]>”
exit 0
}
if ( $hostName.Length -eq 0 ) {
“Please enter a host name.”
exit 1
}
if ( $instanceName.Length -eq 0 ) {
“Please enter a server name.”
exit 1
}$tcpPort=(getTcpPort $hostName $instanceName)
# If TCP Port is not available, the server or the host doesn’t exist.
if ($tcpPort -eq “”) {
“TCP port is not found. Please check the server name and the host name.”
“If the server is a default instance, please use the MSSQLSERVER as the instance name.”
exit 2
}
$tcpPort
############################################# End of Script ##############################################################
Using this script, we can find the default instance on a SQL Server host POWERPC remotely by running
Get-TCPPort.ps1 –hostname POWERPC –instanceName MSSQLSERVER
The result is shown in the figure below.
The script returns a port number 7003. Now we know that the instance is listening on the port 7003. We can connect with the instance remotely using the port number.
Conclusion:
In this article, we illustrated how to discover the TCP port for a SQL Server instance from the registry using Windows Powershell.
