Troubleshoot the SharePoint Online list connector in Azure Data Factory and Azure Synapse

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

This article provides suggestions to troubleshoot common problems with the SharePoint Online list connector in Azure Data Factory and Azure Synapse.

Error code: SharePointOnlineAuthFailed

  • Message: The access token generated failed, status code: %code;, error message: %message;.

  • Cause: The service principal ID and key might not be set correctly.

  • Recommendation: Check your registered application (service principal ID) and key to see whether they're set correctly.

Connection failed after granting permission in SharePoint Online List

Symptoms

You granted permission to your data factory in SharePoint Online List, but you still fail with the following error message:

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."}.

Cause

The SharePoint Online List uses ACS to acquire the access token to grant access to other applications. But for the tenant built after November 7, 2018, ACS is disabled by default.

Recommendation

You need to enable ACS to acquire the access token. Take the following steps:

  1. Download SharePoint Online Management Shell, and ensure that you have a tenant admin account.

  2. Run the following command in the SharePoint Online Management Shell. Replace <tenant name> with your tenant name and add -admin after it.

    Connect-SPOService -Url https://<tenant name>-admin.sharepoint.com/

  3. Enter your tenant admin information in the pop-up authentication window.

  4. Run the following command:

    Set-SPOTenant -DisableCustomAppAuthentication $false

    Diagram of Azure Data Lake Storage Gen1 connections for troubleshooting issues.

  5. Use ACS to get the access token.

Related content

For more troubleshooting help, try these resources: