Unable to connect on-prem SQL database to AzureSQL database in Azure Data Factory

Question

Unable to connect on-prem SQL database to AzureSQL database in Azure Data Factory

Steve Herz 36

I have a copy data task in my AzureDataFactory pipeline (source = on premise SQL; sink = AzureSQL) 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=,'
Source
Pipeline
pipeline1

The IP address of the Self-hosted Integration Runtime is whitelisted in my AzureSQLServer and I've allowed Azure services and resources access to the server.

My self hosted integration runtime indicates it is running successfully. My linked service referencing the SHIR connects successfully. The datasets referencing the linked service all connect successfully.

When I originally created the project I was using a different SHIR to my local machine but we are trying to deploy this now and connect to a different SHIR. All works successfully when connected to my local machine but the above message is encountered when connecting to the other SHIR.

Accepted answer

1 additional answer

Your answer

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Please make sure you have opened all the ports listed here.

Steve Herz 36 Reputation points

2022-08-03T18:01:20.357+00:00

Thanks, checking with IT, port 443 seems to be the key here. I'll let you know what I hear from them...
Steve Herz 36 Reputation points

2022-08-03T20:49:01.847+00:00

IT says port 443 outbound is open but still no luck. Having them try to connect via SSMS to the AzureSQL database to see what that will tell us.
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-08-03T21:57:01.887+00:00

Try to ping the Azure logical server from Command Prompt using the name of the server (ping yourservername.database.windows.net). The ping will timeout but it also should return the IP Address of the logical server. If it did not return the IP address then DNS resolution is not working.

If you got the IP address of the server, try to perform a trace route (command tracert of the IP address). Verify it reached Microsoft Azure platform.
Steve Herz 36 Reputation points

2022-08-04T15:17:21.467+00:00

Thanks for the help, we got it sorted out by squaring away a couple of firewall rules. Attempting to connect via SSMS resulted in the same error message. To resolve this we added a rule allowing traffic on 1433 through for the appropriate domain. Then we tried using the SHIR diagnostic and testing capabilities and got the same message so opened up communication for that. I think it was also port 1433 though I'm not 100% certain, he was moving pretty quickly. With this in place we were all set.

Thanks again!!!!
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2022-08-04T16:18:07.037+00:00

Thank you for sharing how you troubleshoot the issue as it may help other Microsoft QA visitors in the future. I glad you are all set to enjoy ADF. Have a great day!

Answer 2

Nandan Hegde 36,146 MVP Volunteer Moderator

Hey,
Can you please confirm whether the SQL connection is successful on lookup activity and is only failing during copy activity?
If yes, can you please try accessing both the source and the sink databases within the Self hosted integration run time?
because when you leverage a SHIR within either source or sink in ADF, it is a need that both the source and sink needs to be accessible via the SHIR

Steve Herz 36 Reputation points

2022-08-03T18:00:44.043+00:00

Lookup activity is indeed fine and properly returns a list of tables present on-premise to copy. The copyData activity is within a for-each loop that iterates through the list of tables provided by the lookup activity. Since it's a like to like transfer (source table 1 columns a,b,c copy to sink table 1 columns a,b,c) there is no mapping pre-defined. How do I access the source or sink from the SHIR? I've confirmed that the SHIR IP address is whitelisted in my AzureSQL server. Do I need to make sure my AzureSQL server is whitelisted on all firewalls that are applicable to the SHIR?
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2022-08-03T18:05:40.317+00:00

Hey,
You can login within the vm hosting the ir and try accessing the source and the sink from within the vm either via ssms Or powerbi Or azure data studio etc.

As stated by Alberto, there might be a scenario of either port setting or ip blockage
Steve Herz 36 Reputation points

2022-08-04T15:18:00.28+00:00

Trying to connect via SSMS and then the SHIR lead us to the firewall being the issue. Many thanks!

Share via

Unable to connect on-prem SQL database to AzureSQL database in Azure Data Factory

1 additional answer

Your answer