Category Archives: Azure

Authorization to Microsoft Graph: Azure Registered Apps API permissions

Being authenticated to Microsoft 365 tenant means Microsoft 365 knows who is trying to get access. To actually be able read/write or manage resource, your app must be Authorized to this resource.

For details – pls refer to MS authorization and Microsoft Graph API permissions. But again, in short in our case that means we need to have an API permission configured for our azure registered app. There are two kinds of API permissions – delegated and application.

Delegated permissions are intended to allow currently authenticated user to have access to the resource. Effective user permissions in this app would be an intersection of user own permissions and app permissions. So if an app have “Sites.FullControl.All” SharePoint delegated API permissions – that does not mean that user will have full control over all sites.

Application permissions are what it says – once permissions are configured – application will have access to the resources according to API permissions.

Generally, application permissions allow an app to have access to all resources of the same kind in tenant, e.g. to get one specific groups owners an app must have “GroupMember.Read.All” permission that allows an app to read all tenant groups and their members. There are some exceptions – e.g. for Teams Microsoft developed RSC that allows scoped app access. For SharePoint there is a similar option – “Sites.Selected” API permissions.

API permissions must have an Admin consent (see below).

References

Search m365 SharePoint and Teams content programmatically via MS Graph API

In the articles below I’m sharing my techniques on searching in Microsoft 365 SharePoint and Teams from application using Microsoft Graph API.
Specifically I’m covering

  • Microsoft Graph search API
  • Microsoft.Graph PowerShell module
  • PnP.PowerShell module

In two flavors:

  • Search on behalf of currently authenticated user
  • Unattended Search with daemon (also called service) applications

Index of my articles on the subject:

Video tutorials

SharePoint Add-Ins and ASC retirement

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 tenants since Apr 2026

(*) SPFx based solutions will continue to be available

So timeline is generous, and we have plenty of time to

Detect if there are any legacy service principals used in the environment

WIP…

Microsoft Azure Data Factory connect to SharePoint

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:

  1. 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)
  2. 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
  3. 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:

  1. Use Azure Registered App (not a SharePoint app-only spn)
  2. 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

Massive Microsoft 365 groups update with PowerShell

What if you need to update thousands of m365 groups? E.g. membership – to add a group owner or member.

In my case, it was Microsoft 365 ownerless groups policy… Details are here but in short, I needed to update ownership for 10,000 m365 groups adding 10 accounts to group owners, 1,000 groups per account.

And I figured out for myself that the fastest way is to use Microsoft Graph API with PowerShell parallel trick. Here is the PowerShell code:

$groups | ForEach-Object -Parallel {
    $owner = "user2del27@vladslab.onmicrosoft.com"
    Add-PnPMicrosoft365GroupOwner  -Identity $_.Id -Users $owner
} -ThrottleLimit 50

In my environment, it took ~ 8 seconds per 1,000 groups.

Sites.Selected permissions provisioning automation

Scenario

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.

Solution

Solution architecture

My way to automate it includes:

  • 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:

  1. get application registration in Azure and properly configure it
  2. 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.

Code repository

https://github.com/VladilenK/Sites.Selected-Automation

Videos

Part 1: Getting Azure App Registration with Sites.Selected API Permissions

Part 2: SharePoint and Microsoft Graph API Sites.Selected permissions provisioning automation

Microsoft Forms Troubleshooting

So far some findings I came up with after several Microsoft forms troubleshooting sessions… I’ll keep all the gotchas here as “how to” guide for myself. I’d be glad if this also helps you troubleshoot your Microsoft forms.

Microsoft forms links

You know, a user can create a Microsoft form. Then user can share it. There are two kind of links –

  • to respond
  • to edit/view/export results

Link to respond is kind of :
https://forms.office.com/Pages/ResponsePage.aspx?id=FHPcfQGf1UWwEnFmW7HFRMgvShgV5J1Phpi7J1M_UoVUOUI1TzNQUEdWOTAzVVdRUVYzVVg4MlhZNC4u
or short one: https://forms.office.com/r/kDKaHWauj7

