Enable Microsoft Entra authentication for Azure-SSIS integration runtime

APPLIES TO: Azure Data Factory Azure Synapse Analytics (Preview)

This article shows you how to enable Microsoft Entra authentication with the specified system/user-assigned managed identity for your Azure Data Factory (ADF) or Azure Synapse and use it instead of conventional authentication methods (like SQL authentication) to:

• Create an Azure-SSIS integration runtime (IR) that will in turn provision SSIS catalog database (SSISDB) in Azure SQL Database server/Managed Instance on your behalf.

• Connect to various Azure resources when running SSIS packages on Azure-SSIS IR.

For more info about the managed identity for your ADF, see Managed identity for Data Factory and Azure Synapse.

Enable Microsoft Entra authentication on Azure SQL Database

Azure SQL Database supports creating a database with a Microsoft Entra user. First, you need to create a Microsoft Entra group with the specified system/user-assigned managed identity for your ADF as a member. Next, you need to set a Microsoft Entra user as the Active Directory admin for your Azure SQL Database server and then connect to it on SQL Server Management Studio (SSMS) using that user. Finally, you need to create a contained user representing the Microsoft Entra group, so the specified system/user-assigned managed identity for your ADF can be used by Azure-SSIS IR to create SSISDB on your behalf.

Create a Microsoft Entra group with the specified system/user-assigned managed identity for your ADF as a member

You can use an existing Microsoft Entra group or create a new one using Azure AD PowerShell.

1. Install the Azure AD PowerShell module.

2. Sign in using Connect-AzureAD -AzureEnvironmentName AzureChinaCloud, run the following cmdlet to create a group, and save it in a variable:

