question

HillTory-5286 avatar image
0 Votes"
HillTory-5286 asked JimOng-8133 commented

SSIS: The AcquireConnection method call to the connection manager failed with error code 0xC0202009. TCP Provider: An existing connection was forcibly closed by the remote host.

There are three SQL Servers involved in this error.

1 Application Server

SQL Server 2016 Standard

2 SSIS Server

SQL Server 2016 Enterprise
Running SSIS and hosting SSISDB

3 Data Warehouse


SQL Server 2016 Enterprise

An SSIS package is executed on Server #2 which pulls data from Server #1 and writes it to Server #3. Server #2 runs many packages like this pulling data from many different systems (SQL, Excel, CSV, SQLite, etc.).

We are having intermittent errors when trying to copy data from Server #1, no other data flow is impacted, only those that query Server #1.

The Errors are always the exact same, regardless of the tables being queried and they all happen on the OLEDB Source.

Errors:
OLEDB Source failed validation and returned error code 0xC020801C

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

PackageName: Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005
Description: "Client unable to establish connection".
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005
Description: "TCP Provider: An existing connection was forcibly closed by the remote host."


I haven't found anything in the SQL Logs of either server and I'm using the most up to date OLEDB drivers available.

I ran a trace on the server with the Networking team and we identified that when the error happens, there is no traffic registered by Server #1.

Any advice or guidance with this error would be greatly appreciated.

-M3mori3s

sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@HillTory-5286,

In such case, you need to check and compare TLS 1.2 and Cipher Suites on all three servers via IIS Crypto.

What's the latest?
Did you have a chance to do that?



0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 commented

Hi @HillTory-5286,

Please confirm that you are using the following latest driver:
Microsoft OLE DB Driver for SQL Server (x64), 18.6
https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

Also, it could be TLS 1.2 and Cipher Suites related issue.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @YitzhakKhabinsky-0887,

Thank you for your answer, I can confirm we're using the latest driver.

Regards,

0 Votes 0 ·

@HillTory-5286,

Good to hear that you are using the latest driver.

In such case, you need to check and compare TLS 1.2 and Cipher Suites on all three servers via IIS Crypto.
Here is the link: IIS Crypto


0 Votes 0 ·
HillTory-5286 avatar image HillTory-5286 YitzhakKhabinsky-0887 ·

Why IIS crypto? Does this tool need to be installed on the servers in question?

Regards,
Tory

0 Votes 0 ·
Show more comments
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered JimOng-8133 commented

Hi @HillTory-5286,

You may try with below methods.

1.Restart all SQL Server services

2.Check if SQL Server browser is running and SQL Server is configured to allow remote connections.

3.Check your SSL and TLS settings from the registery settings. The settings between the client and server should be logical and consistent (i.e. if the server only allows 1.2 and the client only supports 1.0)

More details for your reference:

https://docs.microsoft.com/en-us/archive/blogs/docast/sql-connectivity-troubleshooting-checklist

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Zoe,

I haven't restarted the services yet, these are production servers so I need to get approval for the restart.

SQL Server Browser isn't running on any of the servers and all the Servers are configured to allow remote connections. I checked SSL and TLS settings and they are consistent between the three servers.

Should Server Browser be enabled?

Regards,
Tory

0 Votes 0 ·

Hi HillTory-5286,

Please restarted the services in your test environment for a try because it just a guess.

If the SQL Server Browser service is not running, the following connections do not work:

Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

Any component that generates or passes server\instance information that could later be used by other components to reconnect.

Connecting to a named instance without providing the port number or pipe.

DAC to a named instance or the default instance if not using TCP/IP port 1433.

The OLAP redirector service.

sql-server-browser-service

So just for a try.

0 Votes 0 ·

Hi @Zoehui-MSFT ,

I was able to restart the services and confirm the SQL Server Browser Service is also running now.

The Errors are still happening intermittently.

Regards,
Tory

0 Votes 0 ·
Show more comments