Tag Archives: PnP.PowerShell

This PowerShell script pulls all tenant sites and all sites owners. The script require app authentication with Sites.FullControl.All and Directory.Read.All permissions.
PnP.PowerShell for PowerShell 7 is used.

It generates two reports

Owners report: one user per line, include: Site Url, Title, Owner e-mail, name and type
Sites report: one site per line, include: Site Url, Title, list of owners e-mails

Here is the script:


$connAdmin = Connect-PnPOnline -ReturnConnection -Tenant $tenantId  -Url $adminUrl -ClientId $clientid -Thumbprint $certThumbprint
$allTenantSites = Get-PnPTenantSite -Connection $connAdmin | Sort-Object Url
$allTenantSites.count

$sitesReport = @()
$ownersReport = @()
foreach ($tenantSite in $allTenantSites) {
    Write-Host $tenantSite.Url
    $connSite = Connect-PnPOnline -ReturnConnection -Tenant $tenantId  -Url $tenantSite.Url -ClientId $clientid -Thumbprint $certThumbprint
    $site = Get-PnPSite -Connection $connSite -Includes RootWeb, GroupId, Owner
    $siteOwnerEmail = ''
    $siteOwnersReport = @()
    if ($site.GroupId.Guid -eq '00000000-0000-0000-0000-000000000000') {
        $siteAdmins = Get-PnPSiteCollectionAdmin -Connection $connSite | ? { $_.PrincipalType -eq 'User' }
        $ownerType = 'Site Collection Administrator'
        $isGroupSite = $false
    }
    else {
        $siteAdmins = Get-PnPAzureADGroupOwner -Connection $connAdmin -Identity $site.GroupId.Guid
        $ownerType = 'Group Owner'
        $isGroupSite = $true
    }
    foreach ($siteAdmin in $siteAdmins) {
        if (!$siteAdmin.UserPrincipalName) {
            Get-PnPProperty -Connection $connAdmin -ClientObject $siteAdmin -Property UserPrincipalName | Out-Null
        }
        $aadUser = Get-PnPAzureADUser -Connection $connAdmin -Identity $siteAdmin.UserPrincipalName
        if ($aadUser.AccountEnabled) {
            $siteOwnerEmail += $aadUser.Mail + '; '
        }
        $siteOwnersReport += [PSCustomObject]@{
            SiteUrl     = $site.Url
            SiteTitle   = $site.RootWeb.Title
            IsGroupSite = $isGroupSite
            OwnerEmail  = $aadUser.Mail
            OwnerName   = $aadUser.DisplayName
            OwnerType   = $ownerType
            Enabled     = $aadUser.AccountEnabled
        }
    }
    $ownersReport += $siteOwnersReport
    $sitesReport += [PSCustomObject]@{
        SiteUrl     = $site.Url
        SiteTitle   = $site.RootWeb.Title
        IsGroupSite = $isGroupSite
        OwnerEmail  = $siteOwnerEmail
    }
}

$ownersReport.count
$sitesReport.count

Source code: https://github.com/VladilenK/Manage-m365-with-PowerShell

Update Large Number of SharePoint Sites with PowerShell Parallel

Leave a reply

WIP

Here I’m trying to figure out – how much PowerShell Parallel option is beneficial and how to avoid throttling…

Let us test, how long would it take to create a SharePoint site, if we use regular (sequential) loop or parallelism (I’m creation a sample set of 50 SharePoint Sites in a row):

	Regular (Sequential) seconds per site	Parallel, 100 sites in batch seconds per site	Parallel, 500 sites in batch seconds per site
Regular (Sequential)	3.0
Parallel, ThrottleLimit = 2		1.60	0.91
Parallel, ThrottleLimit = 5		0.69
Parallel, ThrottleLimit = 10		0.2 – 0.3
Parallel, ThrottleLimit = 20		0.17

Interesting, but I did not get even one (throttling or any other) error during creation 500 sites.

Get sites details

Now let us test, how long it takes to get sites details with Get-PnPTenantSite (I use a sample set of 500 sites):

Test type	Regular (Sequential), seconds per site	Parallel sample = 100 sites, seconds per site	Parallel sample = 200 sites, seconds per site	Parallel sample = 500 sites, seconds per site
Regular (Sequential)	0.65
Parallel, ThrottleLimit = 2		0.40	0.33	0.31
Parallel, ThrottleLimit = 5		0.17	0.14	0.36 (errors)
Parallel, ThrottleLimit = 10		0.11 (errors)	0.11 (errors)	0.34 (errors)
Parallel, ThrottleLimit = 20		0.12 errors+	0.07 errors+	0.52 (errors)

