Get SQL Server TCP Connections from Operating System Level

Problem

Our systems (server) administrator who got local administrator rights to our SQL Server wanted to get the remote TCP connections on SQL Server instance.

This can be done by connecting to the instance and use the Dynamic Mangement View (DMV) called dm_exec_connections to query the information. This will also require us to give VIEW SERVER STATE permission to the user. We want to give "just enough" permission to the user that needs it and by providing VIEW SERVER STATE you give the user permission to all DMVs.

Solution

Knowing that the server admins already have local administrator rights to our servers, this solution can be achieved without giving them access to the database instance.

This solution can be achieved in 3 steps using PowerShell:

1. Get all the process that are running in the server using Get-Process and filter all the SQL Server related processes.

PowerShell

Get-Process -ComputerName $servername | Where-Object {$_.ProcessName -in ('smss','SQLAGENT','sqlceip','sqlservr','sqlwriter')}

2. Get all the TCP connection of the processes that were returned on step 1 using Get-NetTCPConnection. You need to relate the Id from Get-Process with OwningProcess from Get-NetTCPConnection. This will return the IP addresses of the remote TCP connections. 

PowerShell

Get-NetTCPConnection -OwningProcess "$SQLServerProcessID"

3. Get hostname of the remote TCP connections returned on step 2 by using Resolve-DnsName. 

PowerShell

Resolve-DnsName "$TCPRemoteAddress"

Now you can combine all these steps to create a function that will return SQL Server's remote TCP connection without accessing the database instance.

You can download the function Get-SQLServerTCPConnectionsOSLevel from github.