Access SharePoint file from Azure Data Factory

Question

I am new to this forum and I have posted the same question to a more general forum. My apologies. It seems this forum is more specific to Azure.

I am trying to load a CSV file stored in SharePoint into an Azure SQL Server table.

To do this I create Azure Data Factory pipeline. In this pipeline I am using a Web Activity with the following settings:

URL: url of CSV on SharePoint Method: GET Authentication: Service principal Authentication reference method: Inline Tenant: my-tenant Service principal ID: ID of enterprise app Service principal Key: client secret of enterprise app Resource: https://graph.microsoft.com

When debugging the pipeline, this activity fails with the following error: Error code 2108: User configuration issue: 401 UNAUTHORIZED.

The enterprise app permission is Microsoft Graph: Sites.Readwrite.All. I have confirmed that under Enterprise Apps > Sign-ins, the sign-in is successful.

At this point, I am at a loss on how to further debug this.

Answer 1

Hi TYO,

Thanks for reaching out to Microsoft Q&A.

401 UNAUTHORIZED error suggests there may be an issue with the permissions or the way the authentication is configured. Here are a few things you can check and try to resolve this issue:

• Ensure that the Sites.ReadWrite.All permission is granted as a delegated permission or application permission depending on your scenario. It must also be admin-consented if it's an application permission. Verify that the permission is correctly assigned and that the app is properly consented.
• Double-check that the access token you receive using the service principal authentication has the correct permissions by decoding the JWT token. Also, ensure the token includes the appropriate scopes (Sites.ReadWrite.All) and that it's not expired.
• Make sure that the sharePoint URL is correct and accessible. Validate that the service principal has access to the specific SharePoint site and document library where the CSV file is located.
• Ensure the service principal has been granted the correct permissions to the SharePoint site. You might need to add the service principal as a Site Collection Admin or grant specific site permissions.
• Instead of using a Web Activity, try creating an HTTP Linked Service in ADF for connecting to the SharePoint site. Configure the Linked Service to use OAuth2 and provide the necessary details for the service principal.
• Turn on detailed logging in your ADF pipeline to capture more information about the failure. Check the AAD logs for more details on the 401 error.
• If you're working with a large file or experiencing network delays, the token might expire before the operation completes. Implement a retry mechanism in your pipeline or check if the token needs to be refreshed.

If nothing worked, consider capturing and analyzing the request and response headers for more insights.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.