Let me share here
- how to connect to SharePoint list from Azure Data factory 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“
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 here could be
- SharePoint app-only service principal registered at SharePoint site
- Azure registered app
In both cases you get “service principal Id” – 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.
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:
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).
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
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 :
<p class="heading1">Request Error</p>
<p>The server encountered an error processing the request. See server logs for more details.</p>
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
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 (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
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
SiteOwnerManageLegacyServicePrincipalEnabled = False (almost always)
SiteOwnerManageLegacyServicePrincipalEnabled = True (when provide ACS)
LegacyAuthProtocolsEnabled = True
LegacyBrowserAuthProtocolsEnabled = True
BlockAppAccessWithAuthenticationContext = False
DisableCustomAppAuthentication = False
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.