Microsoft Azure Data Factory connect to SharePoint ⋆ Vlad Software Engineer

Below is the my guide on how to connect Azure Data Factory to SharePoint and how to deal with connection error code 23201 “Failed to get metadata of odata service, please check if service url and credential is correct and your application has permission to the resource“

Scenario

You are configuring Azure Data Factory pipeline. You want to connect to SharePoint List as a data source.

To establish connection to SharePoint site you need to provide
Site Url, tenant Id, service principal Id and service principal key:

Service principal

Service principal here could be

SharePoint app-only service principal registered at SharePoint site via appregnew.aspx
Azure registered app (app registered in EntraId)

In both cases you get “service principal” – which is App Id or Client Id and “service principal key” which is app secret (client secret).

Note: in Sep 2023 Microsoft implemented update to all Microsoft 365 tenants. According to the update, by default only tenant administrators can create or update ACS service principal by default. If site collection admin starting from Oct 2023 can register SharePoint app-only spn via appregnew.aspx or provide ACS-based permissions via appinv.aspx – that means tenant admins switched this back.

So, if registering a new SharePoint app-only service principal is still available for your tenant – you can get service principal Id and key from SharePoint via appregnew and/or provide ACS-based permissions via appinv – and there should be no problem connecting to SPO list from ADF (June 2024), but please review special note below.

If a site collection administrator or owner tries to register app in SharePoint with appregnew.aspx or provide permissions to the app with appinv.aspx – and he/she gets:
Your SharePoint tenant admin doesn’t allow site collection admins to create (update) app permissions. Please contact your SharePoint administrator:

Your SharePoint tenant admin doesn’t allow site collection admins to create an Azure Access Control (ACS) principal

that means registering service principal in SharePoint is disabled. In this case Microsoft recommend using Azure application registration – with MS Graph API Sites.Selected and SharePoint Sites.Selected API permissions configured, consented by tenant admin and with access to specific SharePoint site provided by SharePoint admins (refer to this article for more details).

Issue

If you obtained service principal Id and key as Azure Registered App – connection to SharePoint site from Azure Data Factory does not always works. When you configure connection and click test – you might get an error:

error code: 23201

error details:

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: BadRequest, response is : 
...
    <title>Request Error</title>
<body>...
      <p class="heading1">Request Error</p>
      <p>The server encountered an error processing the request. See server logs for more details.</p>
...</body>

Reason

So the issue above is a combination of two controversial circumstances:

Microsoft discourages using SharePoint app-only service principals and disabled ability for site owners to register SharePoint app-only service principals and provide ACS-based permissions in favor of Azure Registerd Apps with Sites.Selected based permissions.
Azure Data Factory still require ACS-based permissions (Upd: June 2024 – still true)

Solution

If Microsoft disabled ability for site owners to to provide ACS-based permissions for the app – that does not mean it is fully disabled. It turns out – SharePoint admins are still able to register SharePoint app-only principals and provide ACS-based permissions.

The recommended steps are:

register Application in Azure (in EntraId, not in SharePoint) to get App (client) Id
this could be done by user from Azure App Registrations (or, if this ability is disabled by tenant admin – there must be a way for users to request an application registered in Azure)
provide to this App Id ACS-based permissions at the target SharePoint site via appinv.aspx – this is done by a person who got at the same time two roles
- SharePoint admin role enabled and
- The specific site collection administrator permissions
  so if your role is a regular user or developer (not an admin) – you’d request this service from your admins
provide Sites.Selected permissions for the App to the target Site
again, this is something you’d need to request from your admins –
tenant admin should be able to provide admin consent to SharePoint and Graph API Sites.Selected permissions for your app and SharePoint admin should be able to provide actual permissions for the app to the site.

Technical Details

(as per June 2024 – still true) Actually, the only you need is to provide any ACS-based access for the application. Even to another site, web or list. You can also remove this just provided ACS-based access.
It seems like the moment you click “Trust” when you provide access via AppInv.aspx – something is triggered in Microsoft Identity Management token issuing mechanics so Azure Data factory connection starts working (assuming Sites.Selected access was provides).

Surely connection will work if you provide only ACS-based permissions (with no Sites.Selected permissions), but this is what we all want to avoid by any means.

More fun! Connection to entire site will start working even if you provide SharePoint app-only (ACS-based) permissions to some specific list. Though later, when you try to ingest data – you will be able to ingest only this list data.

Environment this is tested:

Powershell module used to enable/disable
16.0.24120.12000 Microsoft.Online.SharePoint.PowerShell

SiteOwnerManageLegacyServicePrincipalEnabled = False (almost always)
SiteOwnerManageLegacyServicePrincipalEnabled = True (when provide ACS)

LegacyAuthProtocolsEnabled = True
LegacyBrowserAuthProtocolsEnabled = True
BlockAppAccessWithAuthenticationContext = False
DisableCustomAppAuthentication = False

About Sites.Selected

Microsoft implemented Sites.Selected API permissions for Azure registered apps in 2021-2022 as a preferred way to access specific SharePoint site with application credentials. Microsoft recommend using Azure registered apps instead of SharePoint App-Only service principals and “softly” push developers toward Azure registered apps. Microsoft recently (Aug-Sep 2023) implemented an update and pushed it to all existing Microsoft 365 tenants – so that ability for site admins to register service principals at sites is turned off by default.

So starting Aug-Sep 2023 site owners/admins cannot register and provide ACS-based permissions for apps to their SharePoint sites.

In 2024 Microsoft announced EOL for SharePoint app-only spns and ACS-based permissions.

Special note

This article is written in 2023 with the sole purpose to help you resolve the issue. And it all is still true (validated in June 2024). But! I assume that sooner or later (before April 2026) Microsoft will address it’s own issue and update Azure Data Factory so ADF will be accepting permissions provided via Sites.Selected only. That is why – at the moment – I strictly recommend:

Use Azure Registered App (not a SharePoint app-only spn)
Get both types of permissions for this app:
- modern – Sites.Selected SharePoint and Graph API permissions
- old/classic – ACS-based permissions

if so – your data pipeline should continue working smoothly when MS implement modern authentication in ADF.

References

tbp:

How to use KeyVault with Azure Data Factory connection to SharePoint
How to configure REST connection to SharePoint

Vlad Software Engineer

Microsoft 365 SharePoint, Teams, PowerShell, Azure, Search, Governance, Automation etc.

Microsoft Azure Data Factory connect to SharePoint