Resolve-DNSName Slow In Returning Results For Remote IP

Problem

I need to get the host name of a server but I only have its IP address. I used Resolve-DnsName command with IP address and it took 6 seconds for the results to show up in the screen.

I also tried using the IP address of the server that I am using and it returned the values instantaneously. I am assuming it is quicker since it is getting the information locally. I am expecting that the speed to get the information would be similar for both.

I tried the command again on a 3rd server and the speed is the same with the first one.

I researched and tried additional parameter for Resolve-DnsName to help it run faster.

Solution 

1. Tried -QuickTimeout, but it still took 6 seconds to display the records.

 PowerShell

Resolve-DnsName 250.251.252.253 -QuickTimeout

2. Tried -NoRecursion, it still took 6 second for the results to return.

 PowerShell

Resolve-DnsName 250.251.252.253 -NoRecursion -QuickTimeout

3. Tried -DnsOnly, this command returned the values quickly.

PowerShell

Resolve-DnsName 250.251.252.253 -DnsOnly

Sometimes it just takes a few trial and error since the documentation did not specify that it will return the results quicker -DnsOnly parameter is used.

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.