Sql-server – Why might SQL Server 2008 OLE DB UDL require port 1433 explicitly specified

oledbsql serversql-server-2008

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.

Best Answer

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

Related Topic