Problem
I was asked to catalog all the port information for our SQL Servers and I’m looking to automate things, or at least gather the information without logging on to each server. When I read up on sys.tcp_endpoints from Books Online, I found the following language:
The information that is displayed regarding ports and IP addresses is not used to configure the protocols and may not match the actual protocol configuration. To view and configure protocols, use SQL Server Configuration Manager.
As a result, I know that I can’t query this catalog view, though it would be perfect if I could count on it. I know the TCP port information is stored in the registry. How can I access it?
Solution
You have several options. As the Books Online entry for sys.tcp_endpoints states, you can use SQL Server Configuration Manager. If you use Computer Management (under Administrative Tools), you can right-click on Computer Management when the interface comes up and you can switch to a different computer. If you then review Services and Applications, for instances with the SQL Server Client Tools installed on the system where you’re running Computer Management, you should see at least one entry for SQL Server Configuration Manager. In the picture below I have multiple versions of SQL Server installed, hence the repeat of the entries for SQL Server Configuration Manager. If there are SQL Server related services installed on the computer and you have the appropriate rights, you can administer them through this interface remotely.
Anything you can do locally, you can do remotely.
Gathering the Information Out of the Registry – GUI
You can also view the information in the registry. SQL Server Configuration Manager is pulling the information out of the registry, so if you know how and where the information is stored, you can get the information either using the standard registry tools or via a scripting language like PowerShell.
The standard GUI tool is Registry Editor which you can access quickly by Start | Run (or Win+R) and then entering regedit:
Once you bring up Registry Editor, you can connect to a network registry (the registry on a remote computer):
However, this is like using Computer Management and SQL Server Configuration Manager, in that you can only collect information on one computer at a time.
Gathering the Information Out of the Registry via a PowerShell Script
If you have a PowerShell interface, you can connect to a remote server (provided it has WinRM installed and configured for remote management) via Enter-PSSession:
Enter-PSSession -ComputerName <Computer> -Credential <Domain\Username>
For instance:
Enter-PSSession -ComputerName MySQLServer -Credential MyDomain\MyDBAName
You’ll be prompted to enter the password and if you’ve specified the correct credentials, WinRM is setup, and you have the appropriate rights, you’ll be connected.
Whether you’re connecting remotely or using PowerShell locally, the keys for SQL Server can be reached via the same calls:
cd HKLM: cd Software cd Microsoft cd "Microsoft SQL Server"
if you execute a dir right here, you’ll see the keys underneath. All SQL Server database engine keys will have MSSQL in the name.
SQL Server 2005:
For SQL Server 2005, you’ll see MSSQL.#. Unfortunately, the # corresponds to the order in which a particular service was installed. Merely by looking at the key you can’t tell anything further. You’ll have to navigate further. For instance:
cd MSSQL.1
Then execute a dir and see if you see a key titled MSSQLSERVER. If so, that should be a key containing information on a database engine instance.
SQL Server 2008 and higher:
Newer versions of SQL Server will prefix the key name with MSSQL and the version of SQL Server. You can navigate them the same way. For instance, if I saw a key called MSSQL10_50.SQL2008R2, I would navigate to that key via:
cd MSSQL10_50.SQL2008R2
Getting to the TCP Configuration
After we’ve navigated to the correct key, we’ll need to navigate to SuperSocketNetLib and do a dir:
cd SuperSocketNetLib dir
You should see the values corresponding to the keys beneath SuperSocketNetLib. We’re particular interested in two values grouped together: where the Display Name is TCP/IP and the Enabled value right below it, which is part of the Tcp key (see the Name on the left side):
In this particular case, we see that TCP/IP is not enabled. Therefore, we have no more work to do with this particular instance. It’s not listening on any TCP port. If, however, Enabled is set to 1, we’ll need to go further.
If Enabled is set to 1 for a particular instance, then we’ll need to navigate into the Tcp key and do another dir:
cd Tcp dir
Here’s an example of an instance with which this is the case:
Automating a Crawl Through All the Instances on a Server
One thing we can do is use PowerShell to automate the crawl through the directories. This is especially helpful if more than one instance is installed on a particular server or if you don’t happen to know off-hand what the instances are (especially if you’re still support SQL Server 2005). Here’s a script that does the work for us. There’s a lot of checking to remove out keys we don’t care about and ensure we’re only getting instances, however, the technique is the same as described above. Make sure that your execution policy is set to run the script. You can check this with Get-ExecutionPolicy and set it to the right setting with Set-ExecutionPolicy.
# Store Current Location to return to it when we're done
Push-Location;
# Let's get all the possible hives in the registry on a given system
# We'll use Where-Object to filter down to only those hives which begin with MSSQL
# This gets rid SSAS, SSIS, and SSRS for versions of SQL Server 2008 and up. It also
# gets rid of any hives that are under "Microsoft SQL Server" that aren't for instance
# configuration (or at least not what we're looking for)
Get-ChildItem -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' | Where-Object {$_.Name -like '*MSSQL*'} | foreach {
# Get Instance Name
$props = Get-ItemProperty -path $_.PSPath;
# If there is no default value, this isn't an instance. We need to trap in case the
# property doesn't exist
try {
$InstanceName = $props.psobject.Properties["(default)"].value;
}
catch {
$InstanceName = "";
}
# If there is a valid instance name, proceed farther
if ($InstanceName.length -gt 0) {
# Navigate the child keys
foreach ($key in Get-ChildItem -path $_.pspath){
# Find entries belonging to actual database engine instances
if ($key.name -like "*MSSQLServer*")
{
# Navigate to the key where the TCP settings are stored
Set-Location -path $key.pspath;
cd .\SuperSocketNetLib\tcp -ErrorAction SilentlyContinue;
# Ensure we're only reporting against the right keys
$TCPKey = Get-Location;
if ($TCPKey -like '*SuperSocketNetLib*') {
$TCPProps = Get-ItemProperty $TCPKey;
# Find out if TCP is enabled
$Enabled = $TCPProps.psobject.Properties["enabled"].value;
# Begin the reporting
Write-Host "Instance Name: $InstanceName";
Write-Host "------------------------------------------------------------------------------";
# If TCP is not enabled, there's point finding all the ports. Therefore, we check.
if ($Enabled -eq 1)
{
foreach ($Key in gci $TCPKey)
{
$IPprops = Get-ItemProperty $Key.pspath;
$IPAddress = $IPProps.psobject.Properties["IpAddress"].Value;
# For the Key IPAll, there is no IPAddress value. therefore, we trap for it.
if ($IPAddress -eq $null)
{
$IPAddress = "All"
}
Write-Host " IP Address: $IPAddress";
Write-Host " Dyn. Ports: ", $IPProps.psobject.Properties["TcpDynamicPorts"].Value;
Write-Host " Sta. Ports: ", $IPProps.psobject.Properties["TcpPort"].Value;
}
} else {
Write-Host " TCP not enabled."
}
Write-Host "------------------------------------------------------------------------------";
Write-Host "";
}
}
}
}
}
# Return to original location
Pop-Location;And running the output you’ll get something like the following:
Next Steps
- Read about other ways to learn what port SQL Server is listening on.
- Learn how to configure SQL Server to use a static port.
- See what’s required to open up a firewall for SQL Server to communicate.
- See how a security analyst (or a DBA with proper authorization) can detect SQL Server ports using nmap.