$Group = New-AzureADGroup -DisplayName "SSISIrGroup" `
                          -MailEnabled $false `
                          -SecurityEnabled $true `
                          -MailNickName "NotSet"

The result looks like the following example, which also displays the variable value:

$Group

ObjectId DisplayName Description
-------- ----------- -----------
6de75f3c-8b2f-4bf4-b9f8-78cc60a18050 SSISIrGroup

3. Add the specified system/user-assigned managed identity for your ADF to the group. You can follow the Managed identity for Data Factory or Azure Synapse article to get the Object ID of specified system/user-assigned managed identity for your ADF (e.g. 765ad4ab-XXXX-XXXX-XXXX-51ed985819dc, but do not use the Application ID for this purpose).

   Add-AzureAdGroupMember -ObjectId $Group.ObjectId -RefObjectId 765ad4ab-XXXX-XXXX-XXXX-51ed985819dc
   

   You can also check the group membership afterwards.

   Get-AzureAdGroupMember -ObjectId $Group.ObjectId
   

Configure Microsoft Entra authentication for Azure SQL Database

You can Configure and manage Microsoft Entra authentication for Azure SQL Database using the following steps:

1. In Azure portal, select All services -> SQL servers from the left-hand navigation.

2. Select your Azure SQL Database server to be configured with Microsoft Entra authentication.

3. In the Settings section of the blade, select Active Directory admin.

4. In the command bar, select Set admin.

5. Select a Microsoft Entra user account to be made administrator of the server, and then select Select.

6. In the command bar, select Save.

Create a contained user in Azure SQL Database representing the Microsoft Entra group

For this next step, you need SSMS.

1. Start SSMS.

2. In the Connect to Server dialog, enter your server name in the Server name field.

3. In the Authentication field, select Active Directory - Universal with MFA support (you can also use the other two Active Directory authentication types, see Configure and manage Microsoft Entra authentication for Azure SQL Database).

4. In the User name field, enter the name of Microsoft Entra account that you set as the server administrator, e.g. testuser@xxxonline.com.

5. Select Connect and complete the sign-in process.

6. In the Object Explorer, expand the Databases -> System Databases folder.

7. Right-click on master database and select New query.

8. In the query window, enter the following T-SQL command, and select Execute on the toolbar.

   CREATE USER [SSISIrGroup] FROM EXTERNAL PROVIDER
   

   The command should complete successfully, creating a contained user to represent the group.

9. Clear the query window, enter the following T-SQL command, and select Execute on the toolbar.

   ALTER ROLE dbmanager ADD MEMBER [SSISIrGroup]
   

   The command should complete successfully, granting the contained user the ability to create a database (SSISDB).

10. If your SSISDB was created using SQL authentication and you want to switch to use Microsoft Entra authentication for your Azure-SSIS IR to access it, first make sure that the above steps to grant permissions to the master database have finished successfully. Then, right-click on the SSISDB database and select New query.

    1. In the query window, enter the following T-SQL command, and select Execute on the toolbar.

       CREATE USER [SSISIrGroup] FROM EXTERNAL PROVIDER
       

       The command should complete successfully, creating a contained user to represent the group.

    2. Clear the query window, enter the following T-SQL command, and select Execute on the toolbar.

       ALTER ROLE db_owner ADD MEMBER [SSISIrGroup]
       

       The command should complete successfully, granting the contained user the ability to access SSISDB.

Enable Microsoft Entra authentication on Azure SQL Managed Instance

Azure SQL Managed Instance supports creating a database with the specified system/user-assigned managed identity for your ADF directly. You need not join the specified system/user-assigned managed identity for your ADF to a Microsoft Entra group nor create a contained user representing that group in Azure SQL Managed Instance.

Configure Microsoft Entra authentication for Azure SQL Managed Instance

Follow the steps in Provision a Microsoft Entra administrator for Azure SQL Managed Instance.

Add the specified system/user-assigned managed identity for your ADF or Azure Synapse as a user in Azure SQL Managed Instance

For this next step, you need SSMS.

1. Start SSMS.

2. Connect to Azure SQL Managed Instance using SQL Server account that is a sysadmin. This is a temporary limitation that will be removed once the support for Microsoft Entra server principals (logins) on Azure SQL Managed Instance becomes generally available. You will see the following error if you try to use a Microsoft Entra admin account to create the login: Msg 15247, Level 16, State 1, Line 1 User does not have permission to perform this action.

3. In the Object Explorer, expand the Databases -> System Databases folder.

4. Right-click on master database and select New query.

5. In the query window, execute the following T-SQL script to add the specified system/user-assigned managed identity for your ADF as a user.

   CREATE LOGIN [{your managed identity name}] FROM EXTERNAL PROVIDER
   ALTER SERVER ROLE [dbcreator] ADD MEMBER [{your managed identity name}]
   ALTER SERVER ROLE [securityadmin] ADD MEMBER [{your managed identity name}]
   

   If you use the system managed identity for your ADF, then your managed identity name should be your ADF name. If you use a user-assigned managed identity for your ADF, then your managed identity name should be the specified user-assigned managed identity name.

   The command should complete successfully, granting the system/user-assigned managed identity for your ADF the ability to create a database (SSISDB).

6. If your SSISDB was created using SQL authentication and you want to switch to use Microsoft Entra authentication for your Azure-SSIS IR to access it, first make sure that the above steps to grant permissions to the master database have finished successfully. Then, right-click on the SSISDB database and select New query.

   1. In the query window, enter the following T-SQL command, and select Execute on the toolbar.

      CREATE USER [{your managed identity name}] FOR LOGIN [{your managed identity name}] WITH DEFAULT_SCHEMA = dbo
      ALTER ROLE db_owner ADD MEMBER [{your managed identity name}]
      

      The command should complete successfully, granting the system/user-assigned managed identity for your ADF the ability to access SSISDB.

Provision Azure-SSIS IR in Azure portal/ADF app

When you provision your Azure-SSIS IR in Azure portal/ADF app, on the Deployment settings page, select the Create SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance to store your projects/packages/environments/execution logs check box and select either the Use Microsoft Entra authentication with the system managed identity for Data Factory or Use Microsoft Entra authentication with a user-assigned managed identity for Data Factory check box to choose Microsoft Entra authentication method for Azure-SSIS IR to access your database server that hosts SSISDB.

For more information, see Create an Azure-SSIS IR in ADF.

Provision Azure-SSIS IR with PowerShell

To provision your Azure-SSIS IR with PowerShell, do the following things:

1. Install Azure PowerShell module.

2. In your script, do not set CatalogAdminCredential parameter. For example:

   Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                         -DataFactoryName $DataFactoryName `
                                         -Name $AzureSSISName `
                                         -Description $AzureSSISDescription `
                                         -Type Managed `
                                         -Location $AzureSSISLocation `
                                         -NodeSize $AzureSSISNodeSize `
                                         -NodeCount $AzureSSISNodeNumber `
                                         -Edition $AzureSSISEdition `
                                         -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
                                         -CatalogServerEndpoint $SSISDBServerEndpoint `
                                         -CatalogPricingTier $SSISDBPricingTier
   
   Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                           -DataFactoryName $DataFactoryName `
                                           -Name $AzureSSISName
   

Run SSIS packages using Microsoft Entra authentication with the specified system/user-assigned managed identity for your ADF

When you run SSIS packages on Azure-SSIS IR, you can use Microsoft Entra authentication with the specified system/user-assigned managed identity for your ADF to connect to various Azure resources. Currently we support Microsoft Entra authentication with the specified system/user-assigned managed identity for your ADF on the following connection managers.

• OLE DB Connection Manager

• ADO.NET Connection Manager

• Azure Storage Connection Manager