Azure Data Factory - Azure SQL connectivity error : Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. `SqlErrorNumber=53`

Question

While trying to establish connection from Azure Data factory to Azure SQL, getting below error message. Please assist...

SqlErrorNumber=53

Error Message: Connection failed: Cannot connect to SQL Database: 'vm-123-sql-dev', Database: 'DevServer', User: '123-sql-dev-admin'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct, and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server), SqlErrorNumber=53,Class=20,State=0, The network path was not found

Activity ID: ac7aba96-53d3-4740-9557-b6da7a6482s3.;

Answer 1

Hi,
Welcome to Microsoft Q&A forum and thanks for reaching out.

By looking at the error message and the SQL error number, below could be the possible root case and resolution for the issue.

SqlErrorNumber=53

• Possible root cause:
  • This error usually occurs when you are trying to access SQL data sources located in an Azure VM / VNet using Azure IR or
  • Firewall issues
• Possible resolution/workaround:
  To be able to have a successful connection, you will need to install the Self-Hosted Integration Runtime in the Azure VM and whitelist the Machine’s IP address in Azure SQL VM inbound NSG rules and open Port 443 & 1433 to allow communication from Azure Data Factory to the SQL VM. Then you should be able to connect to SQL VM from Data Factory by choosing the Self-Hosted Integration Runtime in SQL Server linked service to connect.
  Here is the MS doc that explains how to create a Self-Hosted Integration Runtime: https://learn.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime
  Additional information
• To explore more about ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server please refer to this doc: ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server
• Also please refer to this helpful MS doc : Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance
  Hope this info helps. Do let us know if you have further query.

----------

Thank you
Himanshu
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

Hey, there can be multiple reasons for that.some if them are as below:

1. check whether the allow azure service property is enabled in Azure SQL server.
   If not then you would have to white list the IP range of the region in which the ADF is created
2. in case if your source or sink is leveraging an integration runtime,then the azure database must also be accessible from the IR

Can you please provide the source and sine details of your adf