I have an Azure SQL server and database which have MFA login and I am the admin. But when I try to establish a connection via a new linked service from ADF to this database using System Managed Identity option, it throws error - "Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'Server details', Database: 'database name', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

I have already given contributor role access to ADF in SQL database using system managed Identity. Also, I have tried to access this database using Autoresolve runtime and azure runtime. But still the error is coming.

CodePudding user response：

It sounds like you are missing the user creation and role assignment within the SQL database:

Connect to the database with your account and create an account for the data factory:

CREATE USER [<datafactory-name>] FROM EXTERNAL PROVIDER;

Then grant it the required role for your task:

ALTER ROLE [<roleName>] ADD MEMBER [<datafactory-name>]

Some available role names are:

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin
• public

CodePudding user response：

I created Azure SQL database in portal and created linked service in azure data factory with managed identity authentication I got below error:

I followed below procedure to resolve this: I turned on the managed identity of data factory

I set admin for azure SQL database:

Login with Admin to sql database Create User username as data factory name using below code:

CREATE USER [DATAFACTORY NAME] FROM eXTERNAL PROVIDER

Added rules to the user using below code:

ALTER ROLE db_datareader ADD MEMBER [DATA FACTORY NAME];

I tested linked service again, tested successfully

It worked for me, once check from your end.