Talking to SharePoint with Azure Automation

Last updated on July 10, 2020

tl;dr

Recently the SharePoint Online PnP Powershell module added native support for using certificate-based authentication. In this post I will show you how to leverage this support to run PowerShell-scripts against your SharePoint Online environment from Azure Automation.

Some background…

When interacting with SharePoint Online programmatically, you have a number of API’s to run your code against. Traditionally, these relied on SharePoint app registrations for authentication and authorization.

Another way to achieve this is using an AzureAD app registration. The main advantage of using this method is that you only need a single identity for interacting with SharePoint Online and Azure. This is especially useful when you want to automate tasks in SharePoint Online using Azure Automation. Additional benefits are;

• reduced attack surface – less identities exist in your tenant;
• a reduction of overhead – you only have to manage one identity, rather than two.

The biggest disadvantage of these Azure AD app registrations, is that you need to use certificate based authentication. This is more secure than secret-based authentication but also more complex to set up. I will show you how to do this.

Let’s get started!

To start, we need to setup the application registration. For this I will refer you to Microsoft’s tutorial on this subject. After you have completed those steps you should have:

• A certificate, exported twice, one with and one without the private key. These certificates can be self-signed as they are not exposed to the world. I recommend;
  • a .cer for the public part and;
  • a password protected .pfx for the private part.
• An AzureAD app registration with;
  • permission on the SharePoint API’s (Sites.FullControl.All);
  • admin consent granted;
  • the public certificate uploaded to it’s Certificates & Secrets-tab.

Next up: Automation

Deploy an automation account to your Azure subscription or reuse an existing one. You do not need a ‘RunAs’-account for this bit. Don’t forget to import the SharePointPnPPowerShellOnline module either. Or you’ll end up looking like an idiot. Like I did when doing this for this demo…

Once it’s provisioned upload the .pfx certifcate via the certificates-tab on the account’s blade.

With the certificate imported into the automation account, we need to make sure we write our runbooks in a way that they are able to use the certificate for authentication.

Here is a snippet of code showing you how to use connect-pnponline using the newly imported certificate in a runbook:

$appid = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' #the application (client) ID from the app registration
$tenant = 'sometenant.onmicrosoft.com' #your tenant url
$url = 'https://sometenant.sharepoint.com' #the sharepoint site you wish to connect to
$cert = Get-AutomationCertificate -Name 'DemoCertificate' #the certificate you just imported

Connect-PnPOnline -url $url -ClientId $appid -Certificate $cert -Tenant $tenant #the magic
Get-PnpContext #confirmation of set magic, remove this in your scripts...

As always, be careful when running cmdlets in runbooks. Cmdlets should always output into a variable or be directed at out-null. This ensures your runbook won’t break when the output of the cmdlet changes. The proper way to use the connect-pnponline cmdlet would be:

$connection = Connect-PnPOnline -url $url -ClientId $appid -Certificate $cert -Tenant $tenant

Wrapping up

With the example in this post you can easily move on to creating complex runbooks, automating a wide range of SharePoint Online management tasks. You can also expand the automation into other Azure Resources such as Logic Apps, Key Vaults or Storage Accounts.

Using the AzureAD app registration can be used for many other things besides, authenticating to SharePoint Online for Azure Automation. Many other scenarios where you need to authenticate to SharePoint Online programmatically can utilize this. Unfortunately this functionality does not (yet) extend to the SharePoint-Online PowerShell-module (connect-sposervice).

Using app registrations in Azure significantly reduced the reliance on traditional credentials. Forced expiration of secrets and centralized authorization helps keep your solutions manageable and secure.