Microsoft announced retirement for SharePoint Add-Ins and ASC-based app permissions (SharePoint app-only principals). Let me summarize here:
SharePoint Add-Ins, ASC-based app permissions and SharePoint app-only principals
Timeline of retirement and future plans
How to detect if there are legacy service principals used in the environment
Migration guidance
Timeline
Known key retirement milestone dates:
Mar 2024 – Microsoft will stop accepting new Add-In submission to app store
Jul 2024 – Microsoft will stop ability for customers to acquire add-ins from App Store (*)
using ACS and Add-ins will be blocked for new tenants since Nov 2024 (**)
using ACS and Add-ins will be blocked for all existing tenants since Apr 2, 2026
(*) SPFx based solutions will continue to be available, Installation from a private app catalog stays possible (**) regardless of their origin (public marketplace, private tenant catalog)
So timeline is generous, and we have plenty of time to
What you need to do to prepare
Detect if there are any legacy service principals used in the environment: Use the Microsoft 365 Assessment tool (by PnP) to scan your tenants for SharePoint Add-In usage.
After SharePoint Add-Ins are disabled, users will not be able to add SharePoint Add-Ins to their sites, and admins cannot add new SharePoint Add-Ins to the tenant and site collection app catalogs. SharePoint Add-Ins already added to sites will stay available and can still be used by the site’s users.
SharePoint Add-Ins will not be available from the public marketplace
After July 1, 2024, users browsing the public marketplace (AppSource) will see SharePoint Add-In, but if they select Get it now, a message will explain that SharePoint Add-Ins are retired and cannot be added. If you still require a specific SharePoint Add-In, contact the Add-In creator to understand the possible options.
Microsoft recently (Oct 2023) announced Microsoft Graph command-line interface (CLI) tool – mgc. Microsoft: “The Microsoft Graph PowerShell command-line interface (CLI) acts as an API wrapper for the Microsoft Graph APIs, exposing the entire API set for use from the command line”. Example:
mgc users list --filter "displayName eq 'John Smith'"
Meantime there is a Microsoft Graph PowerShell SDK (PowerShell module Microsoft.Graph ) since 2020. Example:
Get-MgUser -Filter "displayName eq 'John Smith'"
So, what is the difference? Why Microsoft provides two similar tools? What are the use case scenarios, functionality and scope of each one?
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.
Update (Nov 2024): Azure Data Factory V2 supports connection to SharePoint using certificate. So we do not need to provide legacy ACS permissions to the app registration. Instead we’d provide just Sites.Selected API permissions and use certificate to authenticate ADF to to Microsoft 365.
Below is legacy (classic) approach connecting ADF to SharePoint
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:
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:
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
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.
There is a new feature published at Microsoft roadmap site:
Microsoft 365 admin center: Manage ownerless Microsoft 365 groups and teams
Teams, Outlook groups, Team Sites etc. powered by Microsoft 365 Groups supports two roles: members and owners. Members can collaborate with others in the group through files, emails, messages etc. Owners manage the group membership and monitor content and conversations. When employees leave an organization or switch projects internally, it results in their existing user accounts getting deleted. If such employees were group owners, keeping track of their groups becomes critical to ensure accountability within the organization. We have introduced a new ownership governance policy to help automate the management of ownerless groups by requesting active members to become owners of the group. Admins can define who is eligible for these notifications and configure what notifications and how often these notifications are sent to active group members. Users, who are members of the ownerless groups can simply accept or decline request via the actionable email message.
Feature ID: 180749
Added to roadmap: 10/10/2023
Last modified: 10/10/2023
Product(s): Microsoft 365 Admin Center
Cloud instance(s): GCC
Platform(s): Web
Release phase(s): General Availability
But based on the feature description – all looks exactly as what we already have for years as “Microsoft 365 ownerless groups policy” which you can configure under Microsoft 365 Admin Center -> Settings -> Org settings -> Microsoft 365 groups
What if you need to bulk update Microsoft 365 groups membership e.g. to add a group owner or member for tens of thousands m365 groups? Iterating through groups one-by-one is unproductive and could take days. Can we do it faster? Here is what I found.
In my case, it was Microsoft 365 ownerless groups policy implementation for large tenant… Skipping details – I needed to update ownership for 10,000 Microsoft 365 groups and I was looking for a best/fastest possible option maybe some kind of bulk update or with multiple threads. And I figured out that the fastest way is to use PnP.PowerShell that calls Microsoft Graph API but run it against list of groups with PowerShell parallel trick. Here is the sample PowerShell code:
The Great Grey Owl is a large, grey owl with numerous dark and light streaks, mostly longitudinal. It lacks “ears.” It differs from the Tawny Owl by its larger size, long tail (extending well beyond the tips of folded wings), and absence of a transverse wavy pattern on the underside. Compared to the similarly sized Ural Owl, it has black eyes, lacks concentric circles on the facial disk, and has no black “beard.” Females are noticeably larger than males but have similar coloring. Juveniles replace their second downy plumage with their first adult plumage by late August to November, resembling adults but with lighter head coloring, especially the “face,” and darker, broader streaks on the body. The first adult plumage has pointed tips on the tail and flight feathers (rounded in adults), and all flight feathers are equally worn (adults have staggered feather wear). Males weigh 440-1000g, females 650-1300g, with a length of 50-62 cm, wingspan of 114-134 cm, and wing length of 33.7-37.6 cm for females and 33.7-37.0 cm for males.
Distribution
Breeds on Southwest Altai and in Kalbinskiy Altai. In winter occurs also on valleys of Irtysh and Ural rivers. One dead Ural Owl was found on road at Sarychiganak (Trans-Balkhash area) in January 26, 1989.
Biology
Very rare resident. Inhabits the fir forests with birch and asp trees at altitudes up to 1600 m. Breeds in separate pairs, to nesting uses the old nests of other birds or tree holes. Clutch contains 2-3 eggs. Brood recorded in July 31, 1971. The autumn – winter dispersals begin in the end of September.
You administer Microsoft 365 SharePoint Online. Part of your daily activities is providing Microsoft Graph and SharePoint Sites.Selected API permissions to other users (developers).
In Aug/Sep 2023 Microsoft pushed an update that prevents site collection admins to create or update an Azure Access Control (ACS) principal (that was the way most of developers used to get Client Id and Client secret to access SharePoint site). So your users are probably getting something like Your SharePoint tenant admin doesn’t allow site collection admins to create or update an Azure Access Control (ACS) principal message attempting to create or update SharePoint App-only principal at AppRegNew.aspx or AppInv.aspx pages. Here are more details on the issue.
Microsoft and MVPs shared some technique how to provide Sites.Selected API permissions, but dealing with scripts manually, elevating individual permissions every time you need to run the script – it all takes time and not very efficient. More and more devs are reaching you on the app. So you want to automate this process.
SharePoint list as a frontend here you can accept intake requests, organize approval workflow and display automation results
Azure Function App as a backend here will be your PowerShell script hosted that runs on scheduled basis and takes care of actual permissions provisioning
Solution details
High-level, getting application permissions to some specific SharePoint site is a two-step process:
get application registration in Azure and properly configure it
get permissions for this application to a specific SharePoint site
For the first step – check this and this articles. I’ll focus on the second step below.
You can provide Sites.Selected permissions for the app to a site with
I will be using second one one. Also PnP.PowerShell will be used to get access to SharePoint intake site and read/update requests from SharePoint list and so on.
Azure App Registration
I registered an admin Application in Azure – “SharePoint Automation App”, added Graph Sites.FullControl.All and SharePoint Sites.FullControl.All permissions, then added Microsoft Graph Directory.Read.All permissions and got tenant admin consent:
I generated a self-signed certificate and added it to the app:
This app will be used to call provide permissions, and to connect to the SharePoint front-end.
Users will register their applications in Azure, add Graph Sites.Selected and SharePoint Sites.Selected permissions, got tenant admin consent, then request permissions to the specific site by creating an intake request – new list item.
Front-End SharePoint Site
I created a SharePoint site for automation. This site will play a front-end role for users. I created a list “Sites.Selected” and updated list columns so I have the following fields:
Target Site Url
Application Id
Permissions (read/write)
Automation Output
In real-world (Prod) – You can (should) also implement approval workflow as you’d provide permissions for the application to the site only with this site owner approval. The PowerShell code behind should also validate site owner’s consent with app access to site. But for the sake of simplicity I’ll skip this in my demo.
Azure Function App
I created an Azure Function App with the following parameters: – Runtime stack: PowerShell Core – Version: 7.2. – OS: Windows – Hosting plan: Consumption
And then PowerShell timer-triggered function in Visual Studio Code.
Function requirements.psd1 (it takes a few hours for Azure to install modules; while modules are installing – you might see “[Warning] The first managed dependency download is in progress, function execution will continue when it’s done. Depending on the content of requirements.psd1, this can take a few minutes. Subsequent function executions will not block and updates will be performed in the background.”):
@{
'Az' = '10.*'
'PnP.PowerShell' = '2.*'
}
Azure Az module to access other Azure resources. PnP.PowerShell module will be used to access SharePoint.
I will keep my admin Azure registered app in a key vault, so need somehow to let the key vault know that this specific app can access this specific credentials. So I enabled system assigned managed Identity for the Function App:
MS: “This resource is registered with Azure Active Directory. The managed identity can be configured to allow access to other resources…”. I’m going to use an object (principal) Id of this function to grant access to keyvault.
Azure key vault
Surely we do not hard-code app secrets. So we need a key vault o store app credentials.
I created a key vault under the same resource group in Azure and named it “SharePointAutomationDemo”. Then I added a roles assignment – “Key Vault Secret User” and “Key vault Reader” to the Function App via it’s managed identity:
I also assigned “Key Vault Administrator” role to the user (developer) who will add certificates/secrets to this key vault and develop Azure function code.
With the new ‘Lists.SelectedOperations.Selected’, ‘ListItems.SelectedOperations.Selected’ and ‘Files.SelectedOperations.Selected’ permissions it is new possible to provide application permissions at a specific list, library or list item levels or at a particular document level, so automation solution would be a little more complicated.
Below I’m sharing how to find old or outdated content in SharePoint, Teams or OneDrive site. Specifically, files or documents that are older than some certain date. Why you might need that? For example – to delete content to save space or opposite – to avoid content deletion as a result of retention policies in action (* see below for details).
Search in SharePoint with query parameters (GUI)
At any level of your site hierarchy – root level, library, folder etc. – you can refine your search results specifying properties values, e.g. document author or document created date or document last modified date. For last modified date the property is “LastModifiedTime”, e.g. here I’m in the SharePoint site document library:
If I put in search box query “LastModifiedTime<2023-07-15” I’ll get only documents older than July 15 2023:
There is property “LastModifiedTime”, and there is also property “LastModifiedTimeForRetention” you can use to detect documents your retention policy works against.
When you issue query with just “LastModifiedTimeForRetention<2023-06-15” you get as results all kind of SharePoint content – including pages, libraries, folders etc. If your concern is to avoid specific documents deletion as a result of retention policy – you’d probably be interested in finding documents only and do not want folders (as retention policy applies to all files in all document libraries), e.g.
If you need only Microsoft Word documents older than some specific date (e.g. June 15, 2021), you might use query: “*.docx LastModifiedTimeForRetention<2021-06-15”
For Microsoft Word and Excel documents older than June 15, 2021 – you’ might ‘d use query: “(*.docx OR *.xlsx) LastModifiedTimeForRetention<2021-06-15”
If you need only Microsoft Word documents authored by some specific User and older than some specific date, you might use query: “*.docx author:Patti LastModifiedTimeForRetention<2021-01-01”
Search in Teams
You can successfully use refinements to search for the same in Teams. But you’d select “Files” tab for better experience:
Microsoft is constantly updating this product, so your experience might be different. Note also that when you search in teams – you search through all sites you have access to.
Search for old documents in OneDrive
You can use the same technique – putting “LastModifiedTime<2023-07-15” in search bar in OneDrive. In some ways it’s even better, as you can
search for files in all sites (not only your personal OD site)
select multiple file types you are interested in
Search with Graph API
The same query you can use to search content with Microsoft Graph API. Here is the code example:
If an organization is mature enough – it has data lifecycle policies established. If so – these polices must be applied to information stored in Microsoft 365 via retention policies. Retention policies are configured under Compliance center, but in particular applied to documents stored in SharePoint and OneDrive. Policies might dictate to retain documents or delete documents. Let say your organization is implementing retention policy that is configured to delete documents if 5 years passed after the file was last modified. That literally means all your files modified more than 5 years ago will be deleted and you will not even notice it. So – what if you want to know – which documents in your OneDrive or SharePoint site are older than 5 years?
Note: if your content was migrated from previous SharePoint versions (e.g. your old SharePoint on-prem farm) using migration tools – it’s last modified date is most likely was preserved (for instance, if your 5-years old doc was migrated 1 month ago – it last modified date would be 5 years ago). So if you are going to migrate your existing on-prem SharePoint to Microsoft 365 site and there are retention policies implemented in m365 – your content might be deleted right after migration.
Video tutorial
How to fetch all SharePoint documents older than some amount of time:
