SQL Browser may not be reachable through firewalls when it runs on a cluster
Hi there,
In this blog post, I would like to talk about a problem in which I was involved as a network engineer. The problem was that the SQL server instance name to port mapping wasn’t successfully done through a firewall if the SQL instance in question runs on a cluster. Let’s take a closer look at the problem and the resolution:
I asked the customer to collect simultaneous network traces from the client and SQL server side when reproducing the problem. When I checked the client side trace, I saw that the client’s request sent to SQL Browser service were unresponded and hence the client wasn’t able to locate and connect to the SQL server:
No. Time Source Destination Protocol Info
95534 2010-09-13 11:04:27.465978 192.168.1.7 192.168.29.47 UDP Source port: 4309 Destination port: 1434
|--> 0000 04 11 33 43 53 61 7c 00 .SQLSER.
95568 2010-09-13 11:04:29.197423 192.168.1.7 192.168.29.47 UDP Source port: 4310 Destination port: 1434
95600 2010-09-13 11:04:31.131993 192.168.1.7 192.168.29.47 UDP Source port: 4311 Destination port: 1434
95635 2010-09-13 11:04:33.469884 192.168.1.7 192.168.29.47 UDP Source port: 4312 Destination port: 1434
95676 2010-09-13 11:04:36.012853 192.168.1.7 192.168.29.47 UDP Source port: 4313 Destination port: 1434
95734 2010-09-13 11:04:38.561297 192.168.1.7 192.168.29.47 UDP Source port: 4316 Destination port: 1434
95775 2010-09-13 11:04:41.106219 192.168.1.7 192.168.29.47 UDP Source port: 4317 Destination port: 1434
Note: IP addresses and SQL instance names were replaced on purpose for privacy reasons.
As can be seen from the above traffic, client is constantly sending queries to the SQL browser from different source ports but no response is received and hence name resolution fails.
Then I focused on the SQL server side trace. When we look at the same activity from SQL server perspective, we see that SQL browser running on the cluster side actually returns results to those queries sent by the client side but those responses are not visible at the client side because the hardware firewall running in between drops them:
No. Time Source Destination Protocol Info
3876 2010-09-13 11:04:27.988640 192.168.1.7 192.168.29.47 UDP Source port: 4309 Destination port: 1434
3877 2010-09-13 11:04:27.988640 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4309
3914 2010-09-13 11:04:29.723015 192.168.1.7 192.168.29.47 UDP Source port: 4310 Destination port: 1434
3915 2010-09-13 11:04:29.723015 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4310
3945 2010-09-13 11:04:31.660515 192.168.1.7 192.168.29.47 UDP Source port: 4311 Destination port: 1434
3946 2010-09-13 11:04:31.660515 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4311
3991 2010-09-13 11:04:33.988640 192.168.1.7 192.168.29.47 UDP Source port: 4312 Destination port: 1434
3992 2010-09-13 11:04:33.988640 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4312
4040 2010-09-13 11:04:36.535515 192.168.1.7 192.168.29.47 UDP Source port: 4313 Destination port: 1434
4041 2010-09-13 11:04:36.535515 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4313
4103 2010-09-13 11:04:39.082390 192.168.1.7 192.168.29.47 UDP Source port: 4316 Destination port: 1434
4104 2010-09-13 11:04:39.082390 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4316
4147 2010-09-13 11:04:41.629265 192.168.1.7 192.168.29.47 UDP Source port: 4317 Destination port: 1434
4148 2010-09-13 11:04:41.629265 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4317
Those response UDP packets were dropped by the firewall because the source IP address (the SQL server’s dedicated IP address - 192.168.29.49) in the response packets was different from the destination IP address (SQL server instance’s virtual (cluster) IP address - 192.168.29.47) in the request packets. Since the firewall doesn’t evaluate the response packet as a response to previous request packet because destination IP address in the request packet doesn’t match the source IP address in the response packet, it silently drops them:
4147 2010-09-13 11:04:41.629265 192.168.1.7 192.168.29.47 UDP Source port: 4317 Destination port: 1434
|--> 0000 04 11 33 43 53 61 7c 00 .SQLSER.
4148 2010-09-13 11:04:41.629265 192.168.29.49 192.168.1.7 UDP Source port: 1434 Destination port: 4317
After our customer changed the firewall policies accordingly (by allowing UDP traffic from SQL server VLAN (with source port being set to 1434) to client VLAN), the issue was resolved. Regarding why the SQL Browser service might be returning responses from the dedicated IP address, my guess is that browser service doesn’t respond to the request within the same UDP socket but it creates a new socket to send the response back and while doing that dedicated IP address is selected instead of cluster IP address (the source address selection is OS responsibility and generally socket applications doesn’t indicate a specific source IP address when connecting to remote parties)
Hope this helps
Thanks,
Murat