Encrypt credentials for on-premises data stores in Azure Data Factory

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

You can encrypt and store credentials for any of your on-premises data stores (linked services with sensitive information) on a machine with self-hosted integration runtime.

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.

You pass a JSON definition file with credentials to the
New-AzDataFactoryV2LinkedServiceEncryptedCredential cmdlet to produce an output JSON definition file with the encrypted credentials. Then, use the updated JSON definition to create the linked services.

Create a linked service with encrypted credentials

This example shows how to create a linked service to an on-premises SQL Server data source with encrypted credentials.

Create initial linked service JSON file description

Create a JSON file named SqlServerLinkedService.json with the following content:

Replace <servername>, <databasename>, <username>, and <password> with values for your SQL Server before saving the file. And, replace <integration runtime name> with the name of your integration runtime.

{
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Server=<servername>;Database=<databasename>;User ID=<username>;Password=<password>;Timeout=60"
        },
        "connectVia": {
            "type": "integrationRuntimeReference",
            "referenceName": "<integration runtime name>"
        },
        "name": "SqlServerLinkedService"
    }
}

Encrypt credentials

To encrypt the sensitive data from the JSON payload on an on-premises self-hosted integration runtime, run New-AzDataFactoryV2LinkedServiceEncryptedCredential, and pass on the JSON payload. This cmdlet ensures the credentials are encrypted using DPAPI and stored on the self-hosted integration runtime node locally. The output payload containing the encrypted reference to the credential can be redirected to another JSON file (in this case 'encryptedLinkedService.json').

Please ensure the following prerequisites are met:

  • Remote access option is enabled on the self-hosted integration runtime.
  • Powershell 7.0 or higher is used to execute the cmdlet.
New-AzDataFactoryV2LinkedServiceEncryptedCredential -DataFactoryName $dataFactoryName -ResourceGroupName $ResourceGroupName -IntegrationRuntimeName 'test-selfhost-ir' -DefinitionFile ".\SQLServerLinkedService.json" > encryptedSQLServerLinkedService.json

Use the JSON with encrypted credentials

Now, use the output JSON file from the previous command containing the encrypted credential to set up the SqlServerLinkedService.

Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $ResourceGroupName -Name "EncryptedSqlServerLinkedService" -DefinitionFile ".\encryptedSqlServerLinkedService.json"

Related content

For information about security considerations for data movement, see Data movement security considerations.