Link Azure SQL Database to Data Factory using managed identity

2
votes

I have been trying to use Managed Identity to connect to Azure SQL Database from Azure Data factory.
Steps are as follow:

  1. Created a Linked Service and selected Managed Identity as the Authentication Type
  2. On SQL Server, added Managed Identity created for Azure Data Factory as Active Directory Admin

The above steps let me do all data operations on the database. Actually that is the problem. I want to restrict the privileges given to Azure Data Factory on my SQL database.

First, let me know whether I have followed the correct steps to set up the managed identity. Then, how to limit privileges because I don't want data factory to do any DDL on SQL database.

1
azureazure-sql-databaseazure-data-factoryazure-managed-identity
Restrict the permissions to the user (ADF MI in this case) to db_datareader on the target database to limit the privileges. You can do this via ALTER ROLE command. – Raunak Jhawar
I update my answer,you can have a try.If you have any doubt,please feel free to ask. – Steve Zhao

1 Answers

4
votes

As Raunak comments,you should change the role to db_datareader.

In you sql database,run this sql:

CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER;

and this sql:

ALTER ROLE db_datareader ADD MEMBER [your Data Factory name];

You can find '[your Data Factory name]' here enter image description here

Then you do any DDL operation in Data Factory,you will the error like this:

"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=The INSERT permission was denied on the object

Update:

1.Search for and select SQL server in azure portal enter image description here

2.select you and save as admin enter image description here

3.click the button and run two sql in sql database. enter image description here

More details,you can refer to this documentation.