authenticationazure-sql-databaseazure-data-factorycredentialsazure-managed-identity

Unable to Link Azure SQL Database to Data Factory using managed identity

Cannot like azure SQL Database to ADF using user-assigned managed identity Here are the steps:

  1. Have created a managed Identity named "WeeklyEwsUsageReport_ManageIdentity"
  2. Have configured the SQL server with the following SQL query statement
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO
  1. Have gained the contributor role assignment from both SQL database and ADF
  2. Created a test credential named "credential1" enter image description here

But cannot connect to the SQL server, and here is the exception name and details: Error Code: InvalidCredentialReference Details: Invalid credential reference. Name: credential1

Would like to connect SQL database from ADF

Solution

    1. First you need to add your user assigned managed identity to ADF Managed identities:

      Go to the settings >> Managed identities >> select the User-assigned >> Add and add your user-assigned managed identity to ADF.

      enter image description here

    2. Create credential in ADF:

      Now create credential in ADF to use authentication type as User Assigned Managed Identity. While creating credential in ADF add user assigned managed identity which is assigned to ADF by adding it into ADF Managed identities (as we did in above process).

      enter image description here

    3. Create user for User Assigned Managed Identity:

      Now, create user in SQL and assign proper role for User Assigned Managed Identity in Azure SQL with below commands:

      CREATE  USER [UAMI] FROM  EXTERNAL  PROVIDER;
ALTER  ROLE db_datawriter ADD  MEMBER [UAMI];
ALTER  ROLE db_datareader ADD  MEMBER [UAMI];

      Now connect with this credential to your SQL using User Assigned Managed Identity