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