0

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.

Improve this question
2
  • Did you add SQL database firewall rule with the client IP address. Refer this Microsoft document Commented Jan 21, 2023 at 14:13
  • I added this already but no luck Commented Jan 21, 2023 at 20:11

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a ton for the detailed process...it worked for me once I followed these steps!
Worked for me. Does not seem to be mentioned in any Azure documentation.
1

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
Improve this answer

2 Comments

Created the user as well with the name of the azure data factory and granted permissions even. Still the same issue.
On Managed Instance, you must create a login before creating the user. learn.microsoft.com/en-us/sql/t-sql/statements/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.