Update SharePoint User Profiles based on Azure Group Membership

Many customers with a SharePoint Online intranet also have a page based on people search. Many default SharePoint User Profile properties are synced from Azure AD automatically. But what if you added your own custom properties that you would like to keep in sync based on information in Azure?

Overview scenario

I have many SharePoint User Profiles for different kind of accounts in my Azure AD: I have FTE, temporarily hired people, interns, etc… In Azure AD I have a distribution group with only FTE members. On my SharePoint page I have a people search component that I only want to show the FTE profiles.

For my solution I need a SharePoint User Profile property and a Search Managed property. For the sync component I use a timer triggered Azure Function that reads the members from the Azure AD Group and updates the SharePoint User Profile property accordingly. For the script I use PowerShell, to have it accessible and maintainable for admins.

Still with me? Let’s gooooooooo!

SharePoint User Profile Property

From the SharePoint Admin site, navigate to More Features and open the User Profiles page.

On the User Profiles page, click on the link Manage User Properties

Click on the link New Property

Give both Name and Display Name the value : isFullTimeEmployee and set the Type to boolean

Set the Policy setting to Optional and the Privacy setting to Everyone. This last one is important because this makes it available for SharePoint Search.

Last but not least, make sure the Search setting indexed is checked. By default it is, but check it anyway.

Click on the button [OK] to save the new property.

Before we can configure SharePoint Search, you need to update one user profile to set this new property. It can take a few hours (!) before the Search crawler picks it up.

SharePoint Search Managed Property

Once the search crawler has picked up the new user profile property, a crawled property called people:isFullTimeEmployee now exists. We need this one to create a managed property and map it to this crawled property. The managed property will be required when we do a search query to get all required user profiles.

From the SharePoint Admin site, navigate to More Features and open the Search page.

On the Search page, click on the link Manage Search Schema

Click on the link New Managed Property

Give the property the name IsFullTimeEmployee and set its type to Yes/No

At Main Characteristics, make the property Searchable, Queryable and Retrievable.

Last but not least, add the mapping to the crawled property People:isFullTimeEmployee

Click on the button [OK] to save the Managed Property.

Azure AD Group

In my Azure AD I have created a security group called RBG-FullTimeEmployees. Maybe in your case you have a Distribution List of Microsoft 365 Group. All fine.

What we need here is the Object Id. We are going to use this later with a Microsoft Graph call from our Azure Function.

Azure Function

The Azure Function is the heart of this solution. Basically, it will query both the Azure AD group and the SharePoint User Profiles using Search. Then it will update the SharePoint User Profile. For all group members set the User Profile property IsFullTimeEmployee to True. For all user profiles found and not a member of the Azure AD group set the property to False.

Now, for the Azure Function do all this, we need quite some configuration and permissions. I want to thank Luise Freese with her post that helped me very much! ?

Create your Azure Function the way you like best. Whether it’s manually in the Azure Portal or by using Azure CLI, it’s all okay as long as the runtime stack is PowerShell Core:

Once created, we can configure the Function App.

Managed Identity

For accessing Azure and Graph resources we need permissions. For this I’ll use a Managed Identity. No need now to create an additional App Registration (although we’ll need one later. Hang on…)

Navigate to your created Function App.

In the Settings category, select Identity

On the tab System assigned, toggle the Status switch to On.

Save your settings.

This will create a Managed Identity. We can look this up in Azure AD in Enterprise Applications. It has the same name as your Function App.

When you select your Managed Identity and go to its Permissions, you’ll see it is still empty. And more important, there is no option in the Azure Portal to set permissions! What now? Well, PowerShell or CLI to the rescue!

What the Azure Portal does provide, is the option to run CLI in the browser: Cloud Shell. Awesome, no need to install things locally and sign in.

When you start Cloud Shell, you’ll see something like this:

Now you can use CLI commands directly in the browser. How cool is that! We need to give our Managed Identity some permissions to use the Microsoft Graph. But what permissions exactly? One, we need to query Group Membership and two, we need to know the tenant’s SharePoint root site. For this we need to set Directory.Read.All and Sites.Read.All.

