I am using ADF ,copying data from on premises Data to azure SQL.when it trying to copying data from SQL(On Premises) to Azure SQL it giving error

Soubhagya Patra 0

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'd-puma-eastus-sql.database.windows.net', Database: 'Puma', User: 'AzureAdmin'. 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=,'

Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-16T21:06:43.7633333+00:00
@Soubhagya Patra

In addition to @Nandan Hegde

It seems like the issue might be related to networking changes or connectivity disruptions between your self-hosted integration runtime (SHIR) server and Azure SQL Database. Since you have confirmed that:

The linked service connection is working.

Lookup activity accessing Azure SQL is successful.

The server hosting SHIR cannot ping the Azure SQL server, indicating a potential network issue.

Here are few considerations that might help you:

Verify Network Connectivity - Even if no explicit changes were made, external factors such as security updates, firewall rule changes, or proxy configurations might have affected connectivity. Run telnet <Azure SQL Server Name> 1433 from the SHIR server to check if the Azure SQL Server is reachable on port 1433 (the default SQL port). Alternatively, use the command Test-NetConnection -ComputerName <Azure SQL Server Name> -Port 1433 If this fails, it confirms a network issue.

Check for Blocked Outbound Traffic - Ensure that outbound traffic on port 1433 from the SHIR server is not being blocked by any firewall or network security group (NSG) rules.

Validate Azure SQL Server Firewall Rules - Confirm that the public IP address of your SHIR server is whitelisted in the Azure SQL Database firewall settings. To retrieve the public IP address of your SHIR server, visit https://whatismyipaddress.com/ from the SHIR server or any equivalent service.

Review Recent Network Changes - Investigate if any recent updates or changes were made to the SHIR server, such as, Windows updates or patches. Changes in corporate firewall rules or proxy configurations. If the SHIR server is behind a corporate firewall or proxy, ensure that it allows outbound connections to Azure SQL Database.

Recreate the Linked Service - If none of the above resolves the issue, try recreating the linked service in ADF to ensure there are no underlying misconfigurations.

Test Using a Different Integration Runtime - As a temporary workaround, set up an Azure-hosted integration runtime and test the connection to Azure SQL Database to rule out any issues with the SHIR server.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-17T17:28:19.42+00:00

@Soubhagya Patra

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-20T17:44:36.1433333+00:00

@Soubhagya Patra

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Soubhagya Patra 0 Reputation points

2025-01-22T15:16:48.8966667+00:00

Hi @chandan ,

as i have checked the AzIR conguration manager from SHIR server and found it is able to connect Please see the screen shot. when it copy the data still getting intermittent issue,

what do you think causing issue?
Soubhagya Patra 0 Reputation points

2025-01-22T15:17:57.1+00:00
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-23T10:21:39.64+00:00

@Soubhagya Patra

The error message you are encountering indicates that there may be issues with connectivity between your on-premises SQL Server and Azure SQL Database. Here are some potential causes and recommendations:

Firewall Configuration - Ensure that the Azure SQL Database firewall allows the integration runtime to access the database. You may need to enable the "Allowed Azure services and resources to access this server" option in the Azure SQL Server firewall configuration.

Public Network Access - If the error message contains "SqlErrorNumber=47073," it suggests that public network access is denied. You should set the "Deny public network access" option to No in the Azure SQL firewall settings.

Network Stability - Since you mentioned intermittent issues, it could be related to network stability. If there are transient network issues, consider implementing a retry policy in your data copy activity to handle these situations.

Connection Timeout - The error message also indicates a timeout. This could be due to the data source or destination being overloaded. Check the load on your Azure SQL Database and consider scaling up if necessary.

Port Configuration - Ensure that port 1433 is allowed in your firewall settings, as it is required for SQL Server connections.

Service Principal Permissions - If you are using a service principal for authentication, ensure it has the necessary permissions to access the Azure SQL Database.

For more details, please refer: Troubleshoot the Azure Synapse Analytics, Azure SQL Database, SQL Server, Azure SQL Managed Instance, and Amazon RDS for SQL Server connectors in Azure Data Factory and Azure Synapse

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-23T21:43:30.7766667+00:00

@Soubhagya Patra

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Chandra Boorla 7,260 Reputation points Microsoft Vendor

2025-01-24T21:07:52.27+00:00

@Soubhagya Patra

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Nandan Hegde 33,561 Reputation points MVP

2025-01-14T14:06:34.9666667+00:00

Can you please do the below steps and confirm for us to debug: 1) iis your azure sql database linked service connection successful? 2) use a look up activity accessing the Azure sql database and run and confirm whether it is a success or failure 3) in case if above 2 are success, then login within the server in which the IR is installed and try accessing the azure sql database from within the server and confirm back whether you are able to access. In case whenever we use a self hosted ir in either source or sink in copy activity, both the source and sink must be accessible within the server hosting the ir
Please sign in to rate this answer.
Soubhagya Patra 0 Reputation points

2025-01-14T17:19:19.3966667+00:00

I have checked both (1) and (2) and both are working fine but my Azure SQL server unbale to getting reply from IR server -ping AzureSQLDbname. it was working fine earlier but suddenly it is not working since 2 days. it is getting timeout.

what would be next steps please?

Nandan Hegde 33,561 Reputation points MVP

2025-01-15T05:08:12.3766667+00:00

You can refer the below thread :

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

Ideally to summarize, you have some blockage from your server to Azure SQL database so you might want to whitelist the IP of the server in firewall settings of database and open port wihin server as stated in above link

Soubhagya Patra 0 Reputation points

2025-01-15T09:35:22.6766667+00:00

My question there was no changes and it was suddenly stop working.

so, shall we whistlist the Azure SQL IP on SHIR server and open port?

Nandan Hegde 33,561 Reputation points MVP

2025-01-15T09:43:40.8033333+00:00

The other way, whitelist the IP of the server in the Azure SQL server firewall if it is not present.

Also try opening the Port in the server allowing access to Azure SQL database.

This seems to be more network config team work

Soubhagya Patra 0 Reputation points

2025-01-15T09:47:42.37+00:00

IP are already whistlist on Azure SQL server firewall setting.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

I am using ADF ,copying data from on premises Data to azure SQL.when it trying to copying data from SQL(On Premises) to Azure SQL it giving error

1 answer

Your answer