SQL Server Database Connectivity Testing Using PowerShell

There are several networking issues that can prevent the user from reaching or connecting to the SQL Server database.  This is especially true for a new user or developer connecting for the first time or when standing up a new database or server.  So what are the logical questions you need to answer?

• Is there a DNS entry for the server?
• Will the firewall pass the SQL browser traffic?
• Will the firewall pass SQL Server traffic?

In our environment we have separated the core services behind a firewall, NAT-ed them, and given them a different DNS domain depending on which side of the firewall you are. This allows for many permutations for connection strings, especially if you are running multiple instances on a server. I am a typical lazy DBA with enough sysadmin background to have developed the instinct to automate any solution I've tried more than twice and I've been meaning to dig into PowerShell for a while. I'm also smart enough to let others do the heavy lifting for me (so are you which is why you're reading this tip) and Google is my best friend, so here is what I came up with...

Where are you?

One of the first pieces of information the network team will want is the source IP. This is easy to find and PowerShell does a fine job with just a single line of code. I found this snippet all over the Internet, in fairness though, I've attributed borrowed code in the full script (linked at the end of the tip) to the most complete solution I found and used. This procedure will give all the IP addresses of the local machine, and may remind you if you are on a VPN.

$colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" |
 where{$_.IPEnabled -eq “True”}
 
Write-Host "# --------------------------------------"
Write-Host "# Local IP address information"
Write-Host "# --------------------------------------"
foreach($objItem in $colItems) { 
 Write-Host "Adapter:" $objItem.Description 
 Write-Host " DNS Domain:" $objItem.DNSDomain
 Write-Host " IPv4 Address:" $objItem.IPAddress[0]
 Write-Host " IPv6 Address:" $objItem.IPAddress[1]
 Write-Host " " 
} 

Here is what you get back...

# -------------------------------------- 
# Local IP address information 
# -------------------------------------- 
Adapter: Intel(R) PRO/1000 MT Network Connection
   DNS Domain: localdomain 
   IPv4 Address: 192.168.2.128 
   IPv6 Address: xxxx:xxxx:5c7:57c6:9303:40a4 

Adapter: Cisco Systems VPN Adapter for 64-bit Windows
   DNS Domain: IPv4 
   Address: 10.10.16.1 
   IPv6 Address: xxxx::xxxx:edcf:f2a3:1778 

Where are you going?

Once we know where we are, we need to verify where we are going. The script will ask for a bare host-name and try out a list of provided domains to see if DNS will give us an IP address, we'll hold on to all the IP addresses returned for use later.

Write-Host "# --------------------------------------" 
Write-Host "# DNS Verification" 
Write-Host "# --------------------------------------" 

ForEach ($Domain in $DomainList){ 
 $ComputerAddress = $Computer + $Domain 
 $IPAddress = $null 
  try { 
   $IPAddress = [System.Net.Dns]::GetHostEntry($ComputerAddress).AddressList[0].IPAddressToString 
   } catch { 
   $IPAddress = $null 
   } 
   if ($IPAddress) { 
    Write-Host "DNS reports IP address for $ComputerAddress is $IPAddress"
   if ($AddressList -notcontains $ComputerAddress) { 
    $AddressList += $ComputerAddress 
    } 
   } 
   else { 
    Write-Host "DNS lookup failure for $ComputerAddress" 
    } 
   } 

The results also tell us which FQDNs can be used be used in a connect string from this machine.

# -------------------------------------- 
# DNS Verification 
# -------------------------------------- 
DNS lookup failure for sql-host-1
DNS lookup failure for sql-host-1.core.fake.edu
DNS reports IP address for sql-host-1.admin.fake.edu is 10.10.4.123 

Can you get there from here?

Next we need to see if we can contact the SQL browser on the target server. This is a trickier bit of PowerShell that I found on Wes Brown's blog. It will query the browser on the standard port for available instances, and report back if the browser is listening. I encourage you to read the blog entry at the end of this tip to learn how he deciphered the UDP transaction, the URL is in the references section below and documented in the code.

$IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
$ToASCII = new-object system.text.asciiencoding

$UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
$UDPPacket = 0x02,0x00,0x00

$UDPClient = new-Object system.Net.Sockets.Udpclient
$UDPClient.client.ReceiveTimeout = $ConnectionTimeout
$UDPClient.Connect($IPAddress,$Port)
$UDPClient.Client.Blocking = $True 

[void]$UDPClient.Send($UDPPacket, $UDPPacket.length)
$BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint) 
[string]$Response = $ToASCII.GetString($BytesRecived)

If ($Response) { 
 $Response = $Response.Substring(3,$Response.Length-3).Replace(';;','~')
 $Response.Split('~') | ForEach { 
 $Responses += $_ 
 } 
 $UDPClient.close() 
} 

The script displays if the browser is available, then uses the information returned to try several variations of the connection string to see which, if any, will work.

# -------------------------------------- 
# Check SQL Browser Service Connection 
# -------------------------------------- 
Browser service listening on sql-host-1.admin.fake.edu

Loop through the response string ... 
ForEach ($ds in $DataSourceList) {
 
 $result = Invoke-SQL $ds "master" "select @@SERVICENAME"
 
 if ($result) { 
   Write-Host "Successful SQL connection to $ds" 
  } else { 
   Write-Host "Failed to connect to $ds" 
  } 
} 

Displaying the results of each connection string variation by connecting and executing a simple query

# -------------------------------------- 
# Check connect string permutations 
# -------------------------------------- 
Successful SQL connection to sql-host-1.admin.fake.edu 
Successful SQL connection to sql-host-1.admin.fake.edu\INST_PROD_1 
Successful SQL connection to sql-host-1.admin.fake.edu, 1433 
Failed to connect to sql-host-1.admin.fake.edu\INST_PROD_2 

With this information you can either have the user correct their connection string, or report to the networking team where your connection is having problems.  They will appreciate the amount of troubleshooting this script will have saved them and owe you one :).