Learn more about SQL Server tools

   
   















































SQL Server Database Connectivity Testing Using PowerShell

MSSQLTips author John Grover By:   |   Read Comments (3)   |   Related Tips: More > PowerShell
Problem

You've just commissioned a new SQL Server. A user or developer insists the database is down, they can't connect and they are getting any one of several error messages. You know the SQL Server instance and SQL browser service are running, but rather than just blame the network (you know you want to!) and send them away, why not gather a little useful information to help troubleshoot the issue?  Learn how to test your SQL Server connectivity with PowerShell.

Solution

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 :).

References

Next Steps
  • Download the full PowerShell script and spend some time picking apart the PowerShell.
  • There are more books, white papers, utilities, IDEs, and blogs out there than you will ever need. Find a source you are comfortable with (like MSSQLTips.com) and dig in. Even PowerShell's built-in help is immensely valuable with several levels of help and examples.


Last Update: 1/24/2014


About the author
MSSQLTips author John Grover
John Grover is a 25-year IT veteran with experience in SQL Server, Oracle, clustering and virtualization.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, October 15, 2014 - 5:42:34 AM - Juraj Read The Tip

Exactly what I was looking for to debug connection problems.


Wednesday, March 12, 2014 - 4:48:53 PM - John Grover Read The Tip

Thanks, Mike, I hope you find it useful

-JG


Tuesday, March 11, 2014 - 11:05:42 AM - Mike Bishop Read The Tip

I love it!!!!




 
Sponsor Information