The script is as follows:

#Set values
$webAppName = "RED-FA-WHO-DEV-WE"
$principalId = $(az resource list -n $webAppName --query [*].identity.principalId --out tsv)
$graphResourceId = $(az ad sp list --display-name "Microsoft Graph" --query [0].objectId --out tsv)

# API permission: Directory.Read.All
$appRoleId = $(az ad sp list --display-name "Microsoft Graph" --query "[0].appRoles[?value=='Directory.Read.All' && contains(allowedMemberTypes, 'Application')].id" --out tsv)
$body = "{'principalId':'$principalId','resourceId':'$graphResourceId','appRoleId':'$appRoleId'}"
az rest --method post --uri https://graph.microsoft.com/v1.0/servicePrincipals/$principalId/appRoleAssignments --body $body --headers Content-Type=application/json

# API permission: Sites.Read.All (Graph)
$appRoleId = $(az ad sp list --display-name "Microsoft Graph" --query "[0].appRoles[?value=='Sites.Read.All' && contains(allowedMemberTypes, 'Application')].id" --out tsv)
$body = "{'principalId':'$principalId','resourceId':'$graphResourceId','appRoleId':'$appRoleId'}"
az rest --method post --uri https://graph.microsoft.com/v1.0/servicePrincipals/$principalId/appRoleAssignments --body $body --headers Content-Type=application/json

After execution, you can check the permissions for the Managed Identity again:

App Registration

Sadly (and as far as I know) we also need an App Registration. Why? Because we need to access SharePoint Search and SharePoint User Profiles using REST API and PnP PowerShell. I could not get it done with Managed Identities. That’s only for Azure and Microsoft Graph resources, I guess.

Navigate to Azure Active Directory and select App Registrations

In the App registrations blade, click on the link New registration

Give it a name and click on the button [Register]

Once created, the App Registration is shown. Navigate to Permissions

Add the following permissions:

Grant admin consent.

Almost done with the App Registration. In order to use this app and its permissions, we need to create a secret (or a certificate).

Navigate to Certificates and Secrets.

Select the tab Secrets and click on the link New Client Secret

Give it a description and when it should expire.

Click on the button [Add]

Now, this is important: only now the secret VALUE is shown. Make sure you copy it and store is safely for now. We need it later.

Trust app in SharePoint

Next, we need to tell SharePoint that is app is to be trusted.

First find the application Id of this App Registration. Navigate to the tab Overview and copy the Application (client) ID.

Navigate to the SharePoint Admin site and open the page on this url:
htps://<tenantname>-admin.sharepoint.com/_layouts/15/appinv.aspx

Copy the Application ID in the field App Id and click on the button [Lookup]

When found, add the name of your Function App in the field App Domain

And copy the XML Permission Request:

<AppPermissionRequests AllowAppOnlyPolicy="true">
    <AppPermissionRequest Scope="http://sharepoint/social/tenant" Right="FullControl" />
    <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
</AppPermissionRequests>

Click on the button [Create] and SharePoint will ask you if the app should be trusted. Please, confirm.

Azure Key Vault

One more thing to configure before we head back to our Function App. To store your secrets and certificates safely, we need a key vault. But to access the key vault you’ll need permissions, right? That’s where the Managed Identity comes in! Voila. (and I have another Wow! for you later)

Create a Key Vault, give it a name and the correct region

When created, navigate to your Key Vault and select Access Policies

Click on the link Add Access Policy

For Secret Permissions, select Get

Select Principal : Select your Managed Identity !

Click on the button [Add]

Advantage of this, is that you do not create additional credentials and logic to your code to access the Key Vault.

Key Vault Secret

Now we only need to create a Key Vault secret to store the App Registration secret value.

Navigate to Secrets and clink on the link Generate/Import

Click on the button [Create]

Azure Function

Finally, we can create our function in the Azure Function App.

To make our script awesome, we add some configuration.

Navigate to the Function App and select Configuration

You’ll see the Application Settings and we are adding our own. This is to eliminate hard coded values from the script.

Add the following Application Settings:

Note: the value for Red_APP_SECRET is a special one. If you store the Secret Identifier from the Key Vault secret, then the Function App automatically connects and retrieves the secret value. No need to create code for this!! (Now, you can say WOW!)

To get the Secret Identifier, navigate back to your Key Vault, select your secret and current version:

Required PowerShell Modules

Ow, one more thing (always wanted to say this). Our PowerShell script will both use the Az and Pnp.PowerShell modules. We need to tell this to our Function App. It will then install these modules in the background.

In your Function App, select App files.

From the dropdown list, select requirements.psd1

Configure it as follows:

Save it all.

Add Code to the Function

We want to have a function that runs every now and then. This would be a Timer Trigger function.

Navigate to Functions and click on the link Create

In the Create panel, leave the Development environment to Develop in portal

Select the template Timer Trigger

Give it a proper name and set the schedule once in every 4 hours:

Click on the button [Create]

In the created function, click on Code + Test

Paste the PowerShell script into the editor and save it. What PowerShell script? you say. No worries, you grab the complete script here.

Some code snippets I want to explain:

There are several functions for mainly getting access tokens and calling the graph or REST APIs:

Retrieving Azure AD Group Membership:

function Get-MembersDLGroup() {
    $members = $null

    $uri = "https://graph.microsoft.com/v1.0/groups/$groupId/transitiveMembers/microsoft.graph.user"

    $isNextLink = $false
    do {

        $result = Invoke-RestMethod -Method Get -Headers $authHeader -Uri $uri
        if( $result.value ) {
            $members += $result.value
        }

        if( $result.'@odata.nextLink' ) {
            $isNextLink = $true
            $uri = $result.'@odata.nextLink'
        }
        else {
            $isNextLink = $false
        }

    }  while( $isNextLink ) 
    
    $members
}

The Graph call is here to your Azure group and the transitiveMembers will give you all members of that group and of all nested groups! Since I only want user accounts, I also added the filter microsoft.graph.user.

And, as the Graph will return results in batches, I also check for that. If there are more results a #odata.nextLink is present.

Retrieving SharePoint User Profiles

function Get-SPOEmployeeProfiles( [string]$urlDefaultSite) {
    $profiles = $null

    $header = @{    
        'Accept'        = 'application/json'
        'odata'         = 'verbose'
        'Content-Type'  = 'application/json'
        'Authorization' = 'Bearer ' + $spoToken
    }
    
    $searchUrl = "$urlDefaultSite/_api/search/query?querytext='" + $searchProperty + ":true'&sourceid='b09a7990-05ea-4af9-81ef-edfab16c4e31'"
    $result = Invoke-RestMethod -Method Get -Headers $header -Uri $searchUrl
    if ( $result ) {
        if( $result.PrimaryQueryResult.RelevantResults.RowCount -gt 0 ) {
            $profiles = $result.PrimaryQueryResult.RelevantResults.Table.Rows
        }
    }

    $profiles
}

I use SharePoint Search REST API to query all User Profiles (sourceId=’b09a7990-05ea-4af9-81ef-edfab16c4e31′) and having the value present (IsFullTimeEmployee) using the Search Managed Property.

Updating SharePoint User Profile

    #Set New Employees
    [int]$added=0
    $membersDLGroup | ForEach-Object {
        $userAccount = $_.userPrincipalName
        $userId  = $_.id.ToLower() 
        $userProfile = $workingProfiles | Where-Object { $_.ID -eq $userId }
        if( -Not $userProfile ) {
            $added++
            Write-Host "ADDING - Updating profile for $userAccount"
            Set-PnPUserProfileProperty `
                -Connection $spoConnection `
                -Account $userAccount `
                -PropertyName $profileProperty `
                -Value $True
        }
    }
    Write-Host "Updated $added new employees"

I use the PnP PowerShell cmdlet Set-PnPUserProfileProperty to update the user profile. Easypeasy.

Results