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

Teresa Thomas 21

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.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-13T22:46:16.7266667+00:00

Hi @Teresa Thomas,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Teresa Thomas 21 Reputation points

2024-11-19T17:29:40.31+00:00

Is there no longer a solution using ADO.net to connect to Azure? I have been using the ADO.net connections and literally have many many hundreds of packages that would have to be rebuilt.
Teresa Thomas 21 Reputation points

2024-11-19T20:55:04.1766667+00:00

So - this article (yes - Feb 2023, but not that long ago) https://learn.microsoft.com/en-us/sql/integration-services/data-flow/ado-net-source?view=sql-server-ver16&source=recommendations specifically states: You can use the ADO NET source to connect to Microsoft Azure SQL Database. Connecting to SQL Database by using OLE DB is not supported. I literally have 500 packages connecting to Azure from my SQL Server 2022 environment that have been running for 4 years. (originally on SQL Server 2012 - we upgraded earlier this year to 2022). However about a month ago, I am no longer able to connect/make changes. I am trying to find a solution without essentially rewriting 500 packages. Yes - I can make OLE DB work but at a huge manpower cost. Surely there is a way to have ADO.NET continue to work.
Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-20T01:41:24.2466667+00:00

Hi @Teresa Thomas,

Thanks for the response.

Please refer the document that might help you: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16
Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-21T01:57:38.74+00:00

Hi @Teresa Thomas,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-22T20:59:21.2+00:00

Hi @Teresa Thomas,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Teresa Thomas 21 Reputation points

2024-12-04T19:15:25.1166667+00:00

Hi - sorry for the delay. I was on PTO and then it has been very busy since I got back. I am still having issues. I tried editing my connection string per the instructions in the 'using password authentication' and am getting this error: Test connection failed because of an error in initializing provider. The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception. I researched this error and wasn't able to overcome it. 2nd question - the article doesn't specify but what provider should be leveraged when creating the connection?

Accepted answer

Mahesh Kurva 1,470 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.

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.
Please sign in to rate this answer.
Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-11-14T23:15:24.4633333+00:00

Hi @Teresa Thomas,

Just checking in to see if the above answer helped. 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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Teresa Thomas 21 Reputation points

2024-12-04T20:22:58.4066667+00:00

I was able to manipulate my connection string with the attached article and get it to work with ADO.net https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16
Please sign in to rate this answer.
Teresa Thomas 21 Reputation points

2024-12-04T21:38:27.1933333+00:00

There does seem to be a bug in SSIS UI that i am not able to edit/nor create an ADO.net connection string to access Azure using the SQLclient Data Provider with the UI.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-09T09:31:31.4166667+00:00

Hi @Teresa Thomas,

If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.

Mahesh Kurva 1,470 Reputation points Microsoft Vendor

2024-12-10T09:37:56.01+00:00

Hi @Teresa Thomas,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

1 additional answer

Your answer