Failure to configure BizTalk BRE on named SQL Server instance‏.

I was installing BizTalk on a multi server environment and it failed while configuring BizTalk Server Business Rules Engine (BRE) with an error I never had before so I thought to write a little post about it in case it helps someone some day.

The infrastructure was as follow:

• BizTalk Server: Windows Server 2008.
• SQL Server: Windows Server 2008 cluster (2 nodes).
• The SQL Server was clustered, running on a named instance on a custom port (instead of the default TCP port 1433).
• There is a Firewall between the BizTalk Server and SQL Server with only the necessary ports open.
• SQL Browser is not running.

Symptoms:

When configuring BizTalk Server; Enterprise SSO, BizTalk Group, the BizTalk runtime and all their respective databases could be configured successfully but the BRE (Business Rules Engine) failed to be configured correctly.

When looking at the log file, I saw the following error:

[ Error RulesEngine] System.Net.Sockets.SocketException (0x80004005): No such host is known at System.Net.Dns.GetAddrInfo(String name) at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)

Solution:

This was quite puzzling as I could not see why all BizTalk services and Databases could be configured correctly except for BRE. The only thing I could think of from the error message was that the tool configuring BRE was using a different way to connect to the SQL Server machine.
While defining the SQL Server Name in the BizTalk Server Configuration tool, I used the following syntax:
<SQLServerName>,<PortNumber>\<SQLInstanceName>.
I then tried to reconfigure BRE using an alternative syntax I knew of:
<SQLServerName>\<SQLInstanceName>,<PortNumber>.
This did the trick and BRE got configured correctly. As I said before, this is probably due to the underlying API used to connect to the SQL Server box.

As it was a little disturbing for me that the former syntax works in most cases (and particularly in SQL Server Management Studio) but does not work for the Business Rule Engine part of BizTalk Server configuration, I decided to dig out a little more to find out what was the actual canonical syntax. After a quick research, I realized that it is redundant to specify both the SQL Server Instance Name and the port number because a SQL Server instance is mapped to only one TCP port. This is actually what SQL Browser does, it converts the Instance Name to the TCP port number the Named Instance listens to. On a side note, SQL Browser will map the Instance Name to a named pipe when using the named pipe protocol instead of TCP.
So the actual canonical syntaxes are either:

• <SQLServerName>\<SQLInstanceName> in case SQL Browser is running.
• <SQLServerName>,<PortNumber> for TCP connection in case SQL Browser is not running.
• <SQLServerName>\<pipe>\<pipename> for named pipe connections in case SQL Browser is not running.

Conclusion:

I shall stick to the canonical syntax instead of using uncommon alternatives, especially the one I was using: <SQLServerName>,<PortNumber>\<SQLInstanceName>.

References:

SQL Server Browser Service

SqlConnection.ConnectionString Property