Link “to collaborate” -e.g. with the link a person can edit and view results – is created under … “Create or duplicate”, and could be for anyone, for all people in org, and for specific people in org

if the link looks like
"https://forms.office.com/Pages/DesignPageV2.aspx?subpage=design&FormId=<FormId>"
then it’s for specific people in org

if the link looks the same but also contains
"&Token=e3cd16ccf8034a3e868c68747e1f9584"
then it’s for anyone with work or school account or for anyone in the organization

The one with the “edit” link can edit the form (including questions, answers options, and form visibility , view responses, delete responses, create a “summary link”, create a duplicate link, and export responses to excel (“Open in Excel” button). But cannot change collaboration options.

When user complete the form (after submit button), there is an option “Save my response” – if so – user will see this for with only one (his/her) response under forms app.

Collaborator is not seeing the form he/she has access to until follow the link.

Move the form to a group

Form owner can move the form to a group (and this is strictly recommended for all production forms). If so:

  1. people who are group members (not only owners) will see this form under forms app – under specific group
  2. form id will be changed, so the long “respond” link will be different. Though the short link will be the same. All links should continue to work: Old and New long and short respond links. Group-owned form id seemed to me be little longer – 88 characters vs 80 chars for individual-owned forms.

The trick Tomasz Szypula @toszypul shared here (I’m also citing the trick below) on how to find form owner having just a link works like a charm! Even for deleted owner`s IDs.

But let me share some more here. If the form is owned by group – the link will be similar, but with “/group/<groupId>” instead of “/user/<UserId>” .
E.g. here:
https://forms.office.com/formapi/api/7ddc7314-9f01-45d5-b012-71665bb1c544/groups/65714e55-87f4-49c3-b790-fc75d7349c8a/light/...

you can see “65714e55-87f4-49c3-b790-fc75d7349c8a” which is group Id. So you can use the same trick to figure out what group owns a form.

Deleting user who owns forms

What if the original owner of a form is no longer with a company? How can I transfer ownership of the form?

If the employee account was deleted or disabled, the global administrator or office application administrator of the organization who have a valid Forms license can transfer for ownership within 30 days of when an account was disabled/deleted. See details. Note that all forms user owned will be transferred to an admin, then an admin can transfer forms to a group so new owners can have access to answers etc.

Deleting a group that owns forms

When a form is owned by group and the group is getting deleted… tbp…

Audit log events

You can get some ideas on the form from an audit log, including

  • is the form owned by group or by user
  • to whom the form was shared with collaborator link

Below are kinds of events related to Microsoft 365 forms:

  • ListForms – Listed forms – viewed forms home page with list of forms
  • ViewForm – Viewed Form –
  • ViewRuntimeForm – Viewed response page
  • ViewResponses- Viewed responses
  • CreateResponse – Created response
  • ExportForm – Exported form – “export to excel” – file saved to the local machine (form owner=user)
  • ConnectToExcelWorkbook – Connected To Excel Workbook – “export to excel” – file saved to the teams SharePoint site under Documents (form owner = group)
  • EnableSameOrgCollaboration –

How to find Microsoft forms form owner

(credit goes to Tomasz Szypula @toszypul )


toszypul   replied to  Jason_B1025
‎Jan 03 2022 03:17 AM - edited ‎Jan 03 2022 03:18 AM 

@Jason_B1025 I was able to get the ID of the user with a bit of a hack. Here are sample steps:

-Access the form using this designer direct URL https://forms.office.com/Pages/DesignPage.aspx?origin=shell#FormId=<YourFormID>

-Inspect the network traces. You will find a request similar to this 

https://forms.office.com/formapi/api/72f988bf-86f1-41af-91ab-2d7cd011db47/users/e5351c57-d147-418e-89ab-3a3d50c235b6/light/forms('v4j5cvGGr0GRqy180BHbR1ccNeVH0Y5Bias6PVDCNbZUOUg4TkZJUEswSVQ1ODhNNkpHVVlMMldPTi4u')?$select=id,... 

-The ID in bold is the AAD ID of the user
-Use Graph Explorer - Microsoft Graph to run this request to retrieve the username and email address of the owner https://graph.microsoft.com/v1.0/users/<UserID>

My 3×5 cents to this clever trick:

  • Not only “collaborator” link helps, but also “respond to”
  • If the form is owned by the group – the link would be similar but with “…/group/group_id/…” instead of “…/user/user_id/…”
  • If you have a SSO in your org and cannot find this call under network – try different browser or incognito mode or logging out before the call – as what you need appears at early stages – even before authentication

How do I know – is it a person-owned or group-owned form

Let say you got a claim that “we were able to work with the form, and now it is gone”, and the only you have is the “collaborators” link to the form – so you can edit form, view responses etc. but nobody knows who created that form… So how to determine who owns the form – person or group and what person/group.

The form is owned by a person if

  • form id is 80 characters length
  • on “Export to Excel” button – it saves/downloads excel file to the file system
  • audit log contains ExportForm (Exported form) event – as clicking “Export to Excel” button generates ExportForm (Exported form) event
  • network trace contains “https://forms.office.com/formapi/api/<tenantId>/users/<UserId>/…”

The form is owned by a group if

  • form id is 88 characters length
  • on “Export to Excel” button – it saves excel file to the team SharePoint site and opens it in browser
  • clicking “Export to Excel” button generates ConnectToExcelWorkbook – “Connected To Excel Workbook” event in the audit log
  • network trace contains “https://forms.office.com/formapi/api/<tenantId>/groups/<GroupId>/…”

References

See also:
Form blocked due to potential phishing

Manage Microsoft 365 groups membership with PowerShell and Graph API

As SharePoint or Teams admin you can manage Microsoft 365 groups (create, update, delete, manage membership etc.) having your admin role activated. I use Azure registered app with “Group.ReadWrite.All” Microsoft Graph API delegated permission and Microsoft.Graph PowerShell module.

When a user was not a group member or group owner – and is added to the group members – user gets notification “You’ve joined the <Group Name> group” via e-mail that comes from a group e-mail address.

When a user is added to the group owners (or elevated to group owner if user was a group member) – user does not get notification.

When a user was a group owner and now is added to the group members – user does not get notification.

All the actions are logged into Microsoft 365 audit log under your personal Id.

Script samples:

# This script is just a sample to demonstrate basic technique on deletion m365 groups with PowerShell and MS Graph
# please do not run this script as is, but update it upon your needs

# authentication with personal Id
#  app must have as minimum "Group.ReadWrite.All" Microsoft Graph API delegated permission
#  user must have SharePoint admin (or Teams admin) roles activated
Connect-MgGraph -ClientId $clientid -TenantId $tenantId 
Get-MgContext | Select-Object Scopes -ExpandProperty Scopes

# sample data
$groups = @()
$groups += [PSCustomObject]@{GroupId = '443d22ae-683a-4fe4-8875-7bd78227a026' }
$groups += [PSCustomObject]@{GroupId = 'e5805388-c18c-48c0-b42d-6223cf8f3d82' }

# Get Groups
foreach ($group in $groups) {
    Get-MgGroup -GroupId $group.GroupId
}

# add members to the group
$groupId = '443d22ae-683a-4fe4-8875-7bd78227a026'
$userId = 'df74e0d3-d78c-495b-b47a-549437d93cf7' # Adele
New-MgGroupMember -GroupId $groupId -DirectoryObjectId $userId

# add Owner to the group
$groupId = '443d22ae-683a-4fe4-8875-7bd78227a026'
$userId = 'eacd52fb-5ae0-45ec-9d17-5ded9a0b9756' # Megan
New-MgGroupOwner -GroupId $groupId -DirectoryObjectId $userId

# Delete group
# add Owner to the group
$groupId = '443d22ae-683a-4fe4-8875-7bd78227a026'
Remove-MgGroup -GroupId $groupId

https://github.com/VladilenK/m365-PowerShell/tree/main/KBA/Ownerless-Groups