Sharepoint Online List Access through ADF

Aniket Roy 20 Reputation points Microsoft Vendor
2023-05-19T09:10:18.0233333+00:00

I am trying to get Sharepoint online list data thru ADF

I have followed all the steps mentioned in this doc https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory#copy-file-from-sharepoint-online

I am able to get the access_token
User's image

But while trying to pull the data by GET method using this token(in copy activity), I am getting this error:

ErrorCode=HttpRequestFailedWithUnauthorizedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settings.

Can someone please help regarding this?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,184 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,280 questions
{count} votes

Accepted answer
  1. QuantumCache 20,261 Reputation points
    2023-05-24T10:33:04.46+00:00

    Troubleshooting Steps:

    • AAD Application, API Permissions, Check if your App needs Grant Admin consent?
    • On sharepoint Site, please double check the App permissions (on site level or Global level?), Redirect URL, permissions XML.
    • Do the HTTP, API call testing from POSTMAN (I mean outside of ADF) to make sure the same REST API is working and then come back to ADF!!!!

    STEP1: Register an AAD Application, make a note of the ClientID , TenantID, and the Secret Value

    User's image

    User's image

    STEP2:

    I have carefully added the ClientApp to the Sharepoint Site's App permissions, Please take a closer look at the documentation which I have followed exactly!!!!

    User's image

    Once the Permission is added, better to verify!!!!

    User's image

    User's image

    STEP3:

    Carefully add the required fields for Fetching the Auth Token, I followed the documentation, it helped!

    User's image

    Copy Data: Activity

    Please use the Concat function to pass the Auth Token as mentioned in the documentation.

    User's image

    Source DataSet Configurations:

    This the part where most of us face issues with Relative Path to the resource on Sharepoint site. Please check the relative path carefully. Few trials and tests have to be done if you are not sure and doing it for the first time!

    Example: For simple Folder-File.csv on a Site, below is the Relative path.

    Base URL:https://msft465.sharepoint.com

    Relative URL: /sites/londonlabs/_api/web/GetFileByServerRelativeUrl('/sites/londonlabs/Shared Documents/Input/datamart.csv')/$value

    The HTTP connector copies data from the combined URL: [URL specified in linked service]/[relative URL specified in dataset].

    User's image

    Sink Dataset: This is a regular config for your destination!

    Sample:

    User's image

    POSTMAN testing

    I did the base testing for the API calls from POSTMAN and i was able to read the CSV file from Sharepoint site

    User's image

    We have tested this Flow from 2 scenarios: and it worked well.

    Same Tenants:

    SharePoint(Tenant A) & ADF/AAD App (Tenant A)

    Cross Tenants:

    SharePoint(Tenant A) & ADF/AAD App (Tenant B)

    User's image

    If the response is helpful, please click "Accept Answer" and upvote it. So that we can close this thread.
    Happy to help you further, please post a new question or comment in the below section.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful