Azure Data Factory / Azure SQL SqlErrorNumber=64.

Applies to: Azure SQL Database (PaaS), Azure Data Factory

Date created: October 23, 2022.

Updated: October 5, 2023.
 

Problem Description.
 

You have a Copy Data Task on an Azure Data Factory pipeline where the source is a Microsoft SQL Server instance  (on premise SQL) and the sink is an Azure SQL database (PaaS) that keeps generating the following error:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: 'xxx', Database: 'xxx', User: 'xxx'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.),Source=.Net SqlClient Data Provider,SqlErrorNumber=64,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=64,State=0,Message=A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.),},],''Type=System.ComponentModel.Win32Exception,Message=The specified network name is no longer available,Source=,'

You may also get similar error like the one below when you try to connect to an Azure SQL database from Azure Data Factory:

Error Message: Operation on target 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.

You have tested connectivity with SQL Server Management Studio and you get same error. The IP address of the Self-hosted Integration Runtime is whitelisted in Azure SQL Server and you have allowed Azure services and resources have access to the server.

Cause.

This error usually occurs when Azure Data Factory (or an application) cannot connect to the target server due to a transient network issue or due to blocked ports by your corporate or Windows firewall.

Solution.

Please make sure you have opened ports 1433 and 443 in your corporate firewall or Windows firewall. Make sure you have opened all the ports listed on Microsoft Documentation as well.

If you have verified ports have been opened, please make sure you have examined how to resolve most common connectivity issues on Microsoft Documentation.

If it is a transient target server network issue, implementing retry mechanism and increasing the connection timeout value in Azure Data Factory linked service to at least 30 seconds. Please examine Microsoft Documentation about how to deal with transient error.

Change the connection string on the Azure SQL Database linked service to something like:

Server=tcp:myserver.database.windows.net,1433;Database=mydatabase;User ID=mylogin@myserver;Password=mypassword;Encrypt=true;TrustServerCertificate=false;Connection Timeout=30

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.