Setting up a Wireshark filter to view only SQL Server Browser traffic is fairly simple, once you are familiar with the tools. All SQL Server Browser traffic uses UDP port 1434 as either the origin or destination. For a server with multiple instances, the Browser helps direct client connections to the correct instance. Using Wireshark to take a detailed look at this network activity can help shed some light on client connection issues that would otherwise be a real nightmare.

Errors You Might Have Seen

Connection errors related to multiple instances include:

Cannnot connect to SERVERNAME\INSTANCENAME
Additional information:
Instance failure. (System.Data)

 
and/or

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Installing Wireshark

First you will need to install Wireshark. Wireshark is an open-source packet analyzer tool that can be downloaded here. Just go with the defaults on the install. Wireshark will also install WinPcap, which is a packet capture library. More information about WinPcap can be found here.

Running Wireshark

Running the program, and setting up a Capture Filter to narrow things down to SQL Browser network activity, isn’t too complicated:

1. Select the Interface List button from the main window or the toolbar on the top:

Interface List Button

Wireshark Interface List Button

2. Observe the live count of incoming packets. If the server has multiple network adapters/IP addresses, make sure the correct one is selected/checked

3. Click the “Options” button

4. In the “Capture Filter” field, enter: “udp port 1434” (case-sensitive, no quotes)

5. Select “Start” and the Wireshark will begin capturing any UDP packet with a source OR destination port of 1434

Closer Look at an Outgoing Packet

In this example, I will attempt to connect to a reporting server called “SQLRPT1” with an instance of “SCRATCHSQL” (sqlrpt1\scratchsql)

The first packet is sent from my machine (192.168.1.101) to “SQLRPT1” (192.168.1.102) via inbound port UDP 1434 (which is registered to IANA as “ms-sql-m” or “Microsoft-SQL-Monitor” see: http://technet.microsoft.com/en-us/library/cc959829.aspx). The outgoing port (50473) is randomly selected from the list of available Dynamic and/or Private Ports (49152-65535).

Example packet capture of SQL Server Browswer traffic from Wireshark

Example packet capture of SQL Server Browswer traffic from Wireshark (click for full size)



The data sent in the packet is the name of the instance that I am trying to connect to, “SCRATCHSQL”.

Closer Look at a Response Packet

The response packet is sent from “SQLRPT1” (192.168.1.102) over UDP port 1434 TO my machine (192.168.1.101) on the same outgoing port that was randomly selected earlier (50473).

Example response packet from a SQL Server Browser service from Wireshark

Example response packet from a SQL Server Browser service from Wireshark (click for full size)



The data contained in the response:

ServerName;SQLRPT1;
InstanceName;SCRATCHSQL;
IsClustered;No;
Version;11.0.3000.0;
tcp;1436;

 
This instructs my machine to connect to SQLRPT1 over TCP port 1436, if I want to reach the instance “SCRATCHSQL”. It also shows that the server is not clustered and that it is running version 11.0.3000.0
 
Pretty simple so far! In a follow-up post, I’ll cover troubleshooting the SQL Server Browser.

Leave a Reply

Your email address will not be published. Required fields are marked *