Connectivity from ADF to Azure sql fails very often but not always

Question

Since 2 days we experience multiple failures in ADF connectivity to Azure SQL. We perform multiple connection at the same time. Some succeed, some fail. We use managed service identity and user has appropriate permissions. Worked for years without issues and suddenly multiple days of failures

full error (partly dutch) below

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XXX.database.windows.net', Database: 'XXX', User: ''. 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=Er is een verbinding met de server tot stand gebracht, maar vervolgens is er een fout opgetreden tijdens de handshake voorafgaand aan de aanmelding. (provider: TCP Provider, error: 0 - De opgegeven netwerknaam is niet langer beschikbaar.),Source=.Net SqlClient Data Provider,SqlErrorNumber=64,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=64,State=0,Message=Er is een verbinding met de server tot stand gebracht, maar vervolgens is er een fout opgetreden tijdens de handshake voorafgaand aan de aanmelding. (provider: TCP Provider, error: 0 - De opgegeven netwerknaam is niet langer beschikbaar.),},],''Type=System.ComponentModel.Win32Exception,Message=De opgegeven netwerknaam is niet langer beschikbaar,Source=,'

Answer 1

This error usually occurs when ADF cannot connect to the target server due to a transient network issue. Usually implementing a retry mechanism and increasing the connection timeout value in ADF linked service (at least 30 seconds is recommended) should help resolve the issue.

Change the connection string 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

Answer 2

Issue found and solved. There was a server migration being tested and the integration runtime had two instances on two identical servers on two different networks. Integration runtime showed no issues but did fail because it couldn't resolve the endpoint sometimes. a bit weird why it did not fail at all. But issue solved, thanks.

Answer 3

HI Alberto,

looking at the number of failures past 2 days compared to the previous 2 years is comparing very much with nothing. So it must be something is going on. I can't find any outages however.

I will look into the timeout setting for the linked service. Retry policy could help indeed