Category Archives: SPO

Find sites shared with Everyone in SPO

There is a know problem in SharePoint – it’s complicated permissions system. As a result, many sites are overshared (overexposed) and site owners/administrators even do not know – who has access to their sites…

The most concern is sites shared with “Everyone”, “Everyone except external users” and “All users”. How do we find sites shared with “Everyone” in a large Microsoft 365 tenant?

Approach #1 (Brute force)

We can get full permissions report at tenant level (or permissions provided to “Everyone”). There are 3-rd party tools (e.g. ShareGate, SysKit, AvePoint, Metalogix etc.), or you can run PowerShell script…

Sounds easy? Well, if you have 1000 sites – probably yes. But if your environment 10K+ sites – it will take forever. Permission report might run hours for an average site with site/subsite, list/library and list item details level.

We need report detailed up to every item level deep, as even one file with sensitive info shared with everyone can cause security issue.

So, if this approach is not working – what’s working?

Approach #2 (Search)

Clever idea: why do we have to iterate through all the tenant documents/items if all the content is already crawled by search? Can we just use search to get files shared with Everyone? Sure!

Check this and this articles. Can we get results programmatically (e.g. with PowerShell)? Can we use Microsoft Graph search API? Sure.
Check this article “How to search against SharePoint Online Content with Microsoft Graph search API with PowerShell”.

But! We have two problems here.

Search Problem #1. The problem is the same as in “brute force”. Search returns so many results – it’ll take weeks to get all of them. (There are team sites “legally” shared with everyone, public Office 365 group based sites, communication sites… ).

Search Problem #2. Even whet we get all search results – we do not know – what is the exact Url of the resource shared with all users. So we will need to build list of sites based on the search results – ant then still need to run permissions report against these sites.

Approach # 3 Hybrid

The idea: why do we need to get all search result if even one result from the site would be enough to add the site to the list of sites require permission report.

