How you should set your firewall rules to be able to connect to a SQL Server 2005/2008 clustered instance

Let’s say you have got a SQL Server 2005/2008 cluster with two nodes. Here’s the configuration in detail :

Cluster name : MYCLUSTER

Cluster IP : 192.168.0.10

Node 1 (active node) hostname : NODE1

Node 1 IP : 192.168.0.11

Node 2 (passive node) hostname : NODE2

Node 2 IP : 192.168.0.12

The SQL Server Instance name : INST1 which is listening on TCP 1433 on both nodes (Of course the one on NODE2 is not working now)

Let’s say your client application wants to connect to this SQL Server 2005/2008 cluster with “MYCLUSTER\INST1” syntax and there’s a firewall in front of this SQL Server 2005/2008 cluster and you already opened the traffice for all 3 IPs (192.168.0.10, 192.168.0.11, 192.168.0.12) for TCP 1433.

But you’re still receiving “SQL Server does not exist or access is denied” error message.

Since you’re using “SERVERNAME\INSTANCENAME” syntax to connect to a SQL Server 2005/2008 instance your client needs to talk to SQL Server 2005/2008 Browser Service (http://msdn.microsoft.com/en-us/library/ms181087.aspx) . As soon as the SQL Server connectivity related libraries installed on your machine sees “\” character in the SQL Server name that you gave to him, it looks for a SQL Server Browser Service on “SERVERNAME” machine.

Question : Why SQL Server Browser Service is needed ?

Answer : Because the client does not have a clue which TCP port the “INSTANCENAME” instance is listening on. In our scenario the INSTANCENAME is listening on TCP 1433 of course but you may have lots of SQL Server instances on your cluster and if this is the scenario, the TCP port will be defined by this SQL Server instance randomly.

Question : Can I send a query manually to the SQL Server Browser service ?

Answer : Sure but since it’s not listening on TCP but UDP, you’ll need to use another tool than telnet. We do have a tool called “PortQryUI” which you can download it from http://www.microsoft.com/downloads/details.aspx?familyid=8355e537-1ea6-4569-aabb-f248f4bd91d0&displaylang=en . Running “PortqueryUI.exe” will show up a GUI and if you enter the servername or IP into the textbox at the top and select “Manually input query ports:” radio button. Type 1434 into “Ports to query : ” textbox and select UDP from “Protocol” combobox and click on “Query” button. Look for “Server’s response” in Query Result.  Here’s an example output from my testing machines :

As you can see, the SQL Server Browser Service returns lots of info. Especially the protocols of the instances which listens. “NP” means “Named Pipes” and TCP means TCP/IP of course 🙂

SQL Server Browser Service listens on UDP 1434 and you cannot modify this port. SQL Server Browser Service is a “Windows Service” and the first thing that you should check is if the service is running. Typing “services.msc” from Start/Run will take you to the windows services list. Also you can type “netstat -ano” from a command prompt and look for a process which is listening on UDP 1434 like  below :

(P.S. : UDP protocol list is coming after TCP Protocol lines)

As you can see from the above screenshot, a process with 2356 PID is listening on 0.0.0.0:1434 means it listens on ALL network interfaces on the machine. In our SQL Server Cluster case, while the Node1 is the active node, SQL Server Browser will listen on both 192.168.0.10 (Cluster  IP) and 192.168.0.11 (Node 1 IP). If the cluster fails over to the other node, the SQL Server Browser Service running on the Node 2 which is already listening on 192.168.0.10 (Cluster IP) and 192.168.0.11 will start responding to UDP 1434 requests.

Now it’s time to refer to the tricky note about SQL Server Browser Service in SQL Server Books Online http://msdn.microsoft.com/en-us/library/ms181087.aspx  :

Clustering

SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.

What does this “not a clustered resource” mean ? If a resource is clustered in a Windows Cluster, in a failove the resource will be transferred from the current node (or we can say “failing node”) to the other node.

SQL Server Service itself , the main SQL Server Engine is a clustered resource and when a failover occured, the resource show up on the other node as soon as failover operation completed. This means, the service that you’re expecting to connect which should be listening on TCP 1433 (or the TCP port that you set) will be always available.

Howewer, this is not true for SQL Server Browser Service since it’s not clustered. The SQL Server Browser Service should always be runnin on all your nodes in case of a failover.

Also , as I said before, the SQL Server Browser Service is listening on ALL network interfaces. If your query reach from the cluster IP or the node IP, SQL Server responds to this request. But there’s another tricky part : SQL Server Browser service respond from the Node IP. The picture will be like this :

Client (client source port: random, destination port : UDP 1434) <==> Firewall <==> SQL Server Browser Service (listens on UDP 1434 and respond from Node IP/UDP 1434)

Let’s analyze this flow from the beginning.

1) Client (let’s say its IP address is 192.168.5.5) sends a request to SQL Server Cluster IP address (192.168.0.10) since the user put MYCLUSTER\INST1 syntax (Remember when we used this syntax, a query needs to be send to MYCLUSTER host which is our cluster’s virtual network name). Of course a random client source port is defined on client machine for this request. Let’s say it’s 5449.

2) This request reaches to the firewall and since we have got a allow UDP 1434 rule in firewall, it permits this packet and pass it to the SQL Server Cluster IP

3) Since the current active node is Node1, the SQL Server Browser running on Node 1 takes the request from 192.168.0.10 (Cluster IP) and prepares the result and sends it from 192.168.0.11 (Node IP) to the Client IP (192.168.5.5) and destination port as 5449.

4) Now your firewall should permit this return request from the SQL Server Browser service Node IP (192.168.0.11) to the client (192.168.5.5, dst port : 5449). Don’t forget that on the return way the port is not UDP 1434, it’s a random port 5449.

5) The client receives the request from port 5449 and the resolution completes. At this point SQL Server Browser Service completes its job and SSRP (SQL Server Resolution Protocol) ends.

6) From this point, the database network library on the client machine starts trying to connect to the TCP port (in our example TCP 1433) it gathered with SSRP and of course this TCP 1433 port should be open in the firewall.

If you want to be sure that something is not blocked by the firewall, taking network captures with Microsoft Network Monitor from both sides (client machine and the SQL Server machine) and analyzing the captures for UDP 1434 traffice will clarify the questions before fighting with your firewall admins 😉

P.S. : SQL Server 2000 is also using SSRP protocol. But it’s not a separate service as in SQL Server 2005/2008. Thereofre don’t look for a “SQL Server Browser Service” service in Control Panel/Services on a SQL Server 2000 installation. When you started the SQL Server instance, the SQL Browser Service starts within the SQL Server Service.