Azure Data Factory – How to Get Database Password from Key Vault

Introduction: Azure Data Factory (ADF) supports Azure Key Vault linked service. This linked service connects your ADF pipeline to a Key Vault to fetch secrets. Overview of Data factory and Key Vault are covered in previous articles.

• Azure Key Vault using CLI
• Create Azure Data Factory Pipeline and perform copy activity

This approach can be used for most of the supported databases in Azure. To accomplish it, majorly we need one Database linked service (ex- MSSQL SERVER) and Key Vault linked service. But before that perform below steps

Steps to Perform

1. Create a resource group <ADF-KeyVault-Demo>
2. Spin a SQL server instance with a database <My-Database>
3. Create a Key Vault with a database password secret <My-Database-Password>
4. An Azure Data Factory instance

*Steps to create these resources are not covered in this article.

Implementation

Go to Resource Group > Azure Data Factory > Author & Monitor and wait for Azure data factory to open.

Create Key Vault Linked Service : Go Connection > Linked services > New > Azure Key Vault

1. Name it KeyVaultLinkedService
2. Select your Azure Subscription from subscription dropdown
3. Select your Key Vault service from Azure key vault name dropdown
4. Click Create.

Create SQL Service Linked Service : Go Connection > Linked services > New > Azure SQL Database and select appropriate resources as shown and click on Test Connection and it will fail which means Key Vault is not allowing to connect and fetch database password.

Note : To fix this, Open Key Vault from Resource group, Go to Access Policies > Add Access Policy and apply the settings as below and click on Add and then Save Access Policies

Go to SQL Service Linked Service and click on Test Connection but it will again fail due SQL server permission. Click on more, you will find that SQL server is not allowing to connection database from one particular IP (which would be different in your case).

Note : To fix this, directly Open SQL Server Database from Resource group, Go to Set server firewall > Add Client IP and configure a rule by providing IP and Save.

Go to SQL Service Linked Service and click on Test Connection and you will see connection has been established successfully.

So now linked service is ready to consume in more secure way to create datasets and fetch the data from tables. Hope, this gives an overview to use Key Vault and SQL Server or any databases in Azure Data Factory.

If you have any suggestions/feedback, please put them in the comment box.

Happy Learning 🙂