Share via

Using Entra ID Auth Only in ADF + Azure SSIS IR solution

Johan Lindholm 0 Reputation points
2024-05-24T09:37:29.6033333+00:00

Hi,

We're currently working on complying with Microsoft cloud security benchmark in our tenant and ran into an issue with our Azure Data Factory service.

To comply, we need to have only Entra ID Auth on our Azure SQL DBs.

We're using Azure Data Factory and Azure SSIS IR.

The Azure SSIS IR is set to use "Use Microsoft Entra authentication with the system managed identity for Data Factory" and the SMI for ADF is in the Entra ID Admin group for our Azure SQL DB.

After we enabled Entra ID Auth Only on the Azure SQL DB our Azure SSIS IR is failing due to login failures.

When creating an Azure SSIS IR in ADF with Entra ID Auth Only, it seems like the Azure SSIS still creates two SQL Accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) that is used to run the SSIS IR.

All I've found so far is an official statement from MS at https://learn.microsoft.com/en-us/azure/data-factory/enable-aad-authentication-azure-ssis-ir

"In this scenario, Microsoft Entra authentication with the specified system/user-assigned managed identity for your ADF is only used in the provisioning and subsequent starting operations of your Azure-SSIS IR that will in turn provision and or connect to SSISDB. For SSIS package executions, your Azure-SSIS IR will still connect to SSISDB to fetch packages using SQL authentication with fully managed accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) that are created during SSISDB provisioning."

Has anyone been able to solve this type of setup with only Entra ID Auth?

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,893 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Smaran Thoomu 11,370 Reputation points Microsoft Vendor
    2024-05-27T05:28:36.51+00:00

    Hi @Johan Lindholm

    Thanks for the question and using MS Q&A platform.

    It seems that the issue you are facing is related to the fact that the Azure SSIS IR still creates two SQL accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) that are used to run the SSIS IR, even when Entra ID Auth Only is enabled on the Azure SQL DB.

    As per the official statement from Microsoft that you mentioned, Entra authentication with the specified system/user-assigned managed identity for your ADF is only used in the provisioning and subsequent starting operations of your Azure-SSIS IR that will in turn provision and or connect to SSISDB. For SSIS package executions, your Azure-SSIS IR will still connect to SSISDB to fetch packages using SQL authentication with fully managed accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) that are created during SSISDB provisioning.

    One possible workaround for this issue is to create a SQL Server login for each of the fully managed accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) and grant them the necessary permissions to access the required databases. Then, you can use these SQL Server logins to connect to the databases instead of using Entra ID Auth Only.

    Alternatively, you can consider using Azure Key Vault to store the credentials for the fully managed accounts (AzureIntegrationServiceDbo and AzureIntegrationServiceWorker) and retrieve them at runtime in your SSIS packages. This way, you can avoid storing the credentials in plain text in your SSIS packages and still use Entra ID Auth Only.

    I hope this helps. Let me know if you have any further questions.

    0 comments