ADF linked service - system managed service identity error

Question

Hello,
I am getting the below error while trying to create Azure SQL linked service using system assigned managed identity.

Cannot connect to SQL Database:’’, Database: '', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user '<token-identified principal>'., SqlErrorNumber=18456,Class=14,State=1, Activity ID:

I am looking at the below thread, but it didn't help. Please suggest if you have a solution for this.

https://learn.microsoft.com/en-us/answers/questions/517559/adf-fails-to-connect-to-azure-sql-database-using-m.html

Thanks

Answer 1

Hello @Narendra ,

Thanks for the question and using MS Q&A platform.

As per the error message, it looks like your Data factory managed identity doesn’t have access to the azure SQL server.

When you create the data factory, by default system managed identity will be created with the same name. You will need to provide access to this managed identity.

Go to Data factory and on the search, under settings, click on managed identities. This is your datafactory system managed identity.

Connect to your DB and run the following commands.

Please note: To execute the below commands, you will need to login using AAD authentication and AAD admin access is required.

You can use Azure portal or SSMS

CREATE USER [datafactory_name] FROM EXTERNAL PROVIDER;  

for db_datareader and db_datawriter
   
EXEC sys.sp_addrolemember   
    @rolename = N'db_datareader',  
    @membername = [datafactory_name]  

EXEC sys.sp_addrolemember   
    @rolename = N'db_datawriter',  
    @membername = [datafactory_name]  

If your managed service needs owner permissions 
EXEC sys.sp_addrolemember   
    @rolename = N'db_owner',  
    @membername = [datafactory_name]  

Please note:  ALTER ROLE ADD MEMBER is not supported in synapse.

Please let us know if you have any further questions.