What information do I need to use Microsoft Entra authentication methods in a local SSIS Package (other than MFA)

Question

Hi, our team got an issue transferring to Microsoft Entra authentication methods in a local SSIS package to access data from Azure SQL Database from other teams.

Backgroud:

We used to use the userID and password using SQL server authentication methods to access the database from another team. But due to the improvement security policy update, the upstream team change the DB access to Microsoft Entra ID methods. The DB owner granted us the read access to a service account (xxx@microsoft.com) of our team. We updated the visual studio to 2022 ver, and sql server tools to the most recent version. And with SSMS, we are able to access the DB with Microsoft Entra MFA method (by login the service account with a couple of clicks and typing). But when we created an SSIS package, the issue occurs when using the Microsoft Entra MFA authentication method.

Issue:

I used the Visual studio 2022, ADO.net connection manager, and .Net providers\Microsoft SqlClient Data Provider, and the Microsoft Entra MFA methods to create the SSIS package on a VM instance. After setting up the connection manager, and test connection, an account login window popped up and asked me to login to the service account. After the manual login, the test connection is successful, from the dataflow task, I was able to preview the data in the other DB. The solution also built with no errors.

However, when I start debugging, an error popped out instantly: ADO NET Source has failed to acquire the connection {XXXXXX} with the following error message: "Could not create a managed connection manager.". This caused the package cannot run locally on the VMs.

Current Investigation:

After searching the online docs and threads, it seems that for SSIS package, SSIS cannot use the Microsoft Entra MFA methods since this method using Authentication=ActiveDirectoryInteractive; but it needs an interactive Authentication option.

Help needed:

I am wondering if there are other methods to use the Microsoft Entra info to access the other team's Azure DB. After looking at a bunch of docs, may be Microsoft Entra Integrated method is the next solution, I also saw another option using the service principal authentication? But for both, I couldn't find any tutorial on how to make it happen.

I have seen other people has similar question, in this thread: https://learn.microsoft.com/en-us/answers/questions/1695933/local-ssis-package-issues-with-mfa-on-managed-inst. But no effective solution yet. could the community help provide some guidance or docs that could help resolve this issue?

Thanks in advance!

Best,

David

Answer 1

@David Liu (CSI INTERFUSION INC) As per the internal team, SSIS package execution needs a non-interactive Entra ID auth flow, like ActiveDirectoryPassword or ActiveDirectoryServicePrincipal, if the driver version you used support these authentication methods.

Regards

Geetha

Answer 2

@David Liu (CSI INTERFUSION INC) This is driver capability, driver has documentation for this. For example,  Using Microsoft Entra ID - OLE DB Driver for SQL Server | Microsoft Learn

 

ADO.NET connection manager uses System.Data.SqlClient - Driver feature support matrix - SQL Server | Microsoft Learn), which does not support AD service principal.

Regards

Geetha