Share via

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

Mfundo Potolwana 0 Reputation points
2024-06-24T13:30:39.4533333+00:00

Hi

I am trying to import data from onprem SQL server database to Azure SQL databases through Azure Data Factor(ADF) Copy activity , this worked for more than a year with no problems ,till few weeks back when I got the error below. I got a workaround it by copying the data to blob first before uploading it to Azure database, but this is a risk when working with big data. I can access both my source and Sink database individually through ADF ,but the copy activity give me below error:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support.

Server: 'servername.database.windows.net', Database: 'Database_name', User: 'useradmin'.

Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,

Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=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: TCP Provider, error: 0 - The wait operation timed out.),Source=Framework Microsoft SqlClient Data Provider,''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'

Azure SQL Database
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,606 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,106 questions
Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
7,338 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 31,111 Reputation points MVP
    2024-06-30T06:11:43.7033333+00:00

    Please follow the below process to debug :

    1. Create 2 lookup activities one with the On Prem Database as a source and another with Azure SQL Database as a source. Also test the individual lined service connections individually?

    If the above is a success, and the failure is only with Copy activity between OnPrem and Azure SQL DB; it means the server on which the SHIR is installed might have the Azure SQL database access blocked out ( either Port 1433 might be disabled or network restricted)

    Similar thread :

    https://learn.microsoft.com/en-us/answers/questions/336067/azure-data-factory-azure-sql-connectivity-error-ch

    0 comments
  2. Pinaki Ghatak 3,110 Reputation points Microsoft Employee
    2024-07-01T08:49:18.93+00:00

    Hello @Mfundo Potolwana

    The error message you received indicates that there is an issue with the connection to your SQL Database.

    One possible cause of this issue is that there are incorrect firewall settings on your Azure SQL Database server, which is preventing the data flow runtime from connecting to it.

    To resolve this issue, you can check the firewall settings of your Azure SQL Database and set it to "Allow access to Azure services" rather than setting a fixed IP range.

    Another possible cause of this issue is that there is an issue with the linked service configuration. You can check the linked service configuration to ensure that it is correct and that the SQL Database firewall allows the integration runtime to access it.

    If you are still having trouble after checking these settings, you can try using copy activities to unblock this issue. This should help you.

    I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

    0 comments