Category Archives: Office 365

Automating Sites.Selected permissions provisioning

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.

How to Find Old Content on Your OneDrive or SharePoint Site

Any organization has it’s own data lifecycle policy and for information stored in SharePoint there must be a retention period… let say 5 years. So your files modified more than 5 years ago are going to be deleted and you will not even notice it.

What if you want to know – which documents in your OneDrive or SharePoint site is older than some specific date – here are some options to find out.

Search with query parameters (GUI)

At any level of your site hierarchy – root level, library, folder etc. – you can refine your search results with, e.g. “LastModifiedTimeForRetention<2021-01-01” if you want to get all documents older than January 1st 2021.

In the screenshot below I use newer dates, but you got the idea:

If you need only Microsoft Word documents older than some specific date, you might use query: “*.docx LastModifiedTimeForRetention<2021-01-01”

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”

Microsoft Forms Troubleshooting

So far some findings I came up with during last Microsoft forms troubleshooting… I’ll keep them here just as a reminder for myself or it might help you to troubleshoot Microsoft forms.

You know, a user can create a 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.

Form owner can move the form to a group. 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, i.e. old links will stop working
    group-owned form id seemed to me little longer – 88 characters vs 80 for individual-owned forms and has no dashes.

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

If the form is owned by group – the link would 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.

Deleting user who owns forms

When a form owner account is deleted from AAD

Deleting a group that owns forms

Audit log events

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

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

Let say you got a “collaborators” link to the form – so you can edit form, view responses etc. But how to determine who owns the form – person or group.

It is a form owned by person if

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

It is a form owned by group

  • 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
  • “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>/…”

Ownerless groups and dependent channels

Here is the scenario:

There is a team (including SharePoint site) under Microsoft Teams. There are multiple channels under this team with types:
– standard channel
– shared channel
– private channel

A single group owner (team owner) leaves company and the team (group) becomes ownerless. Question: what will happen with private and shared channels?

A group (team) gets a new owner. Question: what will happen with private and shared channels?

“Microsoft Teams – Teams And Channels Service made you an owner of a channel”

TBP

Archiving SharePoint Sites

WIP

What is archiving SharePoint sites and why we’d need it?

Disclaimer: Archival that was announced at Microsoft Inspire 2023 (Introducing Microsoft 365 Backup and Microsoft 365 Archive) is not what we are discussing here.

Scenario

You are in the process of cleaning-up large Microsoft 365 environment. You need to delete SharePoint sites (e.g. due to inactivity) but you cannot get confirmation from site owners (e.g. sites or groups are ownerless).

Deleted sites could be restored within 93 days of deletion if somebody rise a hand, but there is still a risk of possible loosing of important information, e.g. in case site is needed one a year. So you need to do clean-up but at the same time you want to decrease risks of loosing information.

So, you might want to do something with sites to engage users to volunteer to be site owner if they want to keep this site – e.g. prevents using the site the regular way and let users know that the site will be deleted etc., but do not actually delete site until it will be fully clear that site is not needed for anyone and can be safely deleted.

Let us call it “Staging” period. Depending on your org culture/rules/licensing etc. it might be 6 months, or 1 year or 5 years or more.

Approach options

generally, the options are (random order):

  • Set site to Read-Only mode
  • Set site to No-Access mode
  • Convert group from Public to Private
  • Remove access to the site (remove users from group)
  • Rename the site
  • Put a banner on a top bar with a message
  • Message to Teams or Yammer chat
  • Send e-mail to site members
  • Implement a Microsoft 365 ownerless groups policy

You might choose to set sites to read-only mode or even no-access mode. If so – users that are still need this site are loosing ability to work with site, but site is not deleted. Consider archiving as kind of scream-test phase before actual sites deletion.

If a user who needs this site would scream (rise a ticket to restore site) – you can trigger processes of
a) finding new owner for the site
b) excluding the site from clean-up process
c) actual restoring site to normal mode

There are some options to setup a site to Read-Only or NoAccess mode. Here is the PowerShell command:


$siteurl = "https://contoso.sharepoint.com/teams/Team-SO-B"
Get-PnPTenantSite -Identity $siteurl   | ft -a Url, LockState
Set-PnPTenantSite -Identity $siteurl -LockState ReadOnly
Get-PnPTenantSite -Identity $siteurl   | ft -a Url, LockState
Set-PnPTenantSite -Identity $siteurl -LockState NoAccess
Get-PnPTenantSite -Identity $siteurl   | ft -a Url, LockState
Set-PnPTenantSite -Identity $siteurl -LockState Unlock

The problem is what if the site is teams-connected or yammer-connected or just group-based. Here are some test results:

Services SharePoint site is connected to/Site StateRead-OnlyNoAccess
Outlook onlyN/AN/A
SharePoint and OutlookOutlook emails: OK
Outlook files: read-only experience; No options to upload or create document; Documents are open in read-only mode. “The file couldn`t be saved to group” error message when trying to save file to a group library.
Outlook emails: OK
Outlook files: empty screen; No error messages; Documents are not visible; “The file couldn`t be saved to group” error message when trying to save file to a group library.
SharePoint and Yammer
SharePoint, Teams and OutlookTeams chats: OK
Teams files: documents are open as read-only; No options to upload or create a new document
SharePoint: “This site is read-only at the administrator’s request.”
Teams chats: OK
Teams files: “403 FORBIDDEN” error message
SharePoint: “
This site can’t be reached
The webpage at https://contoso.sharepoint.com/teams/Team-STO-B might be temporarily down or it may have moved permanently to a new web address.
ERR_INVALID_RESPONSE”

So you can see – behavior is inconsistent – users can still chat in Teams and Yammer and consume SharePoint content (in case the site in read-only) or get error messages or not very meaningful results (in case the site is in NoAccess mode) – so it would be not clear for users that the site is gong to be decommissioned.

Ownerless groups in Microsoft 365

I have multiple publications on the subject – how to manage ownerless groups in Microsoft 365:

And 9 videos :

Here is the Introduction video and content covered in next videos:

Ownerless group policy configuration failed

If you are seeing “Ownerless group policy configuration failed” and “Please try again.” error message:

there might be some different reasons:

  1. Microsoft said it is (was) a know problem – it happens sometimes (timeout?), if you configured the policy properly and have enough permissions.
    So just go back one step and try again – all should be good.
  2. Sometimes “Ownerless group policy configuration failed. Failure in configuring ownerless groups policy” is a permissions issue
    SharePoint admin, Teams admin: cannot configure Ownerless Groups Policy
    Global admin: yes, can configure Ownerless Microsoft 365 Groups Policy.
    What is the minimum role required?
    According to a recent update of the Microsoft’s article – “A Global administrator can create a policy…”. In my experience – groups admin can also configure the policy

Note: Groups admin when configuring the policy can see warning message “You don’t have permissions to save changes”.
No worries 🙂 => You will be able to save changes 🙂

Video tutorial on the policy configuration (at around 5:00 you can see this error message):

MS Graph usage reports: Site vs Team vs Group activity

Microsoft Graph provides very useful reports via MS graph reports API:

  • getOffice365GroupsActivityDetail – details about Microsoft 365 groups and activity
  • getSharePointSiteUsageDetail – details about SharePoint sites and usage
  • getTeamsTeamActivityDetail – details about Microsoft Teams and activity by teams

Also we know, that Teams sites are group-based, and you can have private and shared channels under Teams – but these sites are not actually group-based and there are group-based SharePoint sites with no Teams behind.

And activities might be different – update document or just visit home page, provide permissions and update channel properties etc.

So the question is what kind of activity at what level is recorded at which report?

TBP