Highlight
Do you want to query SharePoint and get the files or list information in a secure way using Azure’s Service Principals or Managed Identities? Here is a short guide on how to do it.
Why using Graph API us better and what changed from 2019?
In 2021 Microsoft published this blog entry (https://devblogs.microsoft.com/microsoft365dev/controlling-app-access-on-specific-sharepoint-site-collections/) where they show the newest way to connect to SharePoint sites in a secure way using Graph permissions. This was previously possible using Sites.*.All permissions, but the issue was that Sites.ReadWrite.All or Sites.FullControl.All was highly privileged permissions, meaning a service principal could literally read any site on your SharePoint. As we know there are many sites that should not be accessed by dev teams like accounting, financial controlling, HR, etc.
Now graph allows new permission called Sites.Selected which means you can use graph endpoints, but access the sites that your service principal was excplicitly granted access to.
This blog entry will describe how to assign these permissions using PowerShell so that you can automate this process.
Permissions
In order to use Graph API your service principal will need two permissions assigned
- On Microsoft Graph API (in Azure Active Directory)
- On SharePoint Site via
- Graph call performed by Site admin
- Old interface to grant SharePoint app permissions
All ways are described in steps below.
Step 1 - assign graph API Sites.Selected permissios
Option #1 - Azure Portal route
- Navigate to Azure AD
![]()
- Find your app registration
![]()
- Add new Graph Permissions (Note: This can be done by App Owner)
![]()
- Find Sites.Selected permission and click add
![]()
- Notice that the permission was added but is NOT active because it requires admin consent. (Note: This step must be performed by Azure AD Global Administrator)
![]()
- Verify that the permission was granted
![]()
PowerShell route
$principalNames = "sp-demo2"
$RequiredPermissions = "Sites.Selected"
foreach ($principalName in $principalNames) {
Write-Host "Assigning privileges for $principalName" -ForegroundColor Green
$sp = Get-AzureADServicePrincipal -SearchString $principalName
if ($sp.AppId -eq $null) {
$errorMessage = "Principal $principalName was not found. Please check principal name."
Write-Host $errorMessage -ForegroundColor Magenta
Write-Error $errorMessage
} else {
$PrincipalId = $sp.AppId
$Graph = Get-AzureADServicePrincipal -Filter "AppId eq '00000003-0000-0000-c000-000000000000'"
$Principal = Get-AzureADServicePrincipal -Filter "AppId eq '$PrincipalId'"
$RequiredPermissions | %{
$permission = $_
Write-Output "Assigning permission $permission"
$Role = $Graph.AppRoles | Where-Object { $_.Value -eq $permission } | Select-Object -First 1
New-AzureADServiceAppRoleAssignment `
-Id $Role.Id `
-PrincipalId $Principal.ObjectId `
-ResourceId $Graph.ObjectId `
-ObjectId $Principal.ObjectId
}
}
}
Step 2 - Assign SharePoint Site Permissions
You need to assign following read or write permissions to service principal using Permissions API. There are two ways to do it.
- via Graph API /sites/{siteId}/permissionss endpoint
- via App Permission Panel (old way)
Prerequisites
- Get SharePoint site ID by opening URL
https://<domain>.sharepoint.com/sites/<site_name>/_api/site/id(replace domain and site_name with your site and domain)![]()
Option #1 - Graph Endpoint way
POST https://graph.microsoft.com/v1.0/sites/{siteId}/permissions
Content-Type: application/json
{
"roles": ["write"],
"grantedToIdentities": [{
"application": {
"id": "89ea5c94-7736-4e25-95ad-3fa95f62b66e",
"displayName": "Foo App"
}
}]
}
Documentation links
- Site Permissions endpoint https://learn.microsoft.com/en-us/graph/api/resources/permission
- Get site permission https://learn.microsoft.com/en-us/graph/api/site-get-permission
- List site permissions https://learn.microsoft.com/en-us/graph/api/site-list-permission
- Create site permissions https://learn.microsoft.com/en-us/graph/api/site-update-permission
- Update site permissions https://learn.microsoft.com/en-us/graph/api/site-post-permission
- Delete site permissions https://learn.microsoft.com/en-us/graph/api/site-delete-permission
PowerShell
# MS Graph PowerShell Modules Docs: https://docs.microsoft.com/en-us/powershell/microsoftgraph/overview
# Installation (Run those commands - one time setup)
# Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
# Install-Module Microsoft.Graph -Scope CurrentUser
# Import-Module Microsoft.Graph
# INPUTS
$siteId = "cfe98761-6d30-4fc9-ab6e-90e3eb304c03"
$appDisplayName = "sp-demo2"
$permission = "read"
# CONNECT
Connect-MgGraph -Scopes "Sites.Read.All","Sites.FullControl.All"
Connect-AzureAD
$sp = Get-AzureADServicePrincipal -SearchString $appDisplayName
$appId = $sp.AppId
$site = Get-MGSite -SiteId $siteId
# TEST - Get Current Permissions
Write-Host "Before the update" -ForegroundColor Green
$permissions = Get-MgSitePermission -SiteId $siteId
$permissions | Select -ExpandProperty GrantedToIdentities | Select -ExpandProperty Application
# ADD - new Service Principal
$body = @{
'roles' = @(
$permission
);
'grantedToIdentities' = @(
@{
'application' = @{
'id' = $appId
'displayName' = $appDisplayName
}
}
);
}
$bodyJson = $body | ConvertTo-Json -Depth 10 -Compress
Invoke-MgGraphRequest `
-Method POST `
-Uri "v1.0/sites/$SiteId/permissions" `
-Body $bodyJson
# TEST - Get Updated Permissions
Write-Host "After the update" -ForegroundColor Green
$permissions = Get-MgSitePermission -SiteId $siteId
$permissions | Select -ExpandProperty GrantedToIdentities | Select -ExpandProperty Application
Option #2 - Using App Consent Screen
White it’s not a recommended way, it seems to be working too for Graph API access. App consent is defined here https://learn.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs
- Open Site app insite URL
https://<domain>.sharepoint.com/sites/<site_name>/_layouts/15/appinv.aspx - Fill in the form
![]()
- Use following payload (use proper permissions Reader/Contributor/FullControl)
<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/sites/<site_name>" Right="Reader" /> </AppPermissionRequests>- Reader
- Contributor
- FullControl
- Trust the app
![]()
DONE! Time for testing
Now you can use two graph endpoints to get the data
- Sites https://learn.microsoft.com/en-us/graph/api/resources/site - to query all website information based on assigned permissions
- Drive https://learn.microsoft.com/en-us/graph/api/resources/drive - to upload / download files on SharePoint sites
POSTMAN call
Example 1: Logic apps with Service Principal (hardcoded for demo)
- Logic App design
![]()
- Code the cope into code view
{ "definition": { "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#", "actions": { "For_each_Site_Drive": { "actions": { "Condition": { "actions": { "For_each_Drive_Item": { "actions": { "Create_blob_(V2)": { "inputs": { "body": "@binary(body('HTTP_-_SharePoint_Get_Drive_Item_Content'))", "headers": { "Content-Type": "@{body('HTTP_-_SharePoint_Get_Drive_Item_Content')?['$content-type']}", "ReadFileMetadataFromServer": true }, "host": { "connection": { "name": "@parameters('$connections')['azureblob']['connectionId']" } }, "method": "post", "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('azsharepoint'))}/files", "queries": { "folderPath": "hr-files/logic-app-sp", "name": "@{body('HTTP_-_SharePoint_Get_Drive_Item_by_ID')?['name']}", "queryParametersSingleEncoded": true } }, "runAfter": { "HTTP_-_SharePoint_Get_Drive_Item_Content": [ "Succeeded" ] }, "runtimeConfiguration": { "contentTransfer": { "transferMode": "Chunked" } }, "type": "ApiConnection" }, "HTTP_-_SharePoint_Get_Drive_Item_Content": { "inputs": { "method": "GET", "uri": "@{items('For_each_Drive_Item')?['@microsoft.graph.downloadUrl']}" }, "runAfter": { "HTTP_-_SharePoint_Get_Drive_Item_by_ID": [ "Succeeded" ] }, "runtimeConfiguration": { "contentTransfer": { "transferMode": "Chunked" } }, "type": "Http" }, "HTTP_-_SharePoint_Get_Drive_Item_by_ID": { "inputs": { "authentication": { "audience": "https://graph.microsoft.com", "clientId": "@body('Set_Client_Credentials')?['ClientId']", "secret": "@body('Set_Client_Credentials')?['ClientSecret']", "tenant": "@body('Set_Client_Credentials')?['TenantId']", "type": "ActiveDirectoryOAuth" }, "method": "GET", "uri": "https://graph.microsoft.com/v1.0/sites/@{variables('SiteId')}/drives/@{items ('For_each_Site_Drive')?['id']}/items/@{items('For_each_Drive_Item')?['id']}" }, "runAfter": {}, "type": "Http" } }, "foreach": "@body('HTTP_-_SharePoint_Get_Drive_Items')?['value']", "runAfter": { "HTTP_-_SharePoint_Get_Drive_Items": [ "Succeeded" ] }, "type": "Foreach" }, "HTTP_-_SharePoint_Get_Drive_Items": { "inputs": { "authentication": { "audience": "https://graph.microsoft.com", "clientId": "@body('Set_Client_Credentials')?['ClientId']", "secret": "@body('Set_Client_Credentials')?['ClientSecret']", "tenant": "@body('Set_Client_Credentials')?['TenantId']", "type": "ActiveDirectoryOAuth" }, "method": "GET", "uri": "https://graph.microsoft.com/v1.0/sites/@{variables('SiteId')}/drives/@{items ('For_each_Site_Drive')?['id']}/root/children" }, "runAfter": {}, "type": "Http" } }, "expression": { "and": [ { "equals": [ "@items('For_each_Site_Drive')?['name']", "Documents" ] } ] }, "runAfter": {}, "type": "If" } }, "foreach": "@body('HTTP_-_SharePoint_Get_Drives')?['value']", "runAfter": { "HTTP_-_SharePoint_Get_Drives": [ "Succeeded" ] }, "type": "Foreach" }, "For_each_Site_List": { "actions": { "HTTP_-_SharePoint_List_Files": { "inputs": { "authentication": { "audience": "https://graph.microsoft.com", "clientId": "@body('Set_Client_Credentials')?['ClientId']", "secret": "@body('Set_Client_Credentials')?['ClientSecret']", "tenant": "@body('Set_Client_Credentials')?['TenantId']", "type": "ActiveDirectoryOAuth" }, "method": "GET", "uri": "https://graph.microsoft.com/v1.0/sites/@{variables('SiteId')}/lists/@{items('For_each_Site_List')? ['id']}/items" }, "runAfter": {}, "type": "Http" } }, "foreach": "@body('HTTP_-_SharePoint_Get_Lists')?['value']", "runAfter": { "HTTP_-_SharePoint_Get_Lists": [ "Succeeded" ] }, "type": "Foreach" }, "HTTP_-_Get_Site_Id": { "inputs": { "authentication": { "audience": "https://graph.microsoft.com", "clientId": "@body('Set_Client_Credentials')?['ClientId']", "secret": "@body('Set_Client_Credentials')?['ClientSecret']", "tenant": "@body('Set_Client_Credentials')?['TenantId']", "type": "ActiveDirectoryOAuth" }, "method": "GET", "uri": "https://graph.microsoft.com/v1.0/sites/adammarczak.sharepoint.com:/sites/hr" }, "runAfter": { "Set_Client_Credentials": [ "Succeeded" ] }, "type": "Http" }, "HTTP_-_SharePoint_Get_Drives": { "inputs": { "authentication": { "audience": "https://graph.microsoft.com", "clientId": "@body('Set_Client_Credentials')?['ClientId']", "secret": "@body('Set_Client_Credentials')?['ClientSecret']", "tenant": "@body('Set_Client_Credentials')?['TenantId']", "type": "ActiveDirectoryOAuth" }, "method": "GET", "uri": "https://graph.microsoft.com/v1.0/sites/@{variables('SiteId')}/drives" }, "runAfter": { "For_each_Site_List": [ "Succeeded" ] }, "type": "Http" }, "HTTP_-_SharePoint_Get_Lists": { "inputs": { "authentication": { "audience": "https://graph.microsoft.com", "clientId": "@body('Set_Client_Credentials')?['ClientId']", "secret": "@body('Set_Client_Credentials')?['ClientSecret']", "tenant": "@body('Set_Client_Credentials')?['TenantId']", "type": "ActiveDirectoryOAuth" }, "method": "GET", "uri": "https://graph.microsoft.com/v1.0/sites/@{variables('SiteId')}/lists" }, "runAfter": { "Initialize_variable_-_Site_Id": [ "Succeeded" ] }, "type": "Http" }, "Initialize_variable_-_Site_Id": { "inputs": { "variables": [ { "name": "SiteId", "type": "string", "value": "@{split(body('HTTP_-_Get_Site_Id')?['id'],',')[1]}" } ] }, "runAfter": { "HTTP_-_Get_Site_Id": [ "Succeeded" ] }, "type": "InitializeVariable" }, "Set_Client_Credentials": { "inputs": { "content": { "ClientId": "<id_here>", "ClientSecret": "<key_here_move_to_AKV_later>", "TenantId": "marczak.io" }, "schema": { "properties": { "ClientId": { "type": "string" }, "ClientSecret": { "type": "string" }, "TenantId": { "type": "string" } }, "type": "object" } }, "runAfter": {}, "type": "ParseJson" } }, "contentVersion": "1.0.0.0", "outputs": {}, "parameters": { "$connections": { "defaultValue": {}, "type": "Object" } }, "triggers": { "manual": { "inputs": {}, "kind": "Http", "type": "Request" } } }, "parameters": { "$connections": { "value": { "azureblob": { "connectionId": "/subscriptions/f73706f8-c55b-42b7-9d31-6fc8e0d24146/resourceGroups/az-sharepoint/providers/ Microsoft.Web/connections/azureblob", "connectionName": "azureblob", "connectionProperties": { "authentication": { "type": "ManagedServiceIdentity" } }, "id": "/subscriptions/f73706f8-c55b-42b7-9d31-6fc8e0d24146/providers/Microsoft.Web/locations/westeurope/managedApis/ azureblob" } } } } } - Update values
![]()
Example 2: Data Factory with Managed Identity
- Graphical design (iterate over files and copy them to Blob storage)
![]()
- Code
{ "name": "PL_SHAREPOINT_SERVICE_PRINCIPAL_DEMO", "properties": { "activities": [ { "name": "HTTP - SharePoint Get Drive Items", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": "https://graph.microsoft.com/v1.0/sites/cfe98761-6d30-4fc9-ab6e-90e3eb304c03/drives/ b!YYfpzzBtyU-rbpDj6zBMAzynDGDfj_pLmKL5ZkL_4LBuUyOwmNYrRYKg9JqsFX19/root/children", "method": "GET", "authentication": { "type": "ServicePrincipal", "userTenant": "3266fd7c-39d3-44b8-9551-b0ffbf68c764", "username": { "value": "@variables('ClientId')", "type": "Expression" }, "resource": "https://graph.microsoft.com", "password": { "type": "AzureKeyVaultSecret", "store": { "referenceName": "AKV_LS", "type": "LinkedServiceReference" }, "secretName": "ClientSecret" } } } }, { "name": "For each Drive Item", "type": "ForEach", "dependsOn": [ { "activity": "HTTP - SharePoint Get Drive Items", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "items": { "value": "@activity('HTTP - SharePoint Get Drive Items').output.value", "type": "Expression" }, "isSequential": false, "activities": [ { "name": "HTTP - SharePoint Get Drive Item by ID", "type": "WebActivity", "dependsOn": [], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "url": { "value": "https://graph.microsoft.com/v1.0/sites/cfe98761-6d30-4fc9-ab6e-90e3eb304c03/drives/ b!YYfpzzBtyU-rbpDj6zBMAzynDGDfj_pLmKL5ZkL_4LBuUyOwmNYrRYKg9JqsFX19/items/@{item().id}", "type": "Expression" }, "method": "GET", "authentication": { "type": "ServicePrincipal", "userTenant": "3266fd7c-39d3-44b8-9551-b0ffbf68c764", "username": { "value": "@variables('ClientId')", "type": "Expression" }, "resource": "https://graph.microsoft.com", "password": { "type": "AzureKeyVaultSecret", "store": { "referenceName": "AKV_LS", "type": "LinkedServiceReference" }, "secretName": "ClientSecret" } } } }, { "name": "Save File", "type": "Copy", "dependsOn": [ { "activity": "HTTP - SharePoint Get Drive Item by ID", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "BinarySource", "storeSettings": { "type": "HttpReadSettings", "requestMethod": "GET" }, "formatSettings": { "type": "BinaryReadSettings" } }, "sink": { "type": "BinarySink", "storeSettings": { "type": "AzureBlobStorageWriteSettings" } }, "enableStaging": false }, "inputs": [ { "referenceName": "DS_HTTP_BINARY", "type": "DatasetReference", "parameters": { "URL": { "value": "@activity('HTTP - SharePoint Get Drive Item by ID').output['@microsoft.graph. downloadUrl']", "type": "Expression" } } } ], "outputs": [ { "referenceName": "DS_BLOB_BINARY", "type": "DatasetReference", "parameters": { "FileName": { "value": "@activity('HTTP - SharePoint Get Drive Item by ID').output.name", "type": "Expression" } } } ] } ] } } ], "variables": { "ClientId": { "type": "String", "defaultValue": "e02f3944-2f40-436e-a261-38ed9bcabc80" }, "ClientSecret": { "type": "String", "defaultValue": "guo7Q~0ISDRVJDXYxtIihcgdNkj4ZtvxLkS2j" }, "TenantId": { "type": "String", "defaultValue": "marczak.io" } }, "annotations": [], "lastPublishTime": "2022-06-08T07:54:06Z" }, "type": "Microsoft.DataFactory/factories/pipelines" }
This is currently the best way to query graph using application accounts (service principals and managed identities). Hope it will be useful for you!
