Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection

asp.net .net sql-server ssis distributed-transactions
Pavvy picture Pavvy · Jul 7, 2017 · Viewed 9.5k times · Source

I am getting following error while attempting to execute the package. I can execute individual task successfully. But whenever attempt to execute whole package it gives me error. SSIS Data flow on distributed transaction - [Execute SQL Task] Error: Failed to acquire connection "targetCon2". Connection may not be configured correctly or you may not have the right permissions on this connection.

I went through many links but nothing worked for me so starting a new thread. I checked SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?

SSIS package fail due to 'acquire connection' error

SSIS Package failing with "Failed to acquire connection" error

My distributed transaction service is running. DTC settings are correct. I am executing everything sequentially. Don't know whats wrong. I want to execute whole part in one transaction.

Please check the screenshots for more details. Project Snap and Error

Answer

Michael picture Michael · Aug 21, 2018

I just experienced this issue as well. My problem was i had switched out the params file to point to a new environment. While the ADO connection 'test connection' button worked; when running the package it failed. I rebuilt the soution and it worked great after that.

Related questions

How can I solve a connection pool problem between ASP.NET and SQL Server?

The last few days we see this error message in our website too much: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and …

Read More
How can I retrieve a table from stored procedure to a datatable?

I created a stored procedure so as to return me a table. Something like this: create procedure sp_returnTable body of procedure select * from table end When I call this stored procedure on the frontend what code do I need …

Read More
No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'.

We are using EntityFramework 6 with Code First. We have a console app that has no reference to EntityFramework but reads the connection string from its App.config. It calls the DatabaseInitializationUtilities assembly passing the connection string as a parameter. DatabaseInitializationUtilities …

Read More