DataFactory Copy Activity Error

Question

Hi,
I am facing the below issue in Data Factory:

ErrorCode=SqlFailedToConnect,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database: '[servername]', Database: '[dbname]', User: '[username]'. 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=Cannot open server '[server]' requested by the login. Client is not allowed to access the server.,Source=.Net SqlClient Data Provider,SqlErrorNumber=40914,Class=14,ErrorCode=-2146232060,State=1,Errors=[{Class=14,Number=40914,State=1,Message=Cannot open server '[server]' requested by the login. Client is not allowed to access the server.,},],'*

The weird thing is the linked service test connection is a success.... some activity like lookup via a stored proc is working as well, and can preview data... but when running the pipeline I get the above error during the Copy Activity only.

I have added all possible IP address in my region in the whitelist and still not successful.

Also, when I switch on "Allow Azure services and resources to access this server", it works.... Obviously, I still prefer IP whitelisting but not sure what is happening. Thanks

Answer 1

Hey,
Based on the above scenario , My assumption is either your source or sink in copy activity is leveraging an integration run time.
In that scenario , the server on which the IR is set up should be able to access both the source and the sink.

So login to the IR server and try accessing both source and sink from it and accordingly take the appropriate steps of whitelisting or opening ports.

Answer 2

Hi.. Thank you for the response...

My setup is source is SQL Server via SHIR, and sink is Azure DB SQL using Azure IR. So you want me to check the SHIR server, then grab the IP, and add that to the whitelist?