So, consider (imho, the best) approach.

  1. You get list of sites in tenant. Here you can refine the list excluding, e.g. sites connected to public teams or known communication sites… Finally wou’ll have a list of sites you want to check – if there are resources shared with “Everyone…”
  2. You run search against each site in the loop (e.g. consider KQL option “Site: https://yourTenant,SharePoint.com/sites/YourSite”. Once even one result fount for the site – add the site to the “Open Sites” list

With this approach you will get list of sites shared with “Everyone…” in a coule of minutes.

The Next step would be “How to let site owners know what are resources shared with Everyone… on their sites”.

References

https://support.microsoft.com/en-us/office/how-to-determine-resources-to-which-all-external-users-have-access-c3c8642b-cfb3-46b7-9f5a-ce27a6cb8ea1

Calling Microsoft Graph interactively from PowerShell code

Scenario

You are a developer or power user in a company with Microsoft 365.

You need to authenticate to Microsoft Graph and then consume some Microsoft Graph resources programmatically with PowerShell. E.g. add/remove documents or list items, search for sites or documents content etc. – whatever available with Graph API.

You need to login to Azure AD – so your code runs under your personal credentials.

You do not have tenant admin permissions or any tenant-level admin permissions (SharePoint, Teams, Exchange etc. ). But you can register an Azure App and request tenant admin consent.

Solution

  • register an azure app
  • under authentication blade – add platform – “Mobile and Desktop app”
    select …/nativeclient Url
    add “http://localhost”
  • under API permissions blade – add delegated permissions you need
    (refer to specific API you’ll use)
  • install MSAL.PS PowerShell module
  • use the following code to get graph access token and call graph API
$connectionDetails = @{
    'TenantId'    = ''
    'ClientId'    = ''
    'Interactive' = $true
}

$token = Get-MsalToken @connectionDetails
$Headers = @{
    'Authorization' = "bearer $($token.AccessToken)"
}
Invoke-RestMethod -Uri 'https://graph.microsoft.com/v1.0/me' -Headers $Headers

You can find the code sample here: https://github.com/VladilenK/

What did not work

Az PowerShell module did not work for me:

Connect-AzAccount -Tenant ""
$azAccessToken = Get-AzAccessToken -Resource "https://graph.microsoft.com" 

$Headers = @{
  'Authorization' = "$($azAccessToken.Type) $($azAccessToken.Token)"
}

Invoke-RestMethod -Uri 'https://graph.microsoft.com/v1.0/me' -Headers $Headers

As I understand we need somehow let Azure know API permissions we want (e.g. via app registerd)…

PnP did not work for me:

$url = "https://orgname.sharepoint.com"
Connect-PnPOnline -ClientId "" -Url $url -Interactive 
$pnpToken = Get-PnPGraphAccessToken 
$Headers = @{
    'Authorization' = "bearer $($pnpToken)"
}
Invoke-RestMethod -Uri 'https://graph.microsoft.com/v1.0/me' -Headers $Headers

the error message was (maybe I missed something – please let me know):

“code”: “InvalidAuthenticationToken”, “message”: “Access token validation failure. Invalid audience.”

Access to a specific SPO Site Programmatically with Sites.Selected

Scenario

You are a software developer. Your company uses Microsoft Office 365 (SharePoint, Teams etc.). The need is to work with a specific site collection programmatically (from code – Python, C#, Java, PowerShell, JavaScript etc.) – e.g. upload/download documents, update list items, search etc. The code must run without user interactions (unattended, aka daemon app).

The solution is based on a new Graph API feature – Sites.Selected and a classic SP-Only app.

Solution

  1. Register an Azure App
  2. API permissions: add -> Microsoft Graph -> Applications Permissions -> “sites.selected
  3. Ask SharePoint/Tenant admin run PowerShell code (e.g. this one) to assign proper permissions for your azure app to a specific site collection (have sc owner consent as well)
  4. Site Collection Owner/Admin – use
    https://contoso.sharepoint.com/teams/YourSite/_layouts/15/appinv.aspx
    to add SharePoint API permissions to your app.
    Consider minimal permissions (e.g. as per Sumit)

Problems Solved

  • you get access to one and only one site collection (“least privilege” principal)
  • you get both – SharePoint API and Microsoft Graph API permissions to SharePoint
  • you can use app secret or certificate to authenticate – depending on what is your security require

SharePoint sites shared with Everyone and Microsoft Delve issue

There is a known problem with Microsoft Delve.

We know SharePoint site permissions are not easy. You can break permissions inheritance at any level – subsite, library, list, folder, list item or specific document. Anybody with full permissions can do that. The worst thing is there is was (*1) no one place where site owner could get full permissions report to the site. We must have used third-party tools or PowerShell to have all permissions in one document.

So no wonder SharePoint sites were heavily over-exposed. Especially when a site owner tired with complexity of SharePoint permissions system decided to share resource with “Everyone”. That is the real issue.

Now, what happens when sites are migrated to Microsoft 365 SharePoint Online with Microsoft Delve enabled by default? Delve works as it should work – it suggests to you documents it believes related to you (based on Microsoft Graph insights) and you already have access to.

What happens is people start seeing documents they never new they have access to. Where these documents from? Of course from sites shared with Everyone.

So strictly says, it is not Delve’s problem. It’s more human problem than technological.
Delve just does it’s job, and does perfectly.

How do we solve the issue?

  1. Disable Delve?
  2. Disable search (stop sites crawling and remove results)?
  3. Restrict access to Microsoft Graph ?
    e.g. Microsoft KBA on how to disable MS Graph for a specific User

Those methods are half-measure. 1-2-3 methods are just hiding the problem – not solving it. Agree it helps stop the deterioration, bud does not fix the issue.

How do we solve the root cause of the issue?

  1. Of course, we need remove incorrectly provided permissions. How?
  2. Only site owner (data owner) knows which content should be shared with whom with which access rights. So we need to ask sites owners to review their permissions. How?
  3. First, we need a list of over-exposed sites. How? There are two methods
    (more details – check this article)
    • Brute force – use PowerShell or 3-rd party tool to get permission report on all sites in tenant, select permissions provided for Everyone…
    • Smart move – use Microsoft search. As search is security-trimmed, we can search for available content on behalf of a user with no permissions provided.
  4. Then we need a list of sites and their owners. How?
    1. tbp
  5. Finally, we need to let every site owner know that his site is Open to everybody and ask to fix it. How?
    1. tbp
    2. inform the site owner how to get full permissions report to his site,
      e.g. KBA How To Get SPO Site Full Permissions Report
      and video “Full Permissions Report for a Team SPO Site Owner


References

PnP.PowerShell Release 1.3.0

Great news:

Added -Interactive login option to Connect-PnPOnline which is similar to -UseWebLogin but without the limitations of the latter. The -UseWebLogin is using cookie based authentication towards SharePoint and cannot access Graph tokens. Using -Interactive we use Azure AD Authentication and as a result we are able to acquire Graph tokens.

more changes: https://github.com/pnp/powershell/releases/tag/1.3.0

Connect-PnPOnline with a certificate stored in Azure Key Vault

Scenario

You need to run some PnP PowerShell code unattended (daemon app, with no user interaction) against SharePoint and/or Azure AD. PnP require authentication with a certificate. You want certificate stored securely in Azure Key Vault.

Solution

  • create a self-signed certificate
  • register an application in Azure
  • add API application permissions to the app
  • upload the certificate to the app
  • create an Azure Key Vault
  • provide permissions to the Key Vault for the user
  • run Connect-AzAccount
  • upload certificate to the Key Vault manually (with GUI)

now you can run this code and it will not ask you to login:

# set parameters:
$orgName = "orgname" 
$clientID = "" # Client ID
$VaultName = "" # Azure Key Vault Name
$certName = "" # Certificate Name as in Azure Key Vault
$tenant = "$orgName.onmicrosoft.com"
$adminUrl = "https://$orgName-admin.sharepoint.com"
# run the following
$secretSecureString = Get-AzKeyVaultSecret -VaultName $vaultName -Name $certName 
$secretPlainText = ConvertFrom-SecureString -AsPlainText -SecureString $secretSecureString.SecretValue
Connect-PnPOnline -Url $adminUrl -ClientId $clientID -CertificateBase64Encoded $secretPlainText -Tenant $tenant 

The same PowerShell code in GitHub: https://github.com/VladilenK/PowerShell/blob/main/PnP/Connect-PnPOnline-with-certificate.ps1

References:

https://docs.microsoft.com/en-us/powershell/module/az.keyvault/get-azkeyvaultcertificate?view=azps-5.3.0

https://stackoverflow.com/questions/43837362/keyvault-generated-certificate-with-exportable-private-key

Yammer API with PowerShell: Private groups and messages

As an Office 365 administrator, I would like to get some reports on Yammer with PowerShell. How it’s done?

Patrick Lamber wrote a good article here: “Access the Yammer REST API through PowerShell“. The only I would add (important!) is:

By default, even with a Verified Admin token, you do not have access to private messages and private groups content.
To get private stuff, you need select “Private Content Mode” under Yammer Admin Center -> Content and Security -> Content Mode:

Check Microsoft: “Monitor private content in Yammer” and
Yammer: “Verified Admin Private Content Mode

If you do not have “Private Content Mode” set up, you might see some weird “Invoke-WebRequest” errors like:

PnP PowerShell 7.1 Parallel

(WIP)

Can I run something like

$items | ForEach-Object -Parallel {
    $listItem = Set-PnPListItem -List "LargeList" -Identity $_ -Values @{"Number" = $(Get-Random -Minimum 100 -Maximum 200 ) }
} 

Unfortunately, no. Instead, use batching!

$batch = New-PnPBatch
1..100 | ForEach-Object{ Add-PnPListItem -List "ItemTest" -Values @{"Title"="Test Item Batched $_"} -Batch $batch }
Invoke-PnPBatch -Batch $batch

More:
Batching in PnP PowerShell | PnP PowerShell
– https://www.youtube.com/watch?v=0-DSwZyK2Zo

Long-running PowerShell Office 365 reports

(WIP)

PowerShell is our best friend when it comes to ad-hoc and/or scheduled reports in Microsoft 365. PnP team is doing great job providing more and more functionality with PnP PowerShell module for Office 365 SharePoint and Teams.

Small and medium business organizations are mostly good, but for large companies it might be a problem due to just huge amount of data stored in SharePoint. PowerShell reports on all users or all sites might run days… which is probably OK if you run this report once, but totally not acceptable if you need this report e.g. daily/weekly or on-demand.

How can we make heavy PowerShell scripts run faster?

Of course, you start with logic (algorithm) and leveraging full PowerShell functionality (e.g. PowerShell 7 parallelism or PnP batching).

(examples)

What if you did everything, but it still takes too long? You need something like brute force – the closer your code runs to your tenant – the better.
What are the option?
– Automation account runbook (+workflow)
– Azure Function Apps
– Azure VM in the region closest to your Tenant

Automation account runbook (+workflow)

Seemed like a good option, but not something Microsoft promotes. Even opposite – automation accounts support only PowerShell 5 (not 7), no plug-ins for VS Code and recently there were messages on some retirement or smth.

Meantime, I tested it – and did not find any significant increasing in speed. In a nutshell, what is behind this service? Same windows machines running somewhere in Azure .

TBC

References
PnP PowerShell

SPO: Allow users to create modern pages

Microsoft: “Using modern pages in Microsoft SharePoint is a great way to share ideas using images, Office files, video, and more. Users can Add a page to a site quickly and easily, and modern pages look great on any device.
If you’re a global or SharePoint admin in Microsoft 365, you can allow or prevent users from creating modern pages. You can do this at the organization level by changing settings in the SharePoint admin center. If you allow the creation of site pages as the organization level, site owners can turn it on or off at the site level.

By default both
– Allow users to create new modern pages
– Allow commenting on modern pages
are turned on (enabled)

Tenant or SharePoint admin can find settings under
SharePoint Admin Center -> Settings -> Pages

How it looks like:

Site Pages are created under “Pages” Library.

Let us test it, with:
– (tenant-level) Allow users to create new modern pages: ON
– (tenant-level) Allow commenting on modern pages: ON
– web feature “Site Pages” – “Allows users to add new site pages to a site”: Activated

User
Permissions
can create Pagecan edit pagecan Enable/Disable
page comments
can comment on Page
Full Control (Owner)YesYesYesYes
Edit (Member)YesYesYesYes
Read (Visitor)NoNoNoYes

There is a web feature “Site Pages” – “Allows users to add new site pages to a site”.
The feature is activated by default:

What if we disable this feature?
“New -> Page” has disappeared from “New” menu under “Site Contents” for Owners and Members…
From “Home” and “Pages” you still can see “New -> Page” options.
You can still create a new page from but if you try to create a page from Pages – “Sorry, something went wrong” “Cannot create a Site Page. Please have your administrator enable the required feature on this site.” :

Office 365 behavior, with:
– (tenant-level) Allow users to create new modern pages: ON
– (tenant-level) Allow commenting on modern pages: ON
– web feature “Site Pages” – “Allows users to add new site pages to a site”: Deactivated

User
Permissions
can create Pagecan edit pagecan Enable/Disable
page comments
can comment on Page
Full Control (Owner)Yes,
but only from “Home”
not from “Site Contents” or “Pages”
YesYesYes
Edit (Member)Yes,
but only from “Home”
not from “Site Contents” or “Pages”
YesYesYes
Read (Visitor)NoNoNoYes


If we disable feature “Site Pages” – “Allows users to add new site pages to a site” on the root web – it does not affect subsites (subwebs).

Can we Activate/Deactivate the feature “Site Pages” using PowerShell?

PowerShell

(TBP)

References
– Microsoft “Allow users to create and comment modern pages

See also:
Allow commenting on modern pages