How to setup DB permissions for Azure Data Factory (Managed Identity)?

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