We have discussed various ways to setup authentication for Azure Data Factory Linked Service to ADLS Gen2, in previous post. One of the standard authentication methods is System-Managed Identity. While setting up proper roles on Azure Portal is enough for this scenario, lets have a look at another scenario where we are using Managed Identity authentication in the Linked Service to connect to a Synapse Analytics Dedicated SQL Pool. This requires proper database permissions to be setup in Synapse Analytics . Let’s have a look at the steps in detail, below.
Step 1: Select the Azure Subscription, Database name and “Managed Identity” as the Authentication Type in Data Factory Linked Service and copy the Managed Identity Name for the Data Factory Instance:
Step 2: Logon to Synapse Analytics dedicated SQL Pool with an account with DB admin privileges and run the following commands in the order shown below.
2.1 First we will create a DB user based on the Managed Identity Name for the Data Factory (that we copied in the previous step).
CREATE USER [<Managed Identity Name>] FROM EXTERNAL PROVIDER;
2.2 Grant CONTROL access at the database level to the User that we created in Step 2.1:
GRANT CONTROL ON DATABASE::[<Synapse SQL Pool>] TO [<Managed Identity Name>]
2.3 (Optional) Check access permissions for the User:
SELECT pri.name, pri.type_desc, perm.permission_name, perm.state_desc, perm.class_desc, object_name(perm.major_id) FROM sys.database_principals pri LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = pri.principal_id WHERE pri.name = '<Managed Identity Name>'
Note: The above code is useful while setting up Managed Identity authentication to Azure SQL and SQL Server (on-prem) databases , as well.
Step 3: Go back to Azure Data Factory Linked Service and check if the Managed Identity authentication is working:
Reference: Copy and transform data in Azure Synapse Analytics – Azure Data Factory & Azure Synapse | Microsoft Docs
One thought on “How to setup DB permissions for Azure Data Factory (Managed Identity)?”