Share via

Test Conenction to SQL Database fails from Azure Data Factory

Ravichandran, Gopi Krishna 161 Reputation points
2023-04-26T08:50:13.21+00:00

Hi, We have two environments (Test and Prod) where we have hosted Azure Data factory services and trying to do test connections with SQL database residing in different subscriptions. The test connection fails through ADF-linked services in the Prod environment alone. It works well in the Test environment and both configurations are similar authentication to the same SQL database via the 'System Assigned managed identity' of ADF. It will be good to show some light in diagnosing and resolving this issue. User's image

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 85,586 Reputation points Microsoft Employee
    2023-04-28T08:04:26.5433333+00:00

    @Ravichandran, Gopi Krishna - Thanks for the question and using MS Q&A platform.

    Based on the error message you provided, it seems like there could be an issue with the firewall settings on your SQL database preventing the Azure Data Factory integration runtime from accessing it.

    Here are some troubleshooting steps you can take to diagnose and resolve the issue:

    1. Check the firewall settings on your SQL database: Make sure that the IP address of the Azure Data Factory integration runtime is added to the firewall rules on your SQL database. You can find the IP address of the integration runtimehere: Azure Integration Runtime IP addresses: Specific regions

    Check the linked service configuration: Double-check that the linked service configuration for your SQL database is correct. Make sure that the connection string, username, and password are all correct and match what is configured on your SQL database.

    Check the authentication method: Ensure that the authentication method specified in the linked service is correct. If you're using a managed identity, make sure that it has the appropriate permissions to access the SQL database.

    Check the permissions on the SQL database: Verify that the managed identity has the necessary permissions to access the SQL database. Specifically, it should have at least 'db_datareader' and 'db_datawriter' roles assigned.

    1. Check the integration runtime: Ensure that the integration runtime is running and is able to connect to the internet.

    If none of the above steps resolve the issue, try recreating the linked service from scratch and testing the connection again.

    I hope this helps you resolve the issue with connecting to your SQL database from Azure Data Factory.

    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.