(errors) means there were small number of errors during test… e.g.

Token – SharePoint API compatibility matrix

Leave a reply

If I get token with (Graph, MSAL, PnP) and use this token for (Graph API, SharePoint CSOM API, SharePoint REST API) matrix.

An App used in this tests has Sites.FullControl.All MS Graph API and SharePoint API permissions, as well as FullControl ACS based permissions to SharePoint (AppInv.aspx).

Token/API	MS Graph /v1.0/sites	SharePoint CSOM PnP.PowerShell Get-PnPSite Get-PnPTenantSite	SharePoint REST API PnP.PowerShell Invoke-PnPSPRestMethod Invoke-RestMethod
MS Graph /oauth2/v2.0/token secret	OK	(401) Unauthorized	AudienceUriValidationFailedException
MSAL.PS Get-MsalToken with secret	OK	(401) Unauthorized	AudienceUriValidationFailedException
MSAL.PS Get-MsalToken with certificate	OK	(401) Unauthorized	AudienceUriValidationFailedException
PnP.PowerShell Get-PnPAccessToken with Certificate	OK	OK OK	OK AudienceUriValidationFailedException
PnP.PowerShell Get-PnPGraphAccessToken with Certificate	OK	OK OK	OK AudienceUriValidationFailedException
PnP.PowerShell Get-PnPAppAuthAccessToken with Certificate or secret	InvalidAuthenticationToken	OK OK	OK OK
PnP.PowerShell Request-PnPAccessToken with Certificate	InvalidAuthenticationToken	OK OK	OK AudienceUriValidationFailedException
PnP.PowerShell Request-PnPAccessToken with Secret	InvalidAuthenticationToken	OK OK	OK OK

AudienceUriValidationFailedException = Exception of type ‘Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException’ was thrown

Testing Sites.Selected SharePoint and MS Graph API

1 Reply

Sites.Selected MS Graph API permissions were introduced by Microsoft in March 2021. One year later, in 2022 they added SharePoint Sites.Selected API permissions.

Azure registered app with SharePoint and MS Graph API Sites.Selected permissions

Why is this so important? Because MS Graph API for SharePoint is still limited and cannot cover all possible needs. I’d estimate: 90% of applications use SharePoint CSOM, so developers have to use AppInv.aspx to provide permissions for their applications to SharePoint API.

But from this moment – having SharePoint API permissions in MS Graph – in theory – we can fully rely on permissions provided in Azure and – in theory – this should allow us disable SharePoint-Apps only principal:

Set-SPOTenant -DisableCustomAppAuthentication $true

My math professor taught me: “before trying to find a solution – ensure the solution exists.” So let us test:

Are we really able to work with a specific SharePoint site using MS Graph and SharePoint API Sites.Selected permissions provided via Microsoft Azure?

What will happen with our new/legacy applications if we disable SharePoint app-only SPNs (DisableCustomAppAuthentication)?

I’m getting controversial test results… maybe PnP.PowerShell 1.10 is not fully support SharePoint Sites.Selected API.

Tech Wizard (Sukhija Vikas) on March 20, 2022 in the article “SharePoint and Graph API APP only permissions for Selected Sites” suggests using pre-release (AllowPrerelease).

So please ignore the following for a while.

Meantime I’ll test providing SharePoint Sites.Selected API permissions via Graph API call.

(wip) Test set #1: Certificate vs Secret

DisableCustomAppAuthentication: $false (SP-app-only spns are enabled).
All applications have “write” access provided to a specific site only.
Connecting with Connect-PnPOnline and then test access with Get-PnPSite

App / Get-PnPSite	Secret	Certificate
ACS based (Azure+AppInv)	OK	The remote server returned an error: (401) Unauthorized.
MS Graph API Sites.Selected	The remote server returned an error: (403) Forbidden.	The remote server returned an error: (401) Unauthorized.
SharePoint API Sites.Selected	OK	OK
MS Graph API + SharePoint API Sites.Selected	Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))	OK
App with no permissions	The remote server returned an error: (403) Forbidden	The remote server returned an error: (401) Unauthorized

(wip) Test set #2: Sites.Selected SharePoint vs MS Graph (secret)

DisableCustomAppAuthentication = $false
(SP-app-only spns are enabled).
All applications have “write” access provided to a specific site only.
Using Client Secret (not a certificate)
Using PnP.PowerShell

