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.
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:
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: