Error when connecting to Azure SQL Database from Azure Data Factory

Cheah, Felix FC 0 Reputation points
2024-01-31T02:09:50.49+00:00

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)

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,151 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Nandan Hegde 29,551 Reputation points MVP
    2024-01-31T04:35:18.1233333+00:00

    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


  2. ShaktiSingh-MSFT 12,541 Reputation points Microsoft Employee
    2024-02-01T06:22:15.3433333+00:00

    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

    0 comments No comments