Another great teaching opportunity landed in my lap this week. I got an email from a coworker looking for some help troubleshooting a SQL connection issue. He had an application server that could not connect to one of our SQL Servers.
We have a fairly complex and secure network environment. Multiple networks across multiple data centers with multiple firewalls in between. Because of this, one of the first things I typically look at is connectivity between application and database servers. The following question/answer session is how I attacked this particular problem, which led me to the actual issue.
Q: Can you connect to the TCP port that SQL Server is listening on?
A: Yes, in this case we could.
Q: What does the connection string look like?
A: The connection string was “Server=earth\ourplanet; Database=’master’; Integrated Security=SSPI;”
Q: Can the application server connect to UDP 1434 on the database server?
A: Uhhh, nope.
Bingo. The application server could not connect to the SQL Browser service on UDP 1434. So maybe now you’re asking why, and that’s kinda the gist of this post. The SQL Browser provides a valuable service when an application tries to connect to a SQL Server named instance. The SQL Browser listens on UDP 1434 and provides information about all SQL Server instances that are installed on the server. One of those pieces of information is the TCP port number that SQL is listening on. Without that info, the application has no idea how to reach to your SQL Server, and will fail to connect. This was our exact issue.
I think it will make more sense if I show you what information the SQL Browser provides. Below is a PowerShell script that you can use to poll the SQL Browser service and below that is an example of the output.
<#
.Notes
NAME: Get-SQLBrowserResponse.ps1
AUTHOR: http://www.sqlservercentral.com/blogs/sqlmanofmystery/2013/02/27/finding-sql-server-installs-using-powershell/
LASTEDIT:
5/23/2012 - CJS - Initial Release
4/4/2013 - CJS - Added better error handling template in the catch block
.Synopsis
Get all SQL Server instances from the SQL Browser using a UDP port probe
.Description
Get all SQL Server instances from the SQL Browser using a UDP port probe. SQL Browser service must be running or this script
will not return anything.
.Parameter Computer
Computer name to probe
.Example
.\Get-SQLBrowserResponse.ps1 -Computer servername
.LINK
http://www.sqlservercentral.com/blogs/sqlmanofmystery/2013/02/27/finding-sql-server-installs-using-powershell/
#>
[cmdletbinding(
DefaultParameterSetName = '',
ConfirmImpact = 'low'
)]
Param(
[Parameter(
Mandatory = $True,
Position = 0,
ParameterSetName = '',
ValueFromPipeline = $True)]
[string]$Computer
)
Begin {
$ErrorActionPreference = "SilentlyContinue";
$Port = 1434
$ConnectionTimeout = 1000
$Responses = @();
}
Process {
# Determine IP and Hostname. This block will determine if it's a valid IP address automatically.
# This allows us to pass in an IP address or a hostname to this script
[System.Net.IPAddress]$IPAddressObject = $null
if([System.Net.IPAddress]::tryparse($Computer,[ref]$IPAddressObject) -and $Computer -eq $IPAddressObject.tostring()) {
$IPaddress = $Computer
$hostinfo = [System.Net.Dns]::GetHostByAddress($IPaddress)
$HostName = $($hostinfo.HostName.split('.'))[0].ToUpper()
} else {
$IPaddress = [System.Net.Dns]::GetHostAddresses($Computer)
$Hostname = $Computer
}
$UDPClient = new-Object system.Net.Sockets.Udpclient
$UDPClient.client.ReceiveTimeout = $ConnectionTimeout
$UDPClient.Connect($IPAddress,$Port)
$ToASCII = new-object system.text.asciiencoding
$UDPPacket = 0x02,0x00,0x00
Try {
$UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
$UDPClient.Client.Blocking = $True
[void]$UDPClient.Send($UDPPacket,($UDPPacket.length))
$BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
[string]$Response = $ToASCII.GetString($BytesRecived)
$res = ""
If ($Response) {
$Response = $Response.Substring(3,($Response.Length-3)).Replace(";;","~")
$Response.Split("~") | ForEach {
$Responses += $_
}
$socket = $null;
$UDPClient.close()
}
}
Catch {
$Error[0].ToString()
$UDPClient.Close()
}
}
End {
return ,$Responses
}C:\Users\cjsommer\Documents\WindowsPowerShell [master +1 ~2 -1 !]> .\Get-SQLBrowserResponse.ps1 pluto ServerName;PLUTO;InstanceName;NOTAPLANET;IsClustered;No;Version;11.0.5058.0;tcp;5150 C:\Users\cjsommer\Documents\WindowsPowerShell [master +1 ~2 -1 !]>
You can see from the output that the SQL Browser service provides server name, instance name, if it’s clustered, SQL version, protocol and port. This server had only 1 instance on it, but if there were multiple instances they would all show up sequentially.
As I mentioned earlier, the SQL Browser service has to be accessible from the client if you want to connect to a named instance using instance name only. Just to prove it I ran a few test cases. Another thing to note, if you connect to a SQL Server by specifying the SQL Server listener port in the connection string, the SQL Browser is no longer required. I’ll show you both of these conditions in the tests below.
Here is the PowerShell script I used for my tests.
function Test-SQLBrowserConnection
{
[cmdletbinding()]
Param(
[string]$Computer ,
[int]$Port = 1434 # Default to SQL Browser service port
)
Begin {
$ErrorActionPreference = "SilentlyContinue";
$ConnectionTimeout = 1000
$Responses = @();
}
Process {
# Determine IP and Hostname. This block will determine if it's a valid IP address automatically.
# This allows us to pass in an IP address or a hostname to this script
[System.Net.IPAddress]$IPAddressObject = $null
if([System.Net.IPAddress]::tryparse($Computer,[ref]$IPAddressObject) -and $Computer -eq $IPAddressObject.tostring()) {
$IPaddress = $Computer
$hostinfo = [System.Net.Dns]::GetHostByAddress($IPaddress)
$HostName = $($hostinfo.HostName.split('.'))[0].ToUpper()
} else {
$IPaddress = [System.Net.Dns]::GetHostAddresses($Computer)
$Hostname = $Computer
}
$UDPClient = new-Object system.Net.Sockets.Udpclient
$UDPClient.client.ReceiveTimeout = $ConnectionTimeout
$UDPClient.Connect($IPAddress,$Port)
$ToASCII = new-object system.text.asciiencoding
$UDPPacket = 0x02,0x00,0x00
Try {
$UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
$UDPClient.Client.Blocking = $True
[void]$UDPClient.Send($UDPPacket,($UDPPacket.length))
$BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint)
[string]$Response = $ToASCII.GetString($BytesRecived)
If ($Response) {
Write-Host "Connection to SQLBrowser Service on '$Computer' successful" -ForegroundColor Green
$UDPClient.close()
}
}
Catch {
Write-Host "Connection to SQLBrowser Service on '$Computer' failed" -ForegroundColor Red
$UDPClient.Close()
}
}
}
function Test-SqlConnection {
[cmdletbinding()]
param (
$SQLServer
)
$SqlConnection = $null
$SqlConnection = New-Object "System.Data.SqlClient.SQLConnection"
$SqlConnection.ConnectionString = "Server=${SQLServer};Database='master';Integrated Security=SSPI; Connect Timeout = 1;"
try {
$SqlConnection.Open()
Write-Host "Connection to SQL Server Instance '$SQLServer' successful" -ForegroundColor Green
} catch {
# Throw will generate a terminating error at this point
Write-Host "Connection to SQL Server Instance '$SQLServer' failed" -ForegroundColor Red
}
}Test Case #1
Configuration for this test case:
SQL Browser service is running and accessible on UDP 1434
SQL Server listening on default port TCP 1433
SQL Server named instance
Write-Host "Test Case #1" Test-SQLBrowserConnection -Computer mercury # Test SQL Browser service Test-SqlConnection -SQLServer 'mercury\firstplanet' # Test instance name only Test-SqlConnection -SQLServer 'mercury,1433' # Test SQL port number Write-Host ""
Test Case #1 Connection to SQLBrowser Service on 'mercury' successful Connection to SQL Server Instance 'mercury\firstplanet' successful Connection to SQL Server Instance 'mercury,1433' successful
Results of this test tell me in this configuration there are no issues connecting to your SQL Server using instance name or SQL port number.
Test Case #2
Configuration for this test case:
SQL Browser service is not running and therefor inaccessible
SQL Server listening on default port TCP 1433
SQL Server named instance
Write-Host "Test Case #2" Test-SQLBrowserConnection -Computer earth # Test SQL Browser service Test-SqlConnection -SQLServer 'earth\ourplanet' # Test instance name only Test-SqlConnection -SQLServer 'earth,1433' # Test SQL port number Write-Host ""
Test Case #2 Connection to SQLBrowser Service on 'earth' failed Connection to SQL Server Instance 'earth\ourplanet' failed Connection to SQL Server Instance 'earth,1433' successful
The results for this test are slightly more interesting. When trying to connect using the instance name only, the connection fails. This can be attributed to the fact that SQL Browser service is not running. Connecting to a named instance requires the SQL Browser to be running.
The other thing to note is that the connection explicitly using SQL port 1433 is successful.
Test Case #3
Configuration for this test case:
SQL Browser service is not running and therefore inaccessible
SQL Server listening on non-default port TCP 5150
SQL Server named instance
Write-Host "Test Case #3" Test-SQLBrowserConnection -Computer pluto # Test SQL Browser service Test-SqlConnection -SQLServer 'pluto\notaplanet' # Test instance name only Test-SqlConnection -SQLServer 'pluto,5150' # Test SQL port number Write-Host ""
Connection to SQLBrowser Service on 'pluto' failed Connection to SQL Server Instance 'pluto\notaplanet' failed Connection to SQL Server Instance 'pluto,5150' successful
The results of this test are pretty much the same as test #2. The only thing I wanted to show here was a SQL Server using the non default port 5150.
Summary
If you’re connecting to a SQL Server named instance using instance name only, the SQL Browser service becomes an important piece of the connectivity puzzle. Without the SQL Browser all connections will fail. The only condition that always connected was by explicitly using the TCP port number in the connection string. That’s because you are telling the application (in the connection string) that SQL Server is listening on a specific port number. Hopefully the examples above help explain it better than I can in a sentence, and hopefully this helps someone out in the future.
