Sharepoint online list connector failed to connect

Question

Hi,

I am trying to fetch data from sharepoint online list to ADLS using synapse pipelines. For this I tried to follow the documentation as follows:

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

I completed all the prerequisites mentioned in the page. I tried creating a "SharePoint Online List" linked serice and used the following details:

1. Site url - https://xyz.sharepoint.com/sites/TestSharepoint ( used "xyz" instead of actual name to maintain privacy)
2. Tenant id - Tenant id of app registration
3. Service principal id - Application id of app registration
4. Service principal key - Client secret of the app registration

But when I tried to test the connection it is giving me the following error -

"Failed to get metadata of odata service, please check if service url and credential is correct and your application has permission to the resource. Expected status code: 200, actual status code: Unauthorized, response is : {"error":"invalid_request","error_description":"Token type is not allowed."}"

Please help me if I am missing anything.

Regards,

Biswajeet Pruseth

Answer 1

Hi @Biswajeet Pruseth ,

Thanks for using Microsoft Q&A forum and posting your query.

Since you have confirmed that you have already followed all the steps mentioned in the document, if yours is a new O365 tenant, then I suspect that your issue could be related to this post: SharePoint Online authorization issue 'Token type is not allowed'

For new SharePoint subscription Grant App Permission is disabled by default or the browser link https://xxxx-admin.sharepoint.com/_layouts/15/appinv.aspx is disabled. To enable this feature, we need to connect to SharePoint using Windows PowerShell and then run set-spotenant -DisableCustomAppAuthentication $false.

Run the following commands on PowerShell.

Install-Module -Name Microsoft.Online.SharePoint.PowerShell  
$adminUPN="<the full email address of a SharePoint administrator account, example: jdoe@contosotoycompany.onmicrosoft.com>"  
$orgName="<name of your Office 365 organization, example: contosotoycompany>"  
$userCredential = Get-Credential -UserName $adminUPN -Message "Type the password."  
Connect-SPOService -Url https://$orgName-admin.sharepoint.com -Credential $userCredential  
set-spotenant -DisableCustomAppAuthentication $false  

Please note:

• Fill in the values for the $adminUPN and $orgName variables (replacing all the text between the quotes, including the < and > characters), and then run the following commands at the SharePoint Online Management Shell command prompt:
• When prompted with the Windows PowerShell credential request dialog box, type the password for the SharePoint admin account.
• Afterward, run https://xxxx-admin.sharepoint.com/_layouts/15/appinv.aspx to grant permission
• Generate new token and CRUD operations using REST API on SharePoint should be working by now.

Additional link: https://learn.microsoft.com/powershell/sharepoint/sharepoint-online/connect-sharepoint-online?view=sharepoint-ps

Also I would recommend you to please test connection using POSTMAN before testing it in ADF linked service to make sure that the necessary connection configurations are properly configured in Sharepoint side.

Hope this info helps. Do let us know how it goes.

----------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Answer 2

I've had the same issue, and spend some time understanding why and how to resolve.

I have made the following guide on what to do in Sharepoint, to allow Data Factory to access SharePoint online.

How to article here:

https://github.com/kasperulvedal/Azure-Data-Factory-Stuff/blob/main/Linked%20Services/Sharepoint/Setting%20up%20Sharepoint%20so%20Azure%20Data%20Factory%20can%20access%20it.md#setting-up-sharepoint-so-azure-data-factory-can-access-it

Sharing the link, just in case someone will run into the same issue.

Answer 3

I've had the same issue, and spend some time understanding why and how to resolve.

I have made the following guide on what to do in Sharepoint, to allow Data Factory to access SharePoint online.

How to article here:

https://github.com/kasperulvedal/Azure-Data-Factory-Stuff/blob/main/Linked%20Services/Sharepoint/Setting%20up%20Sharepoint%20so%20Azure%20Data%20Factory%20can%20access%20it.md#setting-up-sharepoint-so-azure-data-factory-can-access-it

Sharing the link, just in case someone will run into the same issue.