How to add Azure SQL Database as linked service to Azure Data Factory

shellkyle 0 Reputation points
2024-05-07T04:05:57.0066667+00:00

I keep running into errors adding Azure SQL as a linked service to Data Factory. I'm new to Azure so please bear with me. Following all the online guides, I've done all of the following:

  • On the SQL Server side:
    • Under Microsoft Entra ID, enabled Microsoft Entra admin
    • Under Networking, allowed "Azure services and resources to access this server"
    • Under IAM, added Data Factory with an SQL DB Contributor role
  • On the Data Factory side:
    • Under Settings > Managed identities, clicked "On" for the system assigned managed identity

Despite it all, I keep getting this error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: <>, Database: <>, User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user '<token-identified principal>'., SqlErrorNumber=18456,Class=14,State=1,

Help would be much appreciated.

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

1 answer

Sort by: Most helpful
  1. Harishga 4,160 Reputation points Microsoft Vendor
    2024-05-07T07:40:50.7833333+00:00

    Hi @shellkyle

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    Based on the error message you provided, it seems like there is an authentication failure when trying to connect to the Azure SQL Database. This can be due to various reasons such as incorrect credentials or the user not having the correct permissions.

    To troubleshoot this issue, you can take the following steps:

    • Make sure that the SQL Database firewall is configured to allow the integration runtime to access it. This may involve adding the IP address of the integration runtime to the firewall rules.
    • Verify the linked service configuration to ensure that the server name, database name, authentication method, and credentials are correct.
    • Use a SQL client tool like Azure Data Studio or SQL Server Management Studio to manually connect to the SQL Database using the same credentials as specified in the linked service to test connectivity.
    • Confirm that the Data Factory’s managed identity has been granted the necessary permissions on the Azure SQL server. It should have at least the db_datareader and db_datawriter roles, or higher, depending on the operations you need to perform.
    • If you’re using Azure Active Directory for authentication, ensure that you can generate an access token for the account and that the token is valid.

    Reference:
    https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory#troubleshooting

    https://learn.microsoft.com/en-us/answers/questions/950724/adf-linked-service-system-managed-service-identity

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.