Sharepoint Online and Azure Datafactory using Managed Identity

Let’s face it, Microsoft’s documentation on using Sharepoint as a data source (or sink) in ADF is pretty bad. And it doesn’t even describe how to use the Managed Identity of ADF, who still wants to register separate app credentials? Noooooo you don’t.

So here’s an example how to use Managed Identity to read a json file from a given SpO site, using minimal permissions given to ADF.

  1. Enable MI in datafactory, I’m assuming you know how to do this.
  2. Give ADF Sites.Selected Graph permissions, e.g. like this:
Param(
    [Parameter(Mandatory=$true)][String]$displayName="{NAMEOFADFINSTANCE}",
    [Parameter(Mandatory=$true)][String]$role="Sites.Selected"
)
Connect-AzureAD 
$Msi = (Get-AzureADServicePrincipal -Filter "displayName eq '$displayName'")
Start-Sleep -Seconds 10
$baseSPN = Get-AzureADServicePrincipal -Filter "appId eq '00000003-0000-0000-c000-000000000000'"
$AppRole = $baseSPN.AppRoles | Where-Object {$_.Value -eq $role -and $_.AllowedMemberTypes -contains "Application"}
New-AzureAdServiceAppRoleAssignment -ObjectId $Msi.ObjectId -PrincipalId $Msi.ObjectId -ResourceId $baseSPN.ObjectId -Id $AppRole.Id
$Msi.AppId
  1. Go to https://yourtenant.sharepoint.com/sites/yoursite/_api/site/id and copy the Edm Guid:
get sharepoint site ID / guid
  1. Go to https://developer.microsoft.com/en-us/graph/graph-explorer. Log in at the top right using a user with sufficient permissions, set the mode to POST, add the EDM guid in the URL and create the request body as follows (the id in the body can be found back in step 2, $Msi.AppId)
authorize managed identity on sharepoint site
  1. Create a REST linked service in ADF with Managed Identity auth:
create graph rest linked identity
  1. Create data source in ADF of type REST
  1. Replace the “uri” with the direct url of the file you want to read (or use an alternative method). Example uri:
    https://graph.microsoft.com/v1.0/sites/ee614b6d-de15-4101-1c12-14e4cd3186a9/drive/root:/General/Input_files/test.json:/content

Failed to retrieve dynamic inputs in a logic app

For those unfortunate enough to also have an admin rename/move their Sharepoint site….you’ll be seeing errors in your logic app until you fix the URL. Sharing for those googling:

Failed to retrieve dynamic inputs. Error details: 'Resource provider 'Microsoft.Web' returned a redirect response with status code '308'. This response was blocked due to security concerns.'

Error executing the api <list url>. More diagnostic information: x-ms-client-request-id is xyz.

Unable to initialize operation

Unable to initialize operation details for swagger based operation - Get_item. Error details - Incomplete information for operation 'Get_item'

-2147024891, System.UnauthorizedAccessExceptionAccess is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Multi threading in ADDRS

I’ve added basic multi-threading to ADDRS: https://www.powershellgallery.com/packages/ADDRS/1.1.8

This solely ensures that the most compute intensive task (caching sizes/performance) is not repeated between jobs. You’ll still have to handle running multiple jobs using your own preferred method, e.g. foreach -parallel, runspaces or start-job. Example:


$scriptBlock = {
    Param(
        $vm,
        $measurePeriodHours,
        $workspace,
        $token
    )
    import-module ADDRS -force
    Login-AzAccount -AccessToken $token.Token -AccountId $token.UserId -Tenant $token.TenantId
    set-vmRightSize -doNotCheckForRecentResize -targetVMName $vm.Name -domain "lieben.nu" -measurePeriodHours $measurePeriodHours -workspaceId $workspace.CustomerId -Verbose -maintenanceWindowStartHour 22 -maintenanceWindowLengthInHours 3 -maintenanceWindowDay 6
}

Publishing an MSIX as CIM to AVD in a Pipeline

I wanted to put this out there as it felt like a nifty way to pipeline AVD MSIX files into AVD without any user interaction (other than a pipeline kicking off the script).

https://github.com/jflieben/assortedFunctionsV2/blob/main/publish-MSIXPackageToHostpool.ps1

Basically, above will grab the MSIX file from a known Azure Fileshare (after mounting). It’ll read the MSIX’s primary CIM file for meta data, use the Azure Rest API to add it to the hostpool and then updates a param file of an ARM template which can be used to e.g. update the appgroup in Azure.

You’ll need some background knowledge to re-use above in your specific situation 🙂

Code example:

#create the MSIX package object in the hostpool. Ensure the lastUpdated value is always unique otherwise it will fail to overwrite an existing package with the same value
$apiPostData = @{
    "properties" = @{
        "displayName" = if($packageMeta -match "(?<=<DisplayName>)(.*?)(?=<\/DisplayName>)"){$matches[1]}else{Throw "No display name found in AppManifest"}
        "imagePath" = $imagePath
        "isActive" = $True
        "isRegularRegistration" = $False
        "lastUpdated" = (get-itemproperty $packageFolder.FullName).LastWriteTimeUtc.AddSeconds((Get-Random -Minimum "-150" -Maximum 150)).ToString("yyyy-MM-ddThh:mm:ss")
        "packageApplications" = $packageApplications
        "packageDependencies" = @()
        "packageFamilyName" = "$($packageShortName)_$($packageFamily)"
        "packageName" = $packageShortName
        "packageRelativePath" = "\MSIXPackages\$($packageFolder.Name)"
        "version" = $packageVersion
    }
}

#send the actual API request to register the package in the hostpool using the pipeline serviceprincipal
try{
    $context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext
    $token = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, $context.Environment, $context.Tenant.Id.ToString(), $null, [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, $null, "https://management.azure.com")          
    Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$((get-azcontext).Subscription.id)/resourcegroups/rg-common-$($environment)-weeu-01/providers/Microsoft.DesktopVirtualization/hostPools/vdhp-common-$($environment)-weeu-01/msixPackages/$($packageFolder.Name)?api-version=2021-07-12" -Method PUT -UseBasicParsing -ContentType "application/json" -Body ($apiPostData | convertto-json -Depth 15) -Headers @{"Authorization"="Bearer $($token.AccessToken)"} -ErrorAction Stop
}catch{
    Write-Output $_
    closeCIMSession
    Throw
}

OnedriveMapper V5 required update!

Microsoft is planning to modify the Office 365 sign in process slightly by September 15th or October 1st and has kindly supplied advance notice and support for OnedriveMapper, but only for V3.29+ and V5.15+

So if you’re on a lower version -> update asap!

The new login interruption looks like this:

OnedriveMapper V5 @ GitHub

OnedriveMapper V3 @ GitHub

Microsoft 365, Azure, Automation & Code