Connect to Onprem SQL Database from Azure Data Factory

Question

Hi Team,

I am trying to create a linked Service in the Azure Data Factory for setting up a connection to SQL Database on - prem server using Windows Authentication in ADF Linked Service to copy data to Cloud and my Test Connection Failed with below Error -

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'servername.database.windows.net', Database: 'xxx', User: 'xxx'. 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

To add I am able to connect to the same Server and Database using Sql Server Management Studio.

Need help to resolve the issue.

Thanks,

Nalini.

Answer 1

As this is an on prem database, you would need to use Self hosted IR to access that database via ADF.

The above error is because you are using Azure IR and the On Prem database is hosted within a network

https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory