Using Managed Identity in Azure Automation Runbook to connect to SharePoint Online

[joostvdlinden]

Discussed in #2559

^{Originally posted by joostvdlinden November 16, 2022}
Hi folks,

I am trying to connect to SharePoint Online from within an Azure Automation Runbook using a system assigned managed identity.
I noticed that 6 days ago a new release of pnp has been published, which includes the remark "Added system assigned Managed Identity support for SharePoint Online cmdlets. #2354"

Well I have been trying to use this feature for a couple of days now, but somehow I am not able to make it work.
I have enabled the system assigned identity on the 'Identity' tab of the Automation Account. Next, I have assigned permissions to the Enterprise application which had been created automatically by enabling the managed identity. Currently I have assigned the following permissions:

• Microsoft Graph - Group.Read.All | Application type
• Microsoft Graph - User.Read.All | Application type
• Microsoft Graph - Sites.FullControl.All | Application type
• Office 365 SharePoint Online - Sites.FullControl.All | Application type

Eventually I want to limit it down to the least permissive permission level.

So what I want to achieve is that I can connect to any random SharePoint site collection and perform the Rename-PnPTenantSite cmdlet.

When I try the script below (just for testing purposes), I get the error: Unable to connect to the SharePoint Online Admin Center at 'https://orgname-admin.sharepoint.com' to run this cmdlet. Please ensure you pass in the correct Admin Center URL using Connect-PnPOnline -TenantAdminUrl and you have access to it. Error message: The remote server returned an error: (401) Unauthorized..

Connect-PnPOnline -Url orgname.sharepoint.com -ManagedIdentity

Get-PnPTenantSite -Identity "https://orgname.sharepoint.com/sites/TestSite"

Disconnect-PnPOnline

When I try the script below (just for testing purposes), I get the error: Suspended
The runbook job was attempted 3 times, but it failed each time. Common reasons that runbook jobs fail can be found here: https://docs.microsoft.com/en-us/azure/automation/automation-troubleshooting-automation-errors

Connect-PnPOnline -Url "https://orgname.sharepoint.com/sites/TestSite" -ManagedIdentity

Get-PnPSite

Disconnect-PnPOnline

Can someone help me figure out what I'm doing wrong here?

Thanks for all help provided!

[tld6764]

Same exact issue here. It seems like a permissions issue. But I have the same permissions defined as you do.

[lukju]

Same issue here. Did you find a work around or the root cause of this behavior?

[joostvdlinden]

Hi @lukju still no solution yet, unfortunately.

[dremillard]

I have a similar (but different) issue connecting from a runbook using AppID and Cert thumbprint. Noticed it around the same timeframe as this issue. Wonder if there's any connection? My issue is described in discussion #2585

[Dave365CH]

same issue here trying with an azure function and its managed identity

[Null-Fault]

I have also been experiencing this issue. It was odd because I had it working just fine in another tenant, but looking at my working service principal's permissions I discovered something. It appears to be caused by the existence of both the Graph and SharePoint Sites.FullControl.All permissions existing for the service principal.

After removing the Graph permission and leaving the SharePoint Sites.FullControl.All I was able to run my Azure Automation runbooks using -ManagedIdentity without receiving a 401 error.

[martinlingstuyl]

Hi @joostvdlinden,

It might just be that the problem you are facing has nothing to do with auth.

The command Get-PnPSite tries to log to the console, which is problematic in Azure Automation. I'm not sure exactly why.

What you can do is assign the result to a variable: $site = Get-PnPSite and use Write-Output to check if it holds a value.

Reference:
https://social.msdn.microsoft.com/Forums/en-US/d232d7d1-53ce-4778-b6f8-2de6bcba272a/the-runbook-job-was-attempted-3-times-but-it-failed-each-time?forum=azureautomation

[kingy444]

I have also been experiencing this issue. It was odd because I had it working just fine in another tenant, but looking at my working service principal's permissions I discovered something. It appears to be caused by the existence of both the Graph and SharePoint Sites.FullControl.All permissions existing for the service principal.

After removing the Graph permission and leaving the SharePoint Sites.FullControl.All I was able to run my Azure Automation runbooks using -ManagedIdentity without receiving a 401 error.

Thanks for the suggestion, had my hopes up for a moment - unfortunately this didn't work in my case
System Managed Identity is throwing the same error
Unable to connect to the SharePoint Online Admin Center at 'https://mytenant-admin.sharepoint.com' to run this cmdlet. Please ensure you pass in the correct Admin Center URL using Connect-PnPOnline -TenantAdminUrl and you have access to it. Error message: The remote server returned an error: (401) Unauthorized..

The identity did have both the graph and SharePoint permissions but still having the same issue after removing the Graph Permission.

Connect-PnPOnline -Url "https://mytenant.sharepoint.com" -ManagedIdentity

Microsoft Graph\Group.Read.All
Microsoft Graph\User.Read.All
Office 365 SharePoint Online\Sites.FullControl.All

From https://pnp.github.io/powershell/articles/azurefunctions.html#by-using-a-managed-identity you need to be running >version 1.11.95-nightly and I am using 1.12.0 so that shouldn't be an issue.

[martinlingstuyl]

Hi @kingy444,
If you want to run commands that require an admin context, you should sometimes connect to it yourself. PnP PowerShell doesnt always switch for you.

Did you try to do that? (By Adding the -admin part in your url that you're connecting to) Just to be sure, your comment does not suggest this.

[kingy444]

Hi @martinlingstuyl

Thank you for the suggestion. The error message threw me off as it was suggesting to use the -TenantAdminUrl parameter which I could not get to work and not to add the '-admin' into the -Url parameter

This isn't documented over at https://pnp.github.io/powershell/cmdlets/Connect-PnPOnline.html#example-12 but great tip as we are up and running using the below. Never needed to do this when using the RunAsAccount but at least it is working now.
Connect-PnPOnline -Url "https://mytenant-admin.sharepoint.com" -ManagedIdentity

[martinlingstuyl]

Happy to hear its working now!

[joostvdlinden]

Well finally, got it working too.
For assistance to others, this is working for me in the Runbook:

Connect-PnPOnline -Url "https://tenantname-admin.sharepoint.com" -ManagedIdentity

$site = Get-PnPTenantSite -Identity "https://tenantname.sharepoint.com/sites/TestSite"
Write-Output $site

Disconnect-PnPOnline

Thanks all

[martinlingstuyl]

🦾 That's awesome @joostvdlinden, in that case, could you close this issue?

[kingy444]

Could I potentially suggest a documentation update around this before closing ?

[martinlingstuyl]

You mean something like this, but targeted at Automation Runbooks @kingy444?