Share via

ADO.NET connection failure to Azure Database from VS 2022 SSIS Package running against SQL Server 2022 using Microsoft SQLClient Data Provider

Teresa Thomas 1 Reputation point
2024-11-12T13:59:46.06+00:00

I have a SSIS package running on SQL Server 2022 in VS2022 that connects to a 3rd party Azure database as a source with ADO.NET using Microsoft Entra Password that has been running successfully for a few years. However, recently I needed to make changes and can no longer connect using the ADO.NET SQLclient Data Provider. I upgraded to the Microsoft SQLClient Data Provider. The test connection works and the package can see the tables and columns. However, when I try to run the package, I receive this error message "Error: 0xC0208449 at Data Flow Task, ADO NET Source xxxxxxx : ADO NET Source has failed to acquire the connection {C1D9447A-4162-42A7-968B-64B4907EE375} with the following error message: "Could not create a managed connection manager.". The user/password works in SSMS and from other 3rd party database tools. this is similar to Ricardo Silva's inquiry https://learn.microsoft.com/en-us/answers/questions/1611810/ssis-error-could-not-create-a-managed-connection-m but i do not have an always encrypted table and none of the suggestions there worked. I am really stumped.

Azure SQL Database
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,600 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mahesh Kurva 890 Reputation points Microsoft Vendor
    2024-11-12T23:13:32.4366667+00:00

    Hi @Teresa Thomas,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    It seems like you are having trouble connecting to your 3rd party Azure database as a source using the ADO.NET SQLclient Data Provider in your SSIS package.

    Microsoft.Data.SqlClient driver is not supported in SQL 2022 and below. If you need msi or Microsoft Entra ID-based authentication method, please use Oledb Connection Manager instead.User's image

    For more information, please refer the document: https://learn.microsoft.com/en-us/sql/integration-services/connection-manager/ado-net-connection-manager?view=sql-server-ver16#adonet-connection-manager-troubleshooting

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

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.