Sharepoint Online List Access through ADF

Question

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

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?

Answer 1

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

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!!!!

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

STEP3:

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

Copy Data: Activity

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

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].

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

Sample:

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

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)

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.