Azure Data factory linked service – system managed service identity error

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

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

When we create the data factory, by default system managed identity will be created with the same name. we 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 the datafactory system managed identity.

Connect to 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.

We can use Azure portal or SSMSCREATE USER [datafactory_name] FROM EXTERNAL PROVIDER;
--For read/write access.
ALTER ROLE db_datareader ADD MEMBER [datafactory_name]
ALTER ROLE db_datawriter ADD MEMBER [datafactory_name]

Or
If your managed service needs owner permissions, execute db_owner access

ALTER ROLE db_datareader ADD MEMBER [datafactory_name]
(or)
EXEC sys.sp_addrolemember
@rolename = N'db_owner',
@membername = [datafactory_name]