Error when connecting to Azure SQL Database from Azure Data Factory

Question

I am trying connecting data factory to SQLMI using Managed identity. I was able to create the linked service and the test connection was successful. I tried a copy pipeline to query the SQL database and copy it to a storage account and that worked. I created a dataset and could preview the data from there. However, when I tried using that dataset in a data flow (also tried inline data) the connection fails and give this error: Cannot connect to SQL database: 'jdbc:sqlserver://(server name), 'Managed Identity (factory name): (name)'.[SQL Exception]Error Code:18456, Error Message: Login failed for user '<token-identified principal>'. ClientConnectionId:3dda9792-95c4-4561-aab2-66b28bf0b2ff, error stack:shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

Answer 1

Hey, My guess is you are using Self hosted IR to access your SQL MI database via ADF . Can you please confirm on that ? If that is the case, ADF dataflow doesnt support Self IR sources thereby leading to a failure in connection

Answer 2

Hi
Cheah, Felix FC •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting error when trying to connect to Azure SQL Managed Instance from ADF with Managed Identity.

Could you please let us know if you have followed the steps mentioned in the standard documentation without any issue:

https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-managed-instance?tabs=data-factory#managed-identity

and successfully provisioned a Microsoft Entra administrator(https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell#provision-azure-ad-admin-sql-managed-instance ) for your Managed Instance?

Let us know the result and also share the configuration details of the Azure SQL MI.

Thanks