azureazure-data-factory

Trouble assigning an Azure SQL server DB to Azure Data Factory

Using Azure, I first created a Managed Identity. I then created an Azure SQL Server DB and set the identity within security to my Managed Identity. I also created it to be public. I then attached to my empty DB from Azure Data Studio and added some tables.

I now go to Azure Data Factory and try to add a new linked service. I assign the server name and the DB name. I set the credentials to my Managed Identity. When I try and test the connection, it fails. When I open the error detail it just says InvalidCredentialReference.

If I go down to Security/credentials in ADF my managed identity is in there. Cant see at all what is wrong with it.

I select new Linked Service - Azure SQL Database.

I leave the name as AzureSqlDataBase1

Connected via integration runtime set as AutoResolveIntegrationRuntime

The Account is from Azure Subscription. I select my subscription then the Server name and Database name.

Authentication type I set to User-assigned managed Identity.

Credentials I select my Managed identity credential. Then I press 'Test Connection'

It then errors stating that the credential that I selected is invalid.

Solution

  • enter image description here

    If the User-Assigned Managed Identity(UAMI) which you provided in the credential doesn't have the access to the database, then you will get above error. To resolve the error, follow below procedure:

    First, add your UAMI to ADF managed identity by Go to the settings of ADF, Managed Identity --> Select User assigned --> Add your UAMI to ADF as shown below:

    enter image description here

    After that provide access to the UAMI to the required database by creating user of UAMI ang giving required permissions as follows:

    Login to SQL database as administrator run below query to create user and add permission:

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

    Now you will be able to connect to the SQL database with credential successfully as shown below:

    enter image description here