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

Question

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.

Answer 1

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.

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.