question

JoelPrescilla-8206 avatar image
0 Votes"
JoelPrescilla-8206 asked NandanHegde-7720 commented

DataFactory Copy Activity Error

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




azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoelPrescilla-8206 avatar image
0 Votes"
JoelPrescilla-8206 answered NandanHegde-7720 commented

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?

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey,
Login to the server on which you have SHIR installed.

Try connecting to the on Prem SQL server and your Azure SQL database from that server either via SSMS or powerbi etc.

Either of the one is not currently being accessible because of which you are getting error in just copy activity.

Take the necessary steps as to port opening or IP whitelisting etc for the other part to be accessible from the SHIR vm

0 Votes 0 ·

Hello @JoelPrescilla-8206 ,
We haven’t heard from you on the last response from @NandanHegde-7720 and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

Hi,
Yes the issue was resolved by allowing connection frim both source and sink. Thank you

0 Votes 0 ·

Hey @JoelPrescilla-8206 ,
Can you please accept it as answer as it might help other community members as well

0 Votes 0 ·