Edit

Enable Azure Active Directory authentication for Azure-SSIS integration runtime

  • Article
  • 7 minutes to read

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

This article shows you how to enable Azure Active Directory (Azure AD) 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.

Note

  • In this scenario, Azure AD 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.

  • If you have already created your Azure-SSIS IR using SQL authentication, you can not reconfigure it to use Azure AD authentication via PowerShell at this time, but you can do so via Azure portal/ADF app.

Note

We recommend that you use the Azure Az PowerShell module to interact with Azure. See Install Azure PowerShell to get started. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Enable Azure AD authentication on Azure SQL Database

Azure SQL Database supports creating a database with an Azure AD user. First, you need to create an Azure AD group with the specified system/user-assigned managed identity for your ADF as a member. Next, you need to set an Azure AD 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 Azure AD 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 an Azure AD group with the specified system/user-assigned managed identity for your ADF as a member

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

  1. Install the Azure AD PowerShell module.

  2. Sign in using Connect-AzureAD, 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 Azure AD authentication for Azure SQL Database

You can Configure and manage Azure AD 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 Azure AD authentication.

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

  4. In the command bar, select Set admin.

  5. Select an Azure AD 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 Azure AD 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 Azure AD authentication for Azure SQL Database).

  4. In the User name field, enter the name of Azure AD 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 Azure AD 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 Azure AD 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 an Azure AD group nor create a contained user representing that group in Azure SQL Managed Instance.

Configure Azure AD authentication for Azure SQL Managed Instance

Follow the steps in Provision an Azure AD 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 Azure AD server principals (logins) on Azure SQL Managed Instance becomes generally available. You will see the following error if you try to use an Azure AD 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 Azure AD 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 AAD authentication with the system managed identity for Data Factory or Use AAD authentication with a user-assigned managed identity for Data Factory check box to choose Azure AD 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 Azure AD authentication with the specified system/user-assigned managed identity for your ADF

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