Sharepoint online list connector failed to connect

Biswajeet Pruseth 21 Reputation points
2021-09-01T04:10:48.45+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,565 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,432 Reputation points Microsoft Employee
    2021-09-02T07:26:15.99+00:00

    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.

  2. Kasper Ulvedal 11 Reputation points
    2023-10-01T16:20:03.4366667+00:00

    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.

    0 comments No comments

  3. Kasper Ulvedal 11 Reputation points
    2023-10-01T16:21:07.5033333+00:00

    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.

    0 comments No comments