Using PowerShell to Provide TCP Port Details of Running SQL Server Instances

Mike Wolfe -

Connecting to SQL Server and troubleshooting network-related issues can be a challenging task.

It becomes even more complex when dealing with multiple instances of SQL Server or when trying to identify specific connections. To simplify this process and gain insights into TCP connections associated with SQL Server, we have a PowerShell script that provides a comprehensive overview of these connections.

This script proves invaluable in debugging connection issues, monitoring active connections, and understanding the network activity of SQL Server instances.

Static vs. Dynamic Ports in SQL Server

SQL Server instances can be configured to use static or dynamic ports.

We see these terms–static and dynamic–frequently in programming.  In the context of programming, here's the simple definition of each word:

  • Static: does not change
  • Dynamic: changes

Thus, when a SQL Server instance uses a static port, it means that it has been assigned a specific port that is the same every time the associated SQL Server process restarts.

When a SQL Server instance uses a dynamic port, it receives a new port each time the associated process restarts.

Testing Port Access

In my article on troubleshooting SQL Server connections, I recommend using the Get-NetTcpConnection PowerShell cmdlet to verify that you can see the port that the database server is listening on:

Test-NetConnection -ComputerName MyDbServer -Port 1433

Identifying the Database's Port

But how do you know which port to use when testing from your client?  

The default static port for SQL Server is 1433, but that doesn't mean that your particular database instance is listening on that port.  If your instance uses dynamic ports, it will be different every time you restart the instance.  And if you have multiple instances, each running instance will use a unique port.

We will cover two ways to identify the ports for a local database instance:

  • SQL Server Configuration Manager
  • PowerShell

SQL Server Configuration Manager

  1. Open SQL Server Configuration Manager
  2. Navigate to SQL Server Network Configuration > Protocols for {InstanceName}
  3. Right-click TCP/IP > Properties > IP Addresses tab
  4. Scroll down to the bottom and observe the IPAll section

Understanding the IPAll Section

  • If TCP Dynamic Ports is blank, then the static port will appear next to TCP Port
  • If TCP Dynamic Ports is set to 0, it means the instance uses dynamic ports but it is not currently running
  • If TCP Dynamic Ports is greater than 0, it means the instance uses dynamic ports, the instance is running, and the running instance is currently listening on the port that appears next to the TCP Dynamic Ports label

Sample Screenshots

On my development computer, I have four instances of SQL Server installed.

The default instance, named MSSQLSERVER, uses the default static SQL Server port of 1433.  The other three named instances use dynamic ports, though only one of them is running.  

Here are screenshots of the IPAll sections for each one:

  • MSSQLSERVER (running)
  • GB (running)
  • SQLEXPRESS2014 (stopped)
  • DEV2014 (stopped)

PowerShell

The rest of this article is dedicated to using PowerShell to quickly retrieve the above information.

The PowerShell Approach

The PowerShell script utilizes the Get-NetTcpConnection cmdlet to gather information about active TCP connections.

It then filters the results based on the SQL Server process ID and the LocalAddress of the connections. By restricting results to connections with a LocalAddress of 0.0.0.0, we can avoid duplicates and only return a single result for each running instance of SQL Server.

How To Use It

To use this script, follow these steps:

  1. Open an administrative PowerShell session on the computer where the SQL Server instance(s) is (are) running.
  2. Copy the provided PowerShell script below.
  3. Paste the script into the PowerShell session.
  4. Execute the script by pressing Enter.

The PowerShell Commands

# PowerShell command to list TCP connections related to SQL Server with LocalAddress as 0.0.0.0
# Provides information such as LocalAddress, LocalPort, RemoteAddress, RemotePort, State, ProcessID, and CommandLine

# Retrieve SQL Server processes
$SqlProcesses = Get-WmiObject -Class Win32_Process | Where-Object { $_.Name -eq 'sqlservr.exe' }

# Get TCP connections and filter based on SQL Server process ID and LocalAddress
Get-NetTcpConnection |
    Where-Object { $_.OwningProcess -in $SqlProcesses.ProcessId -and $_.LocalAddress -eq '0.0.0.0' } |
    ForEach-Object {
        # Store the process ID and command line for each connection
        $ProcessId = $_.OwningProcess
        $CommandLine = ($SqlProcesses | Where-Object { $_.ProcessId -eq $ProcessId }).CommandLine

        # Add ProcessID and CommandLine properties to each connection
        $_ | Add-Member -NotePropertyName 'ProcessID' -NotePropertyValue $ProcessId -PassThru |
            Add-Member -NotePropertyName 'CommandLine' -NotePropertyValue $CommandLine -PassThru
    } |
    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, ProcessID, CommandLine

Sample Usage

IMPORTANT: This script must be run from the computer that is hosting the SQL Server instance(s).  You cannot run it remotely from a client workstation.

Here is the output if I run the above script on my development computer, which has two running instances of SQL Server:

Here's a brief description of each outputted property:

  • LocalAddress: the IP address of SQL Server, as seen from the remote computer; we restrict results to show only the special address 0.0.0.0 as that eliminates duplicate entries within each SQL Server instance
  • LocalPort: the current port on which the SQL Server instance is listening
  • RemoteAddress: the IP address of the connecting computer (see samples at the end of this article)
  • RemotePort: the port on which the connecting computer is communicating (this value has almost no use when debugging)
  • State: the state of the connection, such as "Listen" or "Established"
  • ProcessID: the process ID of the running SQL Server process
  • CommandLine: the command line used to launch the running SQL Server process; this provides clues as to the version of SQL Server (via the MSSQLxx prefix) and the instance name (e.g., "GB" or "MSSQLSERVER" as shown above)

How It Works

The script starts by retrieving the SQL Server processes using the Get-WmiObject cmdlet. It filters the processes based on the process name, 'sqlservr.exe'.

Next, the script employs the Get-NetTcpConnection cmdlet to retrieve information about active TCP connections. It filters the connections based on two conditions. Firstly, it ensures that the OwningProcess of the connection is among the SQL Server process IDs. Secondly, it verifies that the LocalAddress of the connection is '0.0.0.0'.

For each filtered connection, the script stores the process ID and command line associated with the SQL Server process. It accomplishes this by iterating through the SQL Server processes and matching the process ID with the connection's OwningProcess.

Finally, the script selects and displays relevant information such as LocalAddress, LocalPort, RemoteAddress, RemotePort, State, ProcessID, and CommandLine for the filtered connections.

Useful Tweaks

You can tweak the script to include additional information according to your requirements. Here are a few examples:

Showing Remote Connections

To see all established remote connections to the database server, you can remove the -and $_.LocalAddress -eq '0.0.0.0' clause from the script. This modification will display all TCP connections associated with SQL Server, regardless of the LocalAddress.  This is useful if you want to see what remote computers are currently connecting to the local database instance(s).

Here's the updated script and associated sample output:

PS C:\> # PowerShell command to list open TCP connections related to SQL Server
>>
>> # Retrieve SQL Server processes
>> $SqlProcesses = Get-WmiObject -Class Win32_Process | Where-Object { $_.Name -eq 'sqlservr.exe' }
>>
>> # Get TCP connections and filter based on SQL Server process ID
>> #   (the LocalAddress filter is used to avoid duplicate results)
>> Get-NetTcpConnection |
>>     Where-Object { $_.OwningProcess -in $SqlProcesses.ProcessId } |
>>     ForEach-Object {
>>         # Store the process ID and command line for each connection
>>         $ProcessId = $_.OwningProcess
>>         $CommandLine = ($SqlProcesses | Where-Object { $_.ProcessId -eq $ProcessId }).CommandLine
>>
>>         # Add ProcessID and CommandLine properties to each connection
>>         $_ | Add-Member -NotePropertyName 'ProcessID' -NotePropertyValue $ProcessId -PassThru |
>>             Add-Member -NotePropertyName 'CommandLine' -NotePropertyValue $CommandLine -PassThru
>>     } |
>>     Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, ProcessID, CommandLine


LocalAddress  : ::1
LocalPort     : 49230
RemoteAddress : ::
RemotePort    : 0
State         : Listen
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : ::
LocalPort     : 14330
RemoteAddress : ::
RemotePort    : 0
State         : Listen
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : ::1
LocalPort     : 1434
RemoteAddress : ::
RemotePort    : 0
State         : Listen
ProcessID     : 8840
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

LocalAddress  : ::
LocalPort     : 1433
RemoteAddress : ::
RemotePort    : 0
State         : Listen
ProcessID     : 8840
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

LocalAddress  : 127.0.0.1
LocalPort     : 49230
RemoteAddress : 0.0.0.0
RemotePort    : 0
State         : Listen
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 61920
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63347
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63349
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63348
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 61921
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63345
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 61810
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 61812
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63344
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.7
RemotePort    : 62965
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63346
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63341
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63342
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 10.54.146.23
LocalPort     : 14330
RemoteAddress : 10.54.146.109
RemotePort    : 63343
State         : Established
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 0.0.0.0
LocalPort     : 14330
RemoteAddress : 0.0.0.0
RemotePort    : 0
State         : Listen
ProcessID     : 72312
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.GB\MSSQL\Binn\sqlservr.exe" -sGB

LocalAddress  : 127.0.0.1
LocalPort     : 1434
RemoteAddress : 0.0.0.0
RemotePort    : 0
State         : Listen
ProcessID     : 8840
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

LocalAddress  : 0.0.0.0
LocalPort     : 1433
RemoteAddress : 0.0.0.0
RemotePort    : 0
State         : Listen
ProcessID     : 8840
CommandLine   : "D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

Exporting Results to CSV

If you wish to export the results to a CSV file for further analysis or reporting, you can use the Export-Csv cmdlet. Modify the script as follows:

$SqlProcesses = Get-WmiObject -Class Win32_Process | Where-Object { $_.Name -eq 'sqlservr.exe' }

Get-NetTcpConnection |
    Where-Object { $_.OwningProcess -in $SqlProcesses.ProcessId -and $_.LocalAddress -eq '0.0.0.0' } |
    ForEach-Object {
        $ProcessId = $_.OwningProcess
        $CommandLine = ($SqlProcesses | Where-Object { $_.ProcessId -eq $ProcessId }).CommandLine
        $_ | Add-Member -NotePropertyName 'ProcessID' -NotePropertyValue $ProcessId -PassThru |
            Add-Member -NotePropertyName 'CommandLine' -NotePropertyValue $CommandLine -PassThru
    } |
    Select-Object LocalAddress, LocalPort, RemoteAddress, RemotePort, State, ProcessID, CommandLine |
    Export-Csv -Path 'C:\Path\To\Output.csv' -NoTypeInformation

Adjust the file path in the Export-Csv cmdlet to the desired location where you want to save the CSV file.

Conclusion

The provided PowerShell script is a handy tool to keep in your tool belt for monitoring and troubleshooting SQL Server connections.

By gathering and filtering TCP connection information, you gain insights into the active connections associated with SQL Server instances. It also saves time versus manually gathering this information via SQL Server Configuration Manager. This script can be easily customized and extended to meet your specific needs.

The techniques shown here help you to gain a better understanding of the network activity related to your SQL Server environment.

Acknowledgements
  • Portions of this article's body generated with the help of ChatGPT
  • One or more code samples generated with the help of ChatGPT
  • Initial draft generated with the help of ChatGPT