Search code examples
azureazure-active-directoryazure-sql-databaseazure-data-factoryazure-sql-managed-instance

Azure SQL database with MFA login to connect from Azure ADF

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.

enter image description here enter image description here 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.

Solution

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

    enter image description here

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

    enter image description here

    I set admin for azure SQL database:

    enter image description here

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

    CREATE USER [DATAFACTORY NAME] FROM eXTERNAL PROVIDER

    enter image description here

    Added rules to the user using below code:

    ALTER ROLE db_datareader ADD MEMBER [DATA FACTORY NAME];

    enter image description here

    I tested linked service again, tested successfully

    enter image description here

    It worked for me, once check from your end.