Azure Data factory

Question

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)',Source=,''Type=System.Data.SqlClient.SqlException,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.),Source=.Net SqlClient Data Provider,SqlErrorNumber=10054,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=10054,State=0,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.),},],''Type=System.ComponentModel.Win32Exception,Message=An existing connection was forcibly closed by the remote host,Source=,

Answer 1

Hi Amarnath Bakthavachalam,

Thanks for reaching out to Microsoft Q&A.

The error you're encountering in Azure Data Factory (ADF) typically indicates a network or connectivity issue between ADF and your SQL Server. Here are some steps you can try to troubleshoot and resolve the issue:

Check SQL Server Connectivity:

• Verify that the SQL Server is accessible from the ADF environment. Confirm that there are no firewall rules blocking traffic.
• Ensure the network connection to the SQL Server is stable, especially if the server is hosted on-premises or in a virtual network with a VPN.

Timeout and Retries:

  - Try increasing the connection and command timeouts in the ADF linked service. This can help manage any latency issues that might be causing the connection to be dropped.
  
     - Configure retries for the pipeline in case temporary network interruptions are causing the issue.
     

1. Resource Constraints:

• Check the SQL Server’s performance and resource usage (e.g., CPU, memory) to ensure it’s not under heavy load, as this can cause it to terminate connections.
  • For SQL Database, ensure there are enough resources allocated (ex: DTUs or vCores) for the workload.
  Retry Logic and Fault Tolerance:

  - Configure retry policies in ADF to handle intermittent network issues. This can be set in the pipeline activity properties.
  

1. TLS and Encryption Settings:

• If you're using SQL Server with SSL/TLS encryption, verify that the encryption settings in ADF’s linked service match those of the SQL Server.
  • Ensure that the server supports the encryption protocol (TLS 1.2) if it’s enabled.
  Review Azure Logs:

  - Check for related errors in Azure Monitor or Log Analytics if enabled. This can provide additional insights into connectivity or other underlying issues.
  

If the issue persists, you may need to monitor the network path between ADF and SQL Server to identify potential points of failure or further investigate resource constraints on SQL Server.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.