Action/Via	SharePoint + MS Graph Sites.Selected “secret”	SharePoint Sites.Selected “secret”	MS Graph Sites.Selected “secret”
Connect-PnPOnline	WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.	WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.	WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
Get-PnPSite	OK	OK	The remote server returned an error: (403) Forbidden.
Get-PnPList	OK	OK
Get-PnPListItem	OK	OK
Set-PnPSite		Attempted to perform an unauthorized operation.
Set-PnPList		Attempted to perform an unauthorized operation.
Set-PnPListItem	OK	OK
New-PnPList		Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Add-PnPListItem		OK

(wip) Test set #3: Read vs Write vs FullControl

DisableCustomAppAuthentication = $false
(SP-app-only spns are enabled).
All applications have Sites.Selected SharePoint and MS Graph API permissions.
Using Client Secret (not a certificate)
Using PnP.PowerShell

	Read	Write	FullControl
Connect-PnPOnline	WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.	WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.	WARNING: Connecting with Client Secret uses legacy authentication and provides limited functionality. We can for instance not execute requests towards the Microsoft Graph, which limits cmdlets related to Microsoft Teams, Microsoft Planner, Microsoft Flow and Microsoft 365 Groups.
Get-PnPSite	Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Get-PnPList	Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
Get-PnPListItem
Set-PnPSite
Set-PnPList
Set-PnPListItem
New-PnPList
Add-PnPListItem

(wip) Test set #5: Certificate vs Secret

C#, SharePoint CSOM, PnP.Framework

Findings

PnP.PowerShell Get-, Grant-, Set- and Revoke-PnPAzureADAppSitePermission cmdlets require Azure App with MS Graph Sites.FullControl.All app permissions (otherwise it says “Access denied”) and authentication via certificate (otherwise it says “This cmdlet does not work with a ACS based connection towards SharePoint.”)

The same actions – managing permissions for the client app to the specific site collections – could be done via Microsoft Graph Sites Permissions API using just secret-based authentication.

If an azure app does not have Sites.Selected API permissions configured – “Grant-PnPAzureADAppSitePermission” works as expected – no error messages – the output is normal – as if Sites.Selected API permissions were configured in the app. The same for Get-, -Set and Revoke-. Permissions provided for the app to the site are not effective though: Connect-PnPOnline works well, but all other commands – starting from Get-PnPSite – returns “The remote server returned an error: (403) Forbidden.”

If an app have no permissions to SharePoint – “Connect-PnPOnline” works ok, but “Get-PnPSite” return an error: “The remote server returned an error: (403) Forbidden.”

Set-PnPAzureADAppSitePermission gives an error message “code”:”generalException”,”message”:”General exception while processing”
if the site is not specified.

AppInv is not working?

Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

References

Testing environment

Microsoft 365 E5 Dev environment
PowerShell 7.2.2
PnP.PowerShell 1.10
“write” permissions to the specific sites for client apps were assigned via PnP.PowerShell

Connecting to SharePoint Online programmatically: Secret vs Certificate

1 Reply

Update: Sites.Selected API MS Graph permissions was introduced by Microsoft in 2021. It was a good move towards site-level development, but still developers were limited with only what MS Graph API provides for SharePoint dev.
So devs had to use AppInv.aspx at site level to provide ACS permissions to their apps to be able to use SharePoint CSOM and REST APIs.
Recently Microsoft introduced Sites.Selected SharePoint API permissions for registered Azure Apps! So now devs should be fully happy without ACS-based permissions.

Scenario

You have an application that needs access to Microsoft 365 SharePoint Online site/list/documents. Application is running without interaction with users – e.g. unattended, as daemon job.

There are two options you can authenticate to Microsoft 365 – with the secret or with the certificate. Authenticating with certificate is considered more secure.

Questions

What happens if SharePoint-Apps only principal is disabled
(i.e. ‘set-spotenant -DisableCustomAppAuthentication $true’ )?
Why I’m getting 401 error when authenticating to SPO?
Why I’m getting 403 error when authenticating to SPO with secret?
What permissions to I need to work with SPO?

Findings

Note: we will use PowerShell 7.2 and PnP.PowerShell 1.9 to illustrate it.

Disabled SharePoint-Apps only principal

If SharePoint-Apps only principal is disabled in your tenant
(i.e. ‘Get-PnPTenant | select DisableCustomAppAuthentication’ returns $true ), then the only way you work with SPO from code is:

an App registered in Azure
API permissions provided via Azure (MS Graph, SharePoint)
Certificate is used

In all other cases (even your Connect-PnPOnline command complete successfully) – you will be getting error 401 (unauthorized) when trying Get-PnPTenant or Get-PnPTenantSite or Get-PnPSite

Enabled SharePoint-Apps only principal

