Question 103808 in Azure Question posted in
The official documentation to get you started can be found here.

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search