Azure Data Factory - Azure SQL connectivity error : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) `SqlErrorNumber=64`

Question

While trying to establish connection from Azure Data factory to Azure SQL, getting below error message. Please assist...

SqlErrorNumber=64

Error Message: Operation on target Execute_MASTER_Data_Load failed: Execution fail against sql server. Sql error number: 64. Error Message: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Answer 1

Hi,

Welcome to Microsoft Q&A forum and thanks for reaching out.

By looking at the error message and the SQL error number, below could be the possible root case and resolution for the issue.

SqlErrorNumber=64

• Possible root cause: This error usually occurs when the application (in this case ADF) cannot connect to the target server due to a transient network issue.
• Possible resolution/workaround: To resolve these issues, try the steps (in the order presented) in the Steps to fix common connection issues section of this document: https://learn.microsoft.com/azure/azure-sql/database/troubleshoot-common-errors-issues#steps-to-fix-common-connection-issues

If it is a transient target server network issue, implementing retry mechanism and increasing the connection timeout value in ADF linked service should help resolve the issue. Also below are additional steps for fixing common connection issues for SQL DB:

1. Make sure that TCP/IP is enabled as a client protocol on the application server. For more information, see Configure client protocols (On application servers where you don't have SQL tools installed, verify that TCP/IP is enabled by running cliconfg.exe (SQL Server Client Network utility).
2. Check the application's connection string to make sure it's configured correctly. For example, make sure that the connection string specifies the correct port (1433) and fully qualified server name. See Get connection information.
3. Try increasing the connection timeout value (in ADF SQL linked service). We recommend using a connection timeout of at least 30 seconds.
4. Test the connectivity between the application server and the Azure SQL Database by using SQL Server management Studio (SSMS), a UDL file, ping, or telnet. For more information, see Troubleshooting connectivity issues and Diagnostics for connectivity issues. Note: As a troubleshooting step, you can also test connectivity on a different client computer.
5. As a best practice, make sure that the retry logic is in place. For more information about retry logic, see Troubleshoot transient faults and connection errors to SQL Database.

Hope this information helps. Do let us know if you have further query.

---

Thank you Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.