Modernizing Authentication in SharePoint Online

Starting a year or two ago, Microsoft announced it would stop supporting and/or blocking access to Azure Access Control Services (ACS) and the SharePoint Add-In model. This is important because ACS has been used for many years to grant app/script API access to a SharePoint site(s), and you likely have many sites where this has been used. Moving forward, Azure Access Control (AAC) will be used in place of ACS.

Historically, you would start the permissions journey by generating the client ID and secret at this endpoint:
_layouts/15/AppRegNew.aspx
From there, you grant the newly created identity access to a tenant, sites, lists, libraries, or a combination.
_layouts/15/appinv.aspx
The other option was to create an Azure App Registration and then grant it access to the target objects. When working with SharePoint Online and AppRegNew.aspx, the App Registration is generated automatically. Depending on what is/is not configured, this can be an issue and set off alarms in Azure.

With that out of the way, how do you wire-up a new connection to SharePoint Online, allowing PowerShell, Python, script, or app access to the SharePoint API or the Microsoft Graph API?

Overview of what I’m doing:
Create a self-signed cert
Add the cert to your personal cert store and upload it to Azure, creating an App Registration
Adjust permissions as needed
Grant the App Registration access to a specific SharePoint site
Use the newly created credentials to access the SharePoint site

#create cert with a password
New-PnPAzureCertificate `
    -CommonName "Demo_SP_Azure_2024" `
    -OutPfx c:\code\Demo_SP_Azure_2024.pfx `
    -OutCert c:\code\Demo_SP_Azure_2024.cer `
    -CertificatePassword (ConvertTo-SecureString -String "Taco@Good" -AsPlainText -Force) `
    -ValidYears 1

#import the cert. for this to work, run as Admin.
Import-PfxCertificate `
    -Exportable `
    -CertStoreLocation Cert:\LocalMachine\My `
    -FilePath c:\code\Demo_SP_Azure_2024.pfx `
    -Password (ConvertTo-SecureString -String "Taco@Good" -AsPlainText -Force)

I highly suggest not skipping the ‘-interactive’ part for the next command. It will open a browser window where you must authenticate with an account with adequate permissions to create a new App Registration in Azure. The script’s most important thing to note is SharePointApplicationPermissions Site.Selected. Why is this important? This is extremely useful if you want to limit permissions to a single SharePoint site and not every site in the tenant.

Register-PnPAzureADApp `
   -ApplicationName Demo_SP_Azure_2024 `
   -Tenant tacoranch.onmicrosoft.com `
   -Store CurrentUser `
   -SharePointApplicationPermissions "Sites.Selected" `
   -Interactive

After that runs, the output will include the Client ID and Certificate Thumprint. Take note of it, or you can open Azure, navigate to App Registrations, and select all applications. In the left nav, click Certificates & secrets, where you’ll find the thumbprint; again, in the left nav, click Overview, and you’ll see the Application ID, aka Client ID.

In the next two commands, you will connect to the SharePoint Admin site interactive, then grant the newly created App Registration write access to the target SharePoint site.

Connect-PnPOnline -Url "https://tacoranch-admin.sharepoint.com" `
    -Interactive

#grant the App Reg write access to the site
Grant-PnPAzureADAppSitePermission `
    -AppId 'a95ddafe-6eca-488a-b26a-dc62a64d9105' `
    -DisplayName 'Demo_SP_Azure_2024' `
    -Site 'https://tacoranch.sharepoint.com/sites/cert-demo' `
    -Permissions Write


Now that the App Registration can access the SharePoint site, how do you connect to it using the certificate and thumbprint?

Connect-PnPOnline `
    -Tenant "tacoranch.onmicrosoft.com" `
    -Url "https://tacoranch.sharepoint.com/sites/cert-demo" `
    -ClientId "a95ddafe-6eca-488a-b26a-dc62a64d9105" `
    -Thumbprint "5C5891197B54B9535D171D6D9DD7D6D351039C8E" 

Get-PnPList | Select-Object Title

Using the above commands, you can create a cert, register it in Azure, and grant access to a single SharePoint site.

I’ve included a copy of the full script here:
https://www.sharepointed.com/wp-content/uploads/2024/03/Azure-App-Reg-Cert-Demo.txt

Error(s) and fixes:
Error:
Grant-PnPAzureADAppSitePermission: {“error”:{“code”:”accessDenied”,”message”:”Access denied”,”innerError”:{“date”:”2024-03-21T16:29:47″,”request-id”:””,”client-request-id”:””}}}
Fix:
Ensure the account running this command Grant-PnPAzureADAppSitePermission , has access to the target SharePoint site.

Flow and SharePoint Online Large Libraries

Using Flow to get or check for files in a large SharePoint library can be a little tricky. If you are sure your library will always stay under 5,000 items the Get Files (properties only) Flow action is a quick n’ simple approach to use. When your library crosses over the mythical 5k mark or somewhere in that neighborhood, the Get Files action will fail to return results without warning. What I’m outlining below are other options when working with large libraries.

Setup:



Option 1: Get Files using an Odata filter query
downside: only use this in small libraries



Result:


Option 2: use the SharePoint API
downside: the lack of transparency from Microsoft related to how often data is crawled.

Result:

Option 3: use the SharePoint API along with a filter action on the library. This option does require that you have metadata set up on the library to filter on and there is not a wildcard / contains option. The filtered value needs to be exact.
downside: you will need to set up your metadata ahead of time or create it after the fact then backfill.

Result:

The more I learn about Flow and SharePoint Online, the more I’m starting to like Option 3 when doing a lookup type of search. SharePoint Search is an extremely powerful tool if the search index is fresh.