Unauthorzied 401 while accessing sharepoint

Question

Good day!

I followed the official docs to establish a connection between ADF and Sharepoint:
https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory

Though, when I execute a pipeline I encounter an error:
"...Message: Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials..."

The web activity to Get the Token works fine, the problem is when I actually want to access data (sharepoint online list) and at his point I am running out of ideas.

What I did:

1. I reviewed the Registered App credentials (Tenant ID, App Id) --> typed correctly in ADF
2. Same goes for the secret key
3. The App has been added in Sharepoint Online (with full control privilege).

Before I tried using just Copy Data activity (making Sharepoint Linked Service, not HTTP one like in the above situation) and ADF managed to download the lists names, though it copied no data (even though lists contain data).

It seems like there is some access/privileges issue? Should a permission be granted to the registered app somewhere else? Maybe the Sharepoint List should have the app added in the 'Managed Access'? (However I could not find my app there).

Some details, might be crucial:

1. In HTTP linked service I gave the following Base URL: https://<name>.sharepoint.com
2. In Dataset I gave the rest (Relative URL): /sites/<site-name>/Lists/<list-name>/AllItems.aspx

Would be glad for quick response.

Thanks

Answer 1

@Jan Wojciechowski

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 .

Ask: I followed the official docs to establish a connection between ADF and Sharepoint:

https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory

Though, when I execute a pipeline I encounter an error:
"...Message: Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials..."

The web activity to Get the Token works fine, the problem is when I actually want to access data (sharepoint online list) and at his point I am running out of ideas.

What I did:

1. I reviewed the Registered App credentials (Tenant ID, App Id) --> typed correctly in ADF
2. Same goes for the secret key
3. The App has been added in Sharepoint Online (with full control privilege).

Before I tried using just Copy Data activity (making Sharepoint Linked Service, not HTTP one like in the above situation) and ADF managed to download the lists names, though it copied no data (even though lists contain data).

It seems like there is some access/privileges issue? Should a permission be granted to the registered app somewhere else? Maybe the Sharepoint List should have the app added in the 'Managed Access'? (However I could not find my app there).

Some details, might be crucial:

1. In HTTP linked service I gave the following Base URL: https://<name>.sharepoint.com
2. In Dataset I gave the rest (Relative URL): /sites/<site-name>/Lists/<list-name>/AllItems.aspx

Would be glad for quick response.

Solution: To fix the issue, I had to add another Web activity with:

• a direct URL to the file
• Method: POST

And a 3rd Web activity (instead of copy activity) to save the downloaded file in a blob container.

Using copy activity does not work.. I mean, I can create a Sharepoint Online Linked Service and there is a connection establish between ADF and SPO - List of SPO lists appears and I can choose a particular list, but once I hit Debug in ADF, no data is downloaded from the list, like it was empty.

Perhaps, the issue is related to what other guy wrote - generating access token using Secret is limited, while using the Certificate I would do more.

Anyway, I managed to make a workaround and it is working fine.

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.

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.

Was this answer helpful?Yes

Answer 2

@Jan Wojciechowski

Welcome to the Microsoft Q&A and thank you for posting your questions here

You're trying to connect Azure Data Factory (ADF) to SharePoint Online List using the official Microsoft documentation. The Web activity to get the token works fine, but when you try to access the SharePoint list, you encounter a 401 Unauthorized error.

Potential causes:

Insufficient permissions: The registered app might not have the necessary permissions to access the SharePoint list. Incorrect configuration: There might be an issue with the Base URL or Relative URL in the HTTP linked service or dataset.

1. Check the API permissions: You've granted Sites.ReadWrite.All and User.Read.All permissions, but you might need to add Sites.Read.All or Sites.Write.All explicitly to allow the app to read or write to the SharePoint list.
2. Verify the Base URL and Relative URL: Double-check that the Base URL https://<name>.sharepoint.com and Relative URL /sites/<site-name>/Lists/<list-name>/AllItems.aspx are correct and match the SharePoint site and list you're trying to access.
3. Check the app's permissions in SharePoint: Ensure that the app has been granted the necessary permissions in SharePoint. Since the app doesn't appear in the list when trying to grant permissions, you might need to check the SharePoint site collection's app permissions or contact your SharePoint admin for assistance.
4. Review the Azure AD app registration: Verify that the Azure AD app registration is correct, and the app ID, tenant ID, and secret key are correctly configured in ADF.

Before we dive deeper, I'd like to ask a few questions to clarify the situation:

• Have you tried using the Copy Data activity with the SharePoint linked service (not HTTP) again, and if so, did it work?
• Are there any other error messages or details in the ADF pipeline run logs that might provide more insight into the issue?

Hope this helps. Do let us know if you any further queries. .