ADF throwing 403 error while copying

Question

Hi,

I have Azure synapse analytics setup with private endpoint and I am running a ADF pipeline but it throws the error:

{
    "errorCode": "2200",
    "message": "ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Not able to validate external location because The remote server returned an error: (403) Forbidden.',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105215,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105215,State=1,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,},],'",
    "failureType": "UserError",
    "target": "Synapse sql stage",
    "details": []
}

The solution provided by Microsoft site is https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format#failed-with-an-error-sqlserverexception-not-able-to-validate-external-location-because-the-remote-server-returned-an-error-403

But not sure how to implement this solution. I just want to know step by step what needs to be done here.

Regards

Rajaniesh

Answer 1

Hi @Rajaniesh Kaushikk

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue: I have Azure synapse analytics setup with private endpoint and I am running a ADF pipeline but it throws the error:

{     "errorCode": "2200",     "message": "ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Not able to validate external location because The remote server returned an error: (403) Forbidden.',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,Source=.Net SqlClient Data Provider,SqlErrorNumber=105215,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=105215,State=1,Message=Not able to validate external location because The remote server returned an error: (403) Forbidden.,},],'",     "failureType": "UserError",     "target": "Synapse sql stage",     "details": [] }

The solution provided by Microsoft site is https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format#failed-with-an-error-sqlserverexception-not-able-to-validate-external-location-because-the-remote-server-returned-an-error-403

But not sure how to implement this solution. I just want to know step by step what needs to be done here.
Solution: To fix this issue I have done my own research and created a video and blog so others can benefit from it. Video link:https://youtu.be/GXLXGNRqAZc blog link :https://rajanieshkaushikk.com/2023/09/09/demystifying-azure-synapse-networking/

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi @Rajaniesh Kaushikk
Thank you for reaching out to the Azure community forum with your query.
The error message you provided suggests that the pipeline is not able to access the external location due to a permission issue. To troubleshoot this issue, you can follow these steps:

• Ensure that the firewall settings are configured correctly to allow traffic from the private endpoint to the external location.
• Ensure that the private endpoint is configured correctly to allow traffic to the external location.
• Ensure that the external location is configured correctly to allow traffic from the private endpoint.

After verifying the firewall and private endpoint settings, retry the pipeline to see if the issue is resolved. If the issue persists, you can try checking the authentication settings and the external location settings.

If still faceing the same issue, try adding the "SqlAccessCredential" property with the value "AzureIntegrationRuntime" in the Additional properties section of the Linked Service.

The "SqlAccessCredential" property is used to specify the SQL authentication credentials for the linked service. By setting the value to "AzureIntegrationRuntime", the linked service will use the managed identity of the Azure Integration Runtime to authenticate with the SQL database.
I hope this helps you resolve the issue. Let me know if you have any further questions or concerns.

Answer 3

To fix this issue I have done my own research and created a video and blog so others can benefit from it. Video link:https://youtu.be/GXLXGNRqAZc blog link :https://rajanieshkaushikk.com/2023/09/09/demystifying-azure-synapse-networking/