SQL Server Database Connectivity Testing Using PowerShell

By:   |   Comments (3)   |   Related: > PowerShell


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.


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: 
   IPv6 Address: xxxx:xxxx:5c7:57c6:9303:40a4 

Adapter: Cisco Systems VPN Adapter for 64-bit Windows
   DNS Domain: IPv4 
   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 

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.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 += $_ 

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


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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, October 15, 2014 - 5:42:34 AM - Juraj Back To Top (34967)

Exactly what I was looking for to debug connection problems.

Wednesday, March 12, 2014 - 4:48:53 PM - John Grover Back To Top (29729)

Thanks, Mike, I hope you find it useful


Tuesday, March 11, 2014 - 11:05:42 AM - Mike Bishop Back To Top (29715)

I love it!!!!

get free sql tips
agree to terms