Skip to content

Latest commit

 

History

History
170 lines (110 loc) · 8.64 KB

enable-aad-authentication-azure-ssis-ir.md

File metadata and controls

170 lines (110 loc) · 8.64 KB
title description services documentationcenter author manager ms.service ms.workload ms.tgt_pltfrm ms.devlang ms.topic ms.date ms.author
Enable Azure Active Directory Authentication for the Azure-SSIS integration runtime | Microsoft Docs
This article describes how to configure the Azure-SSIS integration runtime to enable connections that use Azure Active Directory authentication.
data-factory
douglaslMS
craigg
data-factory
data-services
powershell
conceptual
06/21/2018
douglasl

Enable Azure Active Directory authentication for the Azure-SSIS integration runtime

This article shows you how to create an Azure-SSIS IR with Azure Data Factory service identity. You can use Azure Active Directory (Azure AD) authentication with the managed identity for your Azure Data Factory instead of SQL authentication to create an Azure-SSIS integration runtime.

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

Note

If you have already created an Azure-SSIS integration runtime with SQL authentication, you can't reconfigure the IR to use Azure AD authentication with PowerShell at this time.

Create a group in Azure AD and make the managed identity for your ADF a member of the group

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, and run the following command to create the group, and save it in a variable:

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

    The output looks like the following example, which also examines the value of the variable:

    $Group

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

  3. Add the managed identity for your ADF to the group. You can follow Azure Data Factory service identity to get the principal SERVICE IDENTITY ID (for example, 765ad4ab-XXXX-XXXX-XXXX-51ed985819dc, but do not use SERVICE IDENTITY APPLICATION ID for this purpose).

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

    You also can examine the group membership afterward.

    Get-AzureAdGroupMember -ObjectId $Group.ObjectId

Enable Azure AD on Azure SQL Database

Azure SQL Database supports creating a database with an Azure AD user. As a result, you can set an Azure AD user as the Active Directory admin, and then log in to SSMS using the Azure AD user. Then you can create a contained user for the Azure AD group to enable your IR to create the SQL Server Integration Services (SSIS) catalog on the server.

Enable Azure AD authentication for the Azure SQL Database

You can configure Azure AD authentication for the SQL Database using the following steps:

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

  2. Select the SQL Database to be enabled for 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 an administrator of the server, and then select Select.

  6. In the command bar, select Save.

Create a contained user in the database that represents the Azure AD group

For this next step, you need Microsoft SQL Server Management Studio (SSMS).

  1. Start SQL Server Management Studio.

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

  3. In the Authentication field, select Active Directory - Universal with MFA support. (You can also use other two Active Directory authentication types. See Configure and manage Azure Active Directory authentication with SQL Database, Managed Instance.)

  4. In the User name field, enter the name of the Azure AD account that you set as the server administrator - for example, [email protected].

  5. select Connect. Complete the sign-in process.

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

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

  8. In the query window, enter the following line, and select Execute in the toolbar:

    CREATE USER [SSISIrGroup] FROM EXTERNAL PROVIDER

    The command should complete successfully, creating the contained user for the group.

  9. Clear the query window, enter the following line, and Select Execute in the toolbar:

    ALTER ROLE dbmanager ADD MEMBER [SSISIrGroup]

    The command should complete successfully, granting the contained user the ability to create database.

Enable Azure AD on Azure SQL Database Managed Instance

Azure SQL Database Managed Instance doesn't support creating a database with any Azure AD user other than AD admin. As a result, you have to set the Azure AD Group as the Active Directory admin. You don't need to create the contained user.

You can configure Azure AD authentication for the SQL Database Managed Instance server using the following steps:

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

  2. Select the SQL server to be enabled for Azure AD authentication.

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

  4. In the command bar, select Set admin.

  5. Search and select the Azure AD Group (for example, SSISIrGroup), and select Select.

  6. In the command bar, select Save.

Provision the Azure-SSIS IR in the portal

When you provision your Azure-SSIS IR with the Azure portal, on the SQL Settings page, check the "Use AAD authentication with the managed identity for your ADF" option. (The following screenshot shows the settings for IR with Azure SQL Database. For the IR with Managed Instance, the "Catalog Database Service Tier" property is not available; other settings are the same.)

For more info about how to create an Azure-SSIS integration runtime, see Create an Azure-SSIS integration runtime in Azure Data Factory.

Settings for the Azure-SSIS integration runtime

Provision the Azure-SSIS IR with PowerShell

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

  1. Install the Azure PowerShell module.

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

    Set-AzureRmDataFactoryV2IntegrationRuntime -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-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                             -DataFactoryName $DataFactoryName `
                                             -Name $AzureSSISName