Connect Azure SQL from Data Factory using Managed Identity

Azure Data Factory is a Microsoft cloud ETL service for serverless data integrations and orchestrations. It supports a lot of source and target connectors, including the Azure SQL database. In this article, I will describe how to connect to Azure SQL from Data Factory using managed Identity authentication.

What is Managed Identity?

Managed identity is a service principal associated with resources in Azure. When we create a data factory in Azure, it automatically creates an app in Azure Active Directory. Suppose we want to connect Azure SQL using Azure Data Factory. In that case, we need to grant Azure Data Factory app access to the Azure SQL database like any other Azure Active Directory user. This makes access management more secure and easy.

System Assigned Managed identity of Azure Data Factory will be automatically created when we create it through Azure portal or PowerShell. In case you do not find managed identity of Azure Data Factory, you can create it using below PowerShell command. You would need Az PowerShell module installed on the local machine.

#Generate managed identity for Data Factory.
Set-AzDataFactoryV2 -ResourceGroupName $resourceGroupName -Name $factoryName -Location $location

Pre-requisites:
To implement linked service to Azure SQL database from Azure data factory, you would need:
1. Azure subscription and a resource group with Azure Data Factory and Azure SQL database.
2. Admin permission to grant access to Azure SQL database.

Permission to grant Azure Data Factory managed identity access to Azure SQL database

Azure Active Directory users with admin permission on Azure SQL database can only grant access to the managed identity of Azure Data Factory. In case you do not have an Active Directory Admin account set for Azure SQL database, you can do this with the below steps.

  1. Navigate to Azure SQL database resource in the Azure portal and Click on Azure Active Directory under the Settings section.
  2. Click on the Set admin button.
  3. Search for the Azure active directory user or security group to which you want to grant admin access.
  4. Click Select, and this will select the admin account.
  5. Click Save to enable the admin.

Grant Azure Data Factory managed identity access to Azure SQL database

Now that we have an Azure Active Directory admin account set for Azure SQL database, we can grant access to Azure Data Factory Managed Identity to Azure SQL database. You can do this using any Azure SQL database client tools like SQL Server Management Studio 18 or Directly from Azure SQL database portal. Note that if you want to grant permission using client tools like SSMS 18, you must log in to Azure SQL database using Active Active Directory – Universal with MFA and use an Azure SQL admin account.

Create a user in Azure SQL database for Azure Data Factory and grant roles to this newly created user based on your needs.

--Add Azure Data Factory managed identity as an account to Azure SQL database.
CREATE USER [name_of_the_adf] FROM EXTERNAL PROVIDER;
--Grant appropriate role to Azure Data Factory based on your needs. 
ALTER ROLE [db_owner] ADD MEMBER [name_of_the_adf];
Grant Azure Data Factory managed identity access to Azure SQL database

Create a Linked service to Azure SQL Database in Azure Data Factory

We are all set to connect Azure SQL from Data Factory using managed identity. Follow the below steps to create a linked service in Azure Data Factory for Azure SQL using managed identity authentication.

  1. Navigate to Azure Data Factory and Click on New Linked Service.
  2. Search for Azure SQL Database.
  3. Click Continue.
Create a Linked service to Azure SQL Database in Azure Data Factory
  1. On the new linked service page, specify Azure subscription, Azure SQL Server name, and Database name.
  2. In the Authentication type, select System Assigned Managed Identity.
  3. Test the connection and click on Create.
Connect Azure SQL from Data Factory using Managed Identity

You can now use this linked service in ADF activities to perform various operations on the Azure SQL database.

Pro tips:
1. If you use version control (Azure DevOps or git integration) for Azure Data Factory, you need to use a Key vault-based connection string instead of manually specifying Azure SQL database details in the linked service.

See more

Kunal Rathi

Been working in the data engineering and analytics space for over a decade. Helping customers transform their data into insights, Cloud & DevOps enthusiast.