All geek questions in one place

Why does SQL Server 2008 OLE DB UDL explicitly specify port 1433?

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

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

Without the specified port, the error is:

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

In the same environment, but SQL Native Client 10.1, you do not need to explicitly specify 1433:

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

The same test against SQL Server development, OLE DB UDL does not require a default port.

Under what circumstances might you need to explicitly specify a default port?

The production system is clustered with a primary active and secondary passive structure and a cluster server to which connections are made. The development environment is just one SQL Server 2008. I do not know any other differences. If you think this plays it, why does it matter?

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

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

Thanks in advance for your help.

+4
source share
2 answers

This is a known issue unique to clustering. The reason is that the cluster manager will respond to one IP port and then send messages to the actual (physical) server, which can communicate on another port. There seems to be no fix, but you have already found a workaround.
For more information, check out this Microsoft Technet article: http://support.microsoft.com/kb/318432

+6
source

I had to do this several times. Two main reasons:

1) Firewall - Make sure port 2382 is open for SQL Browser and allows external connections. Additional Information: Server-side Server Firewalls

2) Existing SQL alias - Open "Sql Server Configuration Manager". Expand "Client Client Configuration 10.0", Aliases. If there are any items in the list, check if the port is installed. Sometimes these entries are set when working with ODBC server settings. Then they get stuck, and it seems that they can only be deleted / fixed through SQL Config Manager.

0
source

Source: https://habr.com/ru/post/1499419/

More articles:


All Articles