error while connecting to on prem sql server in ADF linked Service

Question

I am getting this error below while trying to connect on prem SQL server using adf linked service.

Cannot connect to SQL Database: '10.152.5.78', Database: 'ASAPFrame', User: 'asapframeuser'. 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: 59b76c29-c913-4f51-96bb-b49acd4b28fb.

Answer 1

Hi @AzeemK ,

Thank you for posting Query in Microsoft Q&A Platform.

SQLErrorNumber = 53 could occur because of below possible root cause

• This error usually occurs when you are trying to access SQL data sources located in an Azure VM / VNet/Private Network using Azure IR or
• Firewall issues

To be able to have a successful connection, you will need to install the Self-Hosted Integration Runtime and whitelist IP address in SQL server firewall rules.

Please check below link where similar issues(SQLErrorNumber = 53) discussed.
https://learn.microsoft.com/en-us/answers/questions/336067/azure-data-factory-azure-sql-connectivity-error-ch.html

Useful resources:

• create a Self-Hosted Integration Runtime: https://learn.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime
• Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance
• ADF connectivity issues with Azure Synapse Analytics, Azure SQL Database, and SQL Server

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators