Why might SQL Server 2008 OLE DB UDL require port

Question

In a production environment, I found that port 1433 must be explicitly specified for a "Microsft OLE DB Provider for SQL Server" UDL. Like this:

Provider=SQLOLEDB.1;User ID=USER;Data Source=IP,1433

Without the port specified, the error is:

 Test connection failed because of an error in initializing provider.
 [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.

Same environment, but SQL Native Client 10.1, does not require 1433 explicitly specified:

Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;
User ID=USER;Data Source=IP;...

Same test against a development SQL Server, OLE DB UDL does not require the default port specified.

Under what circumstances might it be necessary to explicitly specify the default port?

The production system is clustered, with a primary active and secondary passive, and a cluster server to which the connections are made. Development environment is just single SQL Server 2008. I don't know of any other differences. If you think this plays into it, why might that matter?

Client to SQL Server (where UDL was created): Windows Server 2008 R2 Standard MDAC 2.8

All SQL Database Servers: Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64) Windows Server Enterprise Service Pack 2

Thanks in advance for any help.

Answer 1

I've had to do this a few times. The two main causes are

1) Firewall - Make sure port 2382 is open for the SQL Browser service and it is allowing external connections. More info: sql server firewall ports

2) Existing SQL alias - Open "Sql Server Configuration Manager". Expand "SQL Native Client 10.0 Configuration", Aliases. If there are any in the list, check to see if the Port has been set. Sometimes these entries get set when messing with ODBC server settings. Then they get stuck and it seems like they can only be removed/fixed via the SQL Config manager.

Answer 2

This is a known problem that is unique to clustering. The reason is that the cluster manager will answer on one IP/port and then hand-off communications to the actual(physical) server, which may communicate on a different port. It looks like there might not be a fix, but you have already found the workaround.
For more info, check out this Microsoft Technet article: http://support.microsoft.com/kb/318432