Share via

SSIS Error "Could not create a managed connection manager." in VS2022 at runtime

Ricardo Silva 6 Reputation points
2025-06-04T01:24:06.7366667+00:00

I work with Always Encrypted databases in SQL Server with Host Guardian Services set and have to run many integration processes using SSIS.

As per Microsoft support, the ONLY driver capable of establish a connection to SQL Server using Host Guardian Service and retrieve encrypted data in SSIS is the Microsoft.SqlClient.Data.

So I create SSIS packages in VS2022 targeting SQL Server 2022 using the Microsoft.SqlClient.Data provider. During the design phase, I added the ADO Net Source component to a package, the connection was established successfully to the source database, and I was able to preview the encrypted data, all good.

However, when I tried to run the package, I received the infamous error message: ADO NET Source has failed to acquire the connection {...} with the following error message: "Could not create a managed connection manager.".

Currently, with so much threads about attacks on data, data security is paramount however MS SQL Server seems to be in the initial stages of a good, secure, documented implementation of a driver that can support those requirements. What is the point of so much security implementations if we cannot create an app to properly connect and move the data?

Always encrypted has a very poor documentation to support it and it always has been a source of headaches as, by design, you cannot join tables from different databases in the same instance that contain encrypted columns, therefore, it is necessary to move data across databases to be able to join data.

Sounds like a joke when MS started talking about SQL Server 2025, Azure SQL when they can't solve the actual driver's connection problems that has been an issue since 2016 when Always Encrypted was released...

Developer technologies | Visual Studio | Extensions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Omkara Varshitha Kunapalli (INFOSYS LIMITED) 385 Reputation points Microsoft External Staff
    2025-08-13T06:17:39.7966667+00:00

    Thank you for reaching out regarding the SSIS integration issue with Always Encrypted databases and Host Guardian Services.

    You're absolutely right that Microsoft.SqlClient.Data is currently the only supported driver for retrieving encrypted data in SSIS when Host Guardian Services are enabled. It's encouraging that your ADO.NET Source component successfully connects and previews encrypted data during design time.

    However, the error message:

    "Could not create a managed connection manager."

    is a known issue that typically occurs at runtime, not design time. This discrepancy is often due to how SSIS runtime handles connection managers differently than the design environment.

    To solve the issue please follow the below steps :

            1. Ensure SSIS Runtime Compatibility:

               a. Confirm that the SSIS runtime environment (e.g., SQL Server Integration Services runtime or                    SSIS catalog) supports the Microsoft.SqlClient.Data provider.

               b. Some SSIS runtimes may default to older providers like System.Data.SqlClient, which are                     incompatible with always Encrypted and Host Guardian Services. 2. Run Package Outside Visual Studio:     Try executing the package via DTExec or SQL Agent Job to isolate whether the issue is specific to Visual Studio’s runtime. 3. Check for Missing Dependencies:     Ensure that all required assemblies for Microsoft.SqlClient.Data are available in the runtime environment. Missing DLLs can cause this error. 4. For Always Encrypted columns, avoid using OLE DB destinations. Stick with ADO.NET destinations and ensure encryption settings are correctly configured.Microsoft is aware of these gaps and improvements are expected in future releases.

     

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.