If SharePoint-Apps only principals are enabled in your tenant
(i.e. ‘Get-PnPTenant | select DisableCustomAppAuthentication’ returns $false ), then you have two options to work with SPO from code:

Azure App with a secret (Client Id + Client Secret) and permissions to SharePoint provided via SharePoint ( AppInv.aspx )
Azure App with a certificate (Client Id + Certificate) and permissions provided via Azure (Microsoft Graph and/or SharePoint)

Error 401 while accessing SharePoint Online with PnP

(Get-PnPTenant, Get-PnPTenantSite)

PowerShell Script to Fetch All Alerts from SharePoint Online Site

Leave a reply

PowerShell Script to get All Alerts of all Users from a specific SharePoint Online Site Collection, including subsites:

https://github.com/VladilenK/PowerShell/blob/main/reports/Site/Fetch-All-Alerts-from-SPO-Site.ps1

https://raw.githubusercontent.com/VladilenK/PowerShell/main/reports/Site/Fetch-All-Alerts-from-SPO-Site.ps1

based on Salaudeen Rajack:
“SharePoint Online: Get All Alerts from a Site Collection using PowerShell“

Connect to SharePoint Online and MS Graph Interactively with Client App and MSAL token

Leave a reply

Scenario

You have got a Microsoft 365 subscription with SharePoint Online. You use PowerShell, PnP.PowerShell module and MS Graph API to work with SharePoint under current user’s credential. So you need to authenticate to SharePoint Online via Connect-PnPOnline and to Microsoft Graph API interactively on behalf of a user.

Problem

Unfortunately, both “Connect-PnPOnline -Interactive -Url <siteUrl>” or “Connect-PnPOnline -UseWebLogin -Url <siteUrl>” might fail with something like “Need admin approval”, “App needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.” or similar

Solution

register an Azure App. Choose “single tenant”
configure authentication blade:
– add platform – “Mobile and Desktop app”
– select “https://login.microsoftonline.com/common/oauth2/nativeclient”
– add custom Redirect URI: “http://localhost”
configure API permissions blade:
– add delegated permissions you need (refer to specific API you’ll use)
e.g. Microsoft Graph Sites.FullControl.All and SharePoint AllSites.FullControl
use the following code samples

PnP.PowerShell

$siteUrl = "https://contoso.sharepoint.com/teams/myTeamsSite"
$appId = "" # Client Id
Connect-PnPOnline -ClientId $appId -Url $siteUrl -Interactive
Get-PnPSite

A pop-up window will appear to authenticate interactively. If you are already authenticated with another credentials (or single-sigh-on) – an interactive window might pop up and disappear – that prevents you enter your other id.
To ensure Connect-PnPOnline prompts you for your credentials – use ” -ForceAuthentication” option.

If you are a SharePoint tenant admin – you can connect to a tenant with:

$orgName = "yourTenantPrefix" 
$adminUrl = "https://$orgName-admin.sharepoint.com" 
$appId = "" # Client Id 
$connection = Connect-PnPOnline -ClientId $appId -Url $adminUrl -Interactive -ReturnConnection # -ForceAuthentication 
$connection

Microsoft Graph API

Use MSAL.PS module to get an msal token then use token in Microsoft graph-based requests:

$tenantId = ""
$clientid = ""
$url = ""
$token = Get-MsalToken -ClientId $clientid -TenantId $tenantId -Interactive

By default token expires in ~ 1 hour. But you can refresh it silently.
This helps you in long-running PowerShell scripts that takes hours to complete.
So you can include something like this in the loop:

if ($token.ExpiresOn.LocalDateTime -lt $(get-date).AddMinutes(10)) {    
  $token = Get-MsalToken -ClientId $clientid -TenantId $tenantId -ForceRefresh -Silent    
  Write-Host "Token will expire on:" $token.ExpiresOn.LocalDateTime
}

Application permissions

Somehow using Connect-PnPOnline with AccessToken option did not work if the token was acquired with MSAL.PS interactively. But it did work when you get msal.ps token unattended (using App credentials). So…

If you can get an Application (non Delegated) permissions to your azure-registerd-app,
you can use msal token to connect to site with PnP

=========================

NB: For delegated permissions, the effective permissions of your app are the intersection of the delegated permissions the app has been granted (via consent) and the privileges of the currently signed-in user. Your app can never have more privileges than the signed-in user.

Read access: Read items that were created by the user via PowerShell

Leave a reply

Scenario:

