Tip: Connecting to a SQL Server named instance on a cluster behind a firewall

Tip: Connecting to a SQL Server named instance on a cluster behind a firewall

  
Symptoms:

When trying to connect to a SQL Server named instance (on a cluster) behind a network firewall, you may receive the following error message: SQL Server does not exist or access denied.

Cause:

By default, a SQL Server connection is made to the remote 1433 port. However, some installations have more than one SQL server instance running in the same physical server, which forces the instances to choose a different port. This port selection can be done dynamically or fixed by the DBA to a specific port number. Either way, the client will always try to connect to the 1433 port by default, but after that, it will search for it's named instance using the discovery process, which means that it will use the Discovery service of the SQL Server to find the proper named instance. This service runs on the SQL server, usually on the UDP 1434 port, and uses several broadcast messages to find the named instance and negociate a port between Client and Server (named instance). Behind a network firewall, this protocol may be compromissed and blocked and no connection is made to the named instance.

Resolution:

The solution for this type of scenario is to use a fixed port in the named instance in question, and also specify that port on the client data source connection string to bypass the discovery process. In order to do so, one must just use the syntax "sqlserveripaddress,portnumber" in the connection string. Particularly, in the Hub Server Configuration Tool, in the "server" textbox insert "ipaddress,portnumber" and in the machine.config file also add the same string in the data source of the "sqlConnectionString" property in the "sessionState" entry.

For more information please refer to the Microsoft article http://support.microsoft.com/?kbid=888228

Best regards

Miguel João

Thank you for the post! We had the same issue.

You don't have to bypass the discovery process with SQL server on named instances. SQL has the option to use either dynamic ports or a fixed port for a named instance. If you enter a fixed port then you just need to open up port 1433 (or maybe 1434) and the fixed port on the SQL server and the discovery process will always allocate the same port to the clients. The advantage of doing it this way is that if for any reason you need to change the port you only have to modify the server and firewall but not all the connection strings on the clients.

From what I understand from the OP, the problem is the discovery service runs over UDP, which we block externally by default. Our network architects are very leary about opening ports on our firewall. If this were an internal application or an application that did not need to access external tables (via an extension), that would not be a problem. In this case, we have a web-facing application that needs to access external database connections that exist behind the firewall. It runs fine on our intranet, but hitting it on the DMZ causes problems. Using advanced configuration with a fixed port may be our best bet given that we use virtualized environments that may reside on the same physical server for our SQL instances. 

Thats no problem, not using discovery is a valid option, although as a qualified DBA and software developer myself I've found from experience that opening the correct ports and enabling it gives the least problems over time and doesn't make your setup any less secure, after all it's the SQL port itself that is most vulnerable point not the discovery one.