Connecting to SharePoint Online programmatically: Secret vs Certificate

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)

Kazakhs Anthropology

Аскар Исабеков Антропология казахов

Насколько мне известно, книга был написана в 2011 году по заказу Vox Populi (Казахстан), оплачена но тогда не издана.

Похоже, теперь издали в бумажном варианте.

Get list of new SPO sites with PowerShell

Scenario

Let say you administer Microsoft 365 SharePoint Online and you want to get a list of new SharePoint sites (e.g. sites created recently – during last day/week/month).

With GUI it’s done easily: SharePoint Admin Center -> Active Sites -> sort based on “Date Created” – done.

With PowerShell – not so simple.
“Get-PnPTenantSite” cmdlet returns a site object but the object does not have “Created” field. It’s a web property. But to get a web object – you have to connect separately to each site and get root web object to check when the web was created. For small environments it is possible, for large environments it can take days… And still not nice.
“Get-PnPTenantSite” with “-Filter” option would help, but “…Currently, you can filter by these properties: Owner, Template, LockState, Url.”

Get-SPOSite – similar experience.

Solution

Microsoft Graph API helps. It returns result in seconds and you can sort or filter results based on created date . Below are two methods: Option 1 is based on Search and filtering and Option 2 is based on Sites Search and sorting. So there are some pros and cons for each method.

Option #1: Microsoft Graph Search API.

Entry point: https://graph.microsoft.com/v1.0/search/query

Microsoft Graph Search API allows KQL in queries. So we can form a query with something like “created>=1/1/2021” and use entity type = ‘[“site”]’. Search should return only sites created after Jan 01, 2021.

Check PowerShell script sample here: Get-NewSites.ps1
https://github.com/VladilenK/PowerShell/blob/main/reports/SharePoint/Get-NewSites.ps1

If you are getting more than 500 results – think of paging.

Option #2: Microsoft Graph Sites API

Entry point: https://graph.microsoft.com/v1.0/sites

This option is also based on Microsoft Graph API, but sites entry point, which allows search too and sort results by property “createdDateTime”. So we will just search for everything and select how many results we need based on createdDateTime property.

Check PowerShell script sample here: Get-NewSites.ps1
https://github.com/VladilenK/PowerShell/blob/main/reports/SharePoint/Get-NewSites.ps1

References

Why we study foreign languages

I know that my country – the country where I was born and grew up, the country where my parents gave me everything – from first sip of milk to education and cultural upbringing, the country where I got my friends – this country is the best country in the world. But wait… Did you say “the world”? What is the world? Is it something where other people live? Do those people live their own ways and speak their own languages? Do they love their countries and their culture?

Of course, we know that there are other countries (we use internet-connected computers and smartphones). We probably heard/use words like Gastarbeiter, automobile, opera. But did you know they all came from different other languages to our language. Now what are the other languages and why do we learn foreign languages? I would say there are two main reasons.

The first one is just a practical reason that came from the past – knowing a foreign language helps you make more money. International trade is an essential part of the world economy and impossible without communication. So the ability to communicate in different languages simply gives you an advantage – an ability to do thing not everybody can do – and an opportunity to earn more.

The second reason lies more in the cultural or cognitive field. Simple example: How do I know that The Hamburgeris is tasty? Only because I tried other food and I can compare. The same with languages. “Wer fremde Sprachen nicht kennt, weiß nichts von seiner eigenen” – which means “Those who do not know foreign languages do not know anything about their own” stated the famous German poet and scientist Johann Wolfgang von Goethe.

And that is so true. I just started understanding it because I just started studying foreign language. And the more I learn it – the more I like it. The world is diverse. And that’s the beauty of it. And that diversity is expressed via languages. Each language is unique and beautiful as it absorbs all historical and cultural legacy of a nation.

So, knowing other languages not only benefits us with more opportunities and perspectives, but makes us more tolerant, open-minded, intelligent and creative.

Essay: My future profession

Change is live.  Static is death. Everything and everybody must change for live. The moment you stop developing – you are out. That’s true for everything starting from plants and animals. For human being that’s also true, but a little different. We are not competing for food any more. As a social creatures we want to be a part of the society, we need to be respected, recognized, useful. And that is done via job, via profession we choose.

In average we spent 8 hours a day at work. Some people might think that work time is distracted from our live because only after work you can have fun spending money hard earned during the work time when you have to do something unpleasant just to be able to do what you really want to do after work. Miserable and pitiful people they are. Eight hours a day is a decent part of our life. I do not want to waste that time. I want to enjoy that time. How do I enjoy that time? Choosing a profession that I love. 

In a modern world one of the biggest problem mankind facing is a human health. And that is where I want to work. I know, that’s not easy. In order to be successful in the healthcare field, I need to pose certain skills and abilities. One of the essential skills is an ability to love people. I must be able to treat and respect my clients in order to maintain their trust and support. As well as readiness and acceptance. I have to be precise and confident in my actions. I have to be able to accept the consequences if something goes wrong. There may be times when an emergency occurs and the environment gets chaotic. But, on the other hand, what could be better, what could be more demanded and rewarding then help people live healthier lives? 

