Link Azure SQL Database to Data Factory using managed identity

Question

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

Created a Linked Service and selected Managed Identity as the Authentication Type
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.

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
– Raunak Jhawar, Commented Aug 25, 2020 at 18:18
I update my answer,you can have a try.If you have any doubt,please feel free to ask. — Steve Johnson
– Steve Johnson, Commented Aug 26, 2020 at 5:23

Steve Johnson · Accepted Answer · 2020-08-26 05:22:08Z

9

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

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

2.select you and save as admin

3.click the button and run two sql in sql database.

More details,you can refer to this documentation.

edited Aug 26, 2020 at 5:22

answered Aug 26, 2020 at 2:06

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ekan Over a year ago

Thanks Steve for the detailed answer.

Collectives™ on Stack Overflow

Link Azure SQL Database to Data Factory using managed identity

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related