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.
