question

GS-4786 avatar image
1 Vote"
GS-4786 asked KranthiPakala-MSFT edited

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`

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.)

azure-data-factoryazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SatishBoddu-MSFT avatar image
1 Vote"
SatishBoddu-MSFT answered

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

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.