This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(140.8, 75%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETJ%3C/text%3E%3C/svg%3E)
Hi,
I have added an Azure SQL server to Azure Data Factory as a Linked Service. I can connect to it and preview the data in Azure Data Factory without issue. I can also trigger Stored Procedures on the Azure SQL server which includes adding dummy data to a landing table.
However If I try and use the linked server as a Copy Data activity it fails with the following error:
ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XYZ', Database: XYZ: 'XYZUser'. 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 network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.),Source=.Net SqlClient Data Provider,SqlErrorNumber=11001,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=11001,State=0,Message=A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.),},],''Type=System.ComponentModel.Win32Exception,Message=No such host is known,Source=,'
This doesn't make any sense as I can preview data on the server and trigger stored procedures so why won't it connect to the same server and run a Copy Job
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 38%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EQ%3C/text%3E%3C/svg%3E)
Hello @TOWNSON, James,
Did you try to whitelist the IP address as per below suggestion?
Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
If the response is helpful, please click "Accept Answer" and Click 'Yes'. So that we can close this thread.
Hello @TOWNSON, James,Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you! Please let us know if you need any offline help in this regard!
Is the IR used in the source of your copy activity able to access the sink? I observed kind of similar issue wherein user was able to preview the data but the copy activity was failing, when the Azure SQL was whitelisted on the Self Hosted IR of source, the copy activity executed successfully
Thanks
Thanks, when I run a pipeline I created to test running a stored procedure and then carry out the copy job I noticed the IR changes. The source of the data is an on premise server, there are no errors relating to accessing this and I can see the data in preview. What do I need to whitelist? Do I need to somehow whitelist ADF on the Azure SQL Server its trying to copy data to?
You need to whitelist Azure SQL on the Self Hosted Integration Runtime. As you can see the Copy Activity is using integrationRuntime (which is SHIR) so it demands the sink to be accessible as well. Try doing a telnet on the Azure SQL Server on port 1433 in the SHIR machine, if it fails then you need to get that whitelisted
Thanks - I have whitelisted the SHIR IP address on the Azure SQL server (both public and private ip) and its made no difference. Telnet / ODBC and even ping is not working from the SHIR machine, just trying to figure out why. The Azure SQL server is in another resource group - not sure if that should affect it
Thanks for your response
You added the SHIR IP on the Azure SQL Server, but the SHIR network might be blocking connection to Azure SQL Server. You need to whitelist Azure SQL on SHIR, I think what you currently did is reverse :)