[MOSS2007/WSSv3] Using a clustered SQL named instance behind a firewall in SharePoint

[Issue]
For a customer I have been working on a new installation of SharePoint 2007. The environment has high availability requirements, so all components need to be redundant. The customer environment already contained a clustered SQL environment which we could use for SharePoint. We just needed to install a new SQL named instance, dedicated for the SharePoint 2007 installation.

For extra security, the SQL cluster was placed in a separate database VLAN, which was secured by a firewall. To enable communication between the SharePoint 2007 servers and the SQL named instance we opened UDP port 1434 and TCP port 4000 (port used by the instance).

After installing SharePoint 2007 on one server, I ran the SharePoint Products and Technologies Configuration Wizard to create a new farm/Configuration Database. When I used the <server>\<instance> format, SharePoint reported that it could not connect to the instance. It reported that the instance was not running, the server wasn’t a SQL server or a firewall was blocking the traffic.

[Reason]
After troubleshooting on the SharePoint and SQL servers, I turned to the firewall guys. With their troubleshooting tools we found out that:

• SharePoint connected to virtual SQL cluster name (ip 10.10.10.3) using the UDP protocol over port 1434 to determine the port of the SQL instance (see “Background Information”)


• The firewall allowed the traffic to the virtual SQL cluster name


• The server replied on the physical node ip address (ip 10.10.10.1) back to the server


• Because the server registered an outbound connection on the virtual SQL cluster name, it does not allow a reply on a different ip address and blocked the reply

Due to this behavior the SharePoint Config Wizard could not determine the port used by the instance and therefore could not connect to the instance.

After using Google we ran into the following article:
Unable to connect to a SQL Server named instance on a cluster

This issue is caused by the fact that the SQL Browser is not “cluster-aware”. It listens to all ip addresses of the server and replies always on the ip addresses of the physical node instead of on the ip address on which it received the request. The solution according to the blog post: Use SQL Server 2008 in which it is solved…….no real option at the moment.

[Solution]
To solve this issue, we created a ODBC System DSN which is configured to connect to the correct port directly and preventing the discovery process from happening:

• Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)


• Tab System DSN


• Click Add


• Select SQL Server


• Enter the name you would like the DSN to have


• Enter a description


• Enter the name of the server


• Click Next


• Click “Client Configuration”
  Clear the “Dynamically determine port” checkbox and enter the port number of the SQL instance


• Click Next


• The connection is now checked. Click Finish to save the DSN

Now use the name of the DSN as the SQL server name.

[Background information]
When a named instance is used, in most cases that instance is running on a port which is chosen randomly at installation time (customizable afterwards). Any processes that would like to use that specific instance first connect to the SQL Browser (using UDP over port 1434), which supplies the port that the instance is running on. The process then connects to the specific port.