Retrieve SharePoint Online system page html content programmatically (PowerShell)

How can I get HTML content of a SharePoint online page from code, e.g. PowerShell?

Invoke-WebRequest returns “Sign in to your account” page, not a real page, even with -Token option.

Thanks to Denis Molodtsov, the solution is found. It turns out the “Invoke-PnPSPRestMethod” PnP cmdlet works not only against /api endpoints, but also against site pages and system pages.

But (as per my experience) it works only with PnP.PowerShell and with -UseWebLogin authentication option and with -raw parameter.

Connect-PnPOnline -url $siteUrl -UseWebLogin
Invoke-PnPSPRestMethod -url /_layouts/15/viewlsts.aspx -Raw

Other combination of authentication options ( -interactive, -clientId, -Token, -SPOManagementShell, -PnPManagementShell ) – worked well, but only for /_api endpoints, and gave me “401 UNAUTHORIZED” against system/site pages.
Unattended authentication (with clientId, clientSecret and certificate) – same.

Legacy PnP module SharePointPnPPowerShellOnline did not work at all: “EXCEPTION,PnP.PowerShell.Commands.Admin.InvokeSPRestMethod”.

I tested it with
– SharePointPnPPowerShellOnline v 3.29.2101.0 (under Windows PowerShell 5.1) and
– PnP.PowerShell 1.8.0. (both Windows PowerShell 5.1 and .net core PowerShell 7.1.5)

in the middle…

It happened in the middle of 1990-x. I just started to work on software company in Almaty as a computer engineer. The company had a customer in the city of Kustanai. I was sent to Kustanay to solve some problem.
It was winter. Winters in the north-Kazakhstan are pretty cold. Business-trip turned-up a little longer than I expected. At the end, day of flight home came. By this time I had run-out of money and worm clothes.
A Little digress from the topic. I used to travel a lot – by plane, by train and even by ship. I used to be petty experienced traveler. What I do not like at all – is crowds and queues. But when you are travelling by plane, you have to be in queues and among the crowd. You have to stay in the row before check-in, then before security, customs, passport control etc. When boarding announces – everybody rush to the gate and stand at their feet half an hour in the row. Usually in such a situation I sit down somewhere near and wait until everybody is boarded, then, among the couple of the same calm as myself, I get on the plane.
But that time something went unusual. I had been waiting on the bench, and thinking of home. And when boarding was announced, I decided not to wait until the very end, but rushed among the others to the gate. There was no bus. All passengers had to walk across the take-off field, at night, at cold, when the wind knocked you down. Again, I was not first, but was not last one.
The plane was Yak-40, a little jet, designed for 50 seats. The entrance was at the back side of the plane. Convertible back door serves as a stairs when got back. I got on the plane, took my seat and then heard a noise from the back door. A flight attendant, strong woman, blocked access to the plane, standing in the doorway. I was heard something like “Stop, get out of plane. We have no free seats more.”
Passengers were trying to climb. There was almost a fight. A man in the uniform – a second pilot – stout man – hurried to help the flight-attendant. They managed to push people out and closed the door. The plane took off. I was sitting in my seat, in the warm cabin, on the way home, thinking of people who had stayed behind, along, in the middle of the airfield, in the cold night.

Search Unified Audit Log Daemon Job

How to run “Search-UnifiedAuditLog” in unattended way, i.e. non-interactive.
What are the minimal permissions required?

The PowerShell code:

$clientId = ""
$cPwd = ConvertTo-SecureString -String "" -AsPlainText -Force

$cPath = ""C:\Users\UserName\Certificates\Cert.pfx""
$organization = "contoso.onmicrosoft.com"

Connect-ExchangeOnline -CertificateFilePath $cPath -CertificatePassword $cPwd -AppID $clientId -Organization $organization

[DateTime]$start = [DateTime]::UtcNow.AddMinutes(-45)
[DateTime]$end = [DateTime]::UtcNow
$resultSize = 1000

$results = $null
$results = Search-UnifiedAuditLog -StartDate $start -EndDate $end -ResultSize $results.Count
$results | Select-Object RecordType, CreationDate, UserIds, Operations -First 3

Disconnect-ExchangeOnline -Confirm:$false

Troubleshooting

The error “The term ‘Search-UnifiedAuditLog’ is not recognized”:

Search-UnifiedAuditLog: C:\scripts\PowerShell.auth\Search-AuditLog-w-App.ps1:16:12
Line |
16 | $results = Search-UnifiedAuditLog -StartDate $start -EndDate $end -Re …
| ~~~~~~
| The term 'Search-UnifiedAuditLog' is not recognized as a name of a cmdlet, function, script file, or executable
| program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

means a proper administrative role (e.g. “Exchange administrator”) is not assigned to the app.

References

PowerShell Script to Fetch All Alerts from SharePoint Online Site

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