CODEWITHSUNDEEP
azureazure-sql-databaseazure-data-factoryazure-managed-identity
ekan
ekan

Reputation: 55

Link Azure SQL Database to Data Factory using managed identity

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.

Upvotes: 4

Views: 6981

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

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.

Upvotes: 9

Related Questions