By: John Grover | Updated: 2014-01-24 | Comments (3) | Related: > 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
- Finding SQL Server Installs Using PowerShell by Wes Brown Microsoft MVP
- Getting and Using Your IP Address in PowerShell by Sean McNamara
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.
About the author
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
Article Last Updated: 2014-01-24