You have a list in SharePoint Online. You want list items be visible to specific users only.
You want to leverage Item-Level Permissions under List Advanced settings: “Read access: Read items that were created by the user”. But the problem is it was not users who created items. E.g. the list was imported from excel file or created programmatically or migrated.

Solution:

PnP.PowerShell helps. Using “Set-PnPListItem”, you can re-write “Author” field in the list item.

Set-PnPListItem -List "Test" -Identity 1 -Values @{"Author"="testuser@domain.com"}

And, of course, use Item-Level Permissions under List Advanced settings: “Read access: Read items that were created by the user”:

Add users to “Site Visitors” group for read-only access:

… more TBP

Connect-PnPOnline with a certificate stored in Azure Key Vault

Leave a reply

Scenario

You run some PnP PowerShell code unattended e.g. daemon/service app, background job – under application permissions – with no user interaction.
Your app needs to connect to SharePoint and/or Microsoft Graph API. Your organization require authentication with a certificate (no secrets). You want certificate stored securely in Azure Key Vault.

Solution (Step-by-step process)

Obtain a certificate (create a self-signed or request trusted)
In Azure where you have Microsoft 365 SharePoint tenant
1. Create a new Registered App in Azure; save App (client) id, Directory (Tenant) Id
2. Configure App: add MS Graph and SharePoint API application (not delegated) permissions
3. Upload the certificate to the app under “Certificates & secrets”
In Azure where you have paid subscription (could be same or different)
1. Create an Azure Key Vault
2. Upload certificate to the Key Vault manually (with GUI)
While you develop/debug your custom daemon application at your local machine
1. Provide permissions to the Key Vault via Access Control and Access Policies to your personal account
2. Connect to Azure (the one where your Key Vault is) running Connect-AzAccount
  – so your app can get a Certificate to authenticate to SharePoint Online
For your application deployed to Azure (e.g. Azure Function App )
1. Turn On managed identity (Your Function App -> Identity -> Status:On) and Save; notice an Object (Principal) Id just created
2. Provide for your managed identity principal Id permissions to the Key Vault via Key Vault Access Policies, so when your daemon app is running in the cloud – it could go to the key Vault and retrieve Certificate

Here is the sample PowerShell code to get certificate from Azure Key Vault and Connect to SharePoint with PnP (Connect-PnPOnline):

# ensure you use PowerShell 7
$PSVersionTable

# connect to your Azure subscription
Connect-AzAccount -Subscription "<subscription id>" -Tenant "<tenant id>"
Get-AzSubscription | fl
Get-AzContext

# Specify Key Vault Name and Certificate Name
$VaultName = "<azure key vault name>"
$certName = "certificate name as it stored in key vault"

# Get certificate stored in KeyVault (Yes, get it as SECRET)
$secret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $certName
$secretValueText = ($secret.SecretValue | ConvertFrom-SecureString -AsPlainText )

# connect to PnP
$tenant = "contoso.onmicrosoft.com" # or tenant Id
$siteUrl = "https://contoso.sharepoint.com"
$clientID = "<App (client) Id>" # Azure Registered App with the same certificate and API permissions configured
Connect-PnPOnline -Url $siteUrl -ClientId $clientID -Tenant $tenant -CertificateBase64Encoded $secretValueText

Get-PnPSite

The same PowerShell code in GitHub: Connect-PnPOnline-with-certificate.ps1

References:

Vladilen software engineer

Microsoft 365, SharePoint, Teams, PowerShell, Azure

Tag Archives: PnP.PowerShell

PowerShell scripts for Microsoft 365 SharePoint

Get all SharePoint and Teams sites owners report with PowerShell

Update Large Number of SharePoint Sites with PowerShell Parallel

Get sites details

Token – SharePoint API compatibility matrix

Testing Sites.Selected SharePoint and MS Graph API

(wip) Test set #1: Certificate vs Secret

(wip) Test set #2: Sites.Selected SharePoint vs MS Graph (secret)

(wip) Test set #3: Read vs Write vs FullControl

(wip) Test set #5: Certificate vs Secret

Findings

References

Testing environment

Connecting to SharePoint Online programmatically: Secret vs Certificate

Scenario

Questions

Findings

Disabled SharePoint-Apps only principal

Enabled SharePoint-Apps only principal

Error 401 while accessing SharePoint Online with PnP

PowerShell Script to Fetch All Alerts from SharePoint Online Site

Connect to SharePoint Online and MS Graph Interactively with Client App and MSAL token

Scenario

Problem

Solution

PnP.PowerShell

Microsoft Graph API

Application permissions

Connect-PnPOnline with a certificate stored in Azure Key Vault

Scenario

Solution (Step-by-step process)