ADF throwing 403 error while copying

Rajaniesh Kaushikk 476 Reputation points MVP
2023-09-29T17:10:11.98+00:00

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": []
}

synapse

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,473 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,756 questions
{count} votes

Accepted answer
  1. Smaran Thoomu 10,875 Reputation points Microsoft Vendor
    2024-04-10T06:02:34.5633333+00:00

    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": [] }

    synapse

    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.

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Smaran Thoomu 10,875 Reputation points Microsoft Vendor
    2023-09-30T09:32:10.0166667+00:00

    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.


  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    3 deleted comments

    Comments have been turned off. Learn more

  3. Rajaniesh Kaushikk 476 Reputation points MVP
    2024-04-09T18:17:52.71+00:00

    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/