Enterprise Insights connection error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

Summary

The customer encounters the following error on auto-triggered jobs from SQL Server Management Studio (SSMS):

Data Sprint: Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Diagnosis

In addition to the error described in the summary, the following error messages also appear in SSMS:

Devops_P20_Jalign_Set3: Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Client unable to establish connection due to prelogin failure".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.".

The following Connection String is in use on SSMS:

Data Source=[EIServer].database.windows.net; User ID=[EIAccount];Initial Catalog=[EIDatabase];Provider=SQLNCLI11.1;Persist Security Info=True;

Cause

Azure Support identified the potential cause: the use of the SQLNCLI11 provider. This provider is deprecated and may be causing the issue.

Solution

Azure Support recommends switching the provider to MSOLEDBSQL.

For more detailed information, refer to the following documents:

• Microsoft OLE DB Driver for SQL Server

  The OLE DB Driver for SQL Server is a stand-alone data access application programming interface (API), used for OLE DB, that was introduced in SQL Server 2005 (9.x)...

• Announcing the new release of OLE DB Driver for SQL Server

  Previously, Microsoft announced deprecation of the Microsoft OLE DB Provider for SQL Server, part of the SQL Server Native Client (SNAC). At the time, this decision was made to try...