Category Archives: Automation

Configuring the Windows 10 Pro Lock Screen using MEM

Windows 10 Enterprise supports a specific MEM policy to configure the Windows 10 Lock screen for End-users. If you’re unlucky enough to be on a lesser Windows 10 version, you’ll need to trick the OS into thinking the lock screen is modified by the user instead of through a policy.

Here’s a simple ARM template for blob storage and a PS script to deploy through MEM in user context to configure the lock screen of your users:

1-click ARM template

And the script itself (don’t forget to configure the image URL):

    Sets custom lock screen based on file in an Azure Storage Blob container
    See blob template to automatically configure a blob container:
    filename: set-windows10LockScreen.ps1
    author: Jos Lieben
    created: 13/05/2021

$changedDate = "2021-05-13"
$lockscreenFileURL = "" #this is the full URL to the desired lock screen image

Start-Transcript -Path (Join-Path -Path $Env:TEMP -ChildPath "set-windows10LockScreen.log")

$tempFile = (Join-Path $Env:TEMP -ChildPath "img100.jpg")

    Write-Output "downloading lock screen file from $lockscreenFileURL"
    Invoke-WebRequest -Uri $lockscreenFileURL -UseBasicParsing -Method GET -OutFile $tempFile
    Write-Output "file downloaded to $tempFile"
    Write-Output "Failed to download file, aborting"
    Write-Error $_ -ErrorAction SilentlyContinue

[Windows.System.UserProfile.LockScreen,Windows.System.UserProfile,ContentType=WindowsRuntime] | Out-Null
Add-Type -AssemblyName System.Runtime.WindowsRuntime

$asTaskGeneric = ([System.WindowsRuntimeSystemExtensions].GetMethods() | ? { $_.Name -eq 'AsTask' -and $_.GetParameters().Count -eq 1 -and $_.GetParameters()[0].ParameterType.Name -eq 'IAsyncOperation`1' })[0]
Function Await($WinRtTask, $ResultType) {
    $asTask = $asTaskGeneric.MakeGenericMethod($ResultType)
    $netTask = $asTask.Invoke($null, @($WinRtTask))
    $netTask.Wait(-1) | Out-Null

Function AwaitAction($WinRtAction) {
    $asTask = ([System.WindowsRuntimeSystemExtensions].GetMethods() | ? { $_.Name -eq 'AsTask' -and $_.GetParameters().Count -eq 1 -and !$_.IsGenericMethod })[0]
    $netTask = $asTask.Invoke($null, @($WinRtAction))
    $netTask.Wait(-1) | Out-Null

[Windows.Storage.StorageFile,Windows.Storage,ContentType=WindowsRuntime] | Out-Null
	$image = Await ([Windows.Storage.StorageFile]::GetFileFromPathAsync($tempFile)) ([Windows.Storage.StorageFile])
    Write-Output "Image loaded from $tempFile"
}catch {
    Write-Output "Failed to load image from $tempFile"
    Write-Error $_ -ErrorAction SilentlyContinue
    Write-Output "Setting image as lock screen image"
    AwaitAction ([Windows.System.UserProfile.LockScreen]::SetImageFileAsync($image))
    Write-Output "$tempFile configured as lock screen image"
    Remove-Item -Path $tempFile -Force -Confirm:$False
    Write-Output "Failed to set lock screen image"
    Write-Error $_ -ErrorAction SilentlyContinue

Write-Output "Script complete"


Deploying in user context:

Deploying a service principal to (CSP) child tenants

Cloud Solution Providers, or sometimes other types of Managed Service Providers often have to manage a large number of tenants. Ideally, they do their ‘Infrastructure As Code’.

Using various API’s to manage tenants is best done using a Service Principal instead of a user (MFA, lifecycle, etc).

Recently, I was tasked to provide a deployment method of a Service Principal (multi-tenant) to all child tenants of an MSP, including programmatically granting various Graph API permissions. The Graph endpoint for this (oauth2PermissionGrants) is still in Beta, but the other methods I wrote about in the past are not as reliable so we’re using the Beta endpoint.

The linked example script creates an SPN and grants AuditLog.Read.All. If you’re an MSP/CSP, you’ll probably want to capture the tenant ID’s you’re installing into, so you can easily administer these tenants centrally using your main multi-tenant SPN.

Moving forwards, you won’t need an admin user / service account in the tenants you manage anymore, at least for the API’s that support SPN’s.

Note: to completely remove module dependencies / login, check my independent token function.

Upserting Data to Azure SQL DB using PowerShell

For a project involving Azure Security I needed to store fairly large amounts of data in an Azure PaaS database using PowerShell.

If a row already exists, I want to do an UPDATE command, otherwise an INSERT command, also known as an UPSERT in SQL.

It should also use parameters to avoid issues with quotes in fields, and should convert PowerShell null’s/empty objects to the SQL equivalent.

The following function is what resulted, it only supports a single WHERE clause, but should be easy to enhance for those looking to improve 🙂

function invoke-sqlUpsert{
        [Array]$values, # example: @(@{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar})
        [PSObject]$primaryKey, # example: @{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar}

    $sqlQuery = "BEGIN TRANSACTION;
    UPDATE $tableName
        SET "
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)=@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
    $sqlQuery = "$($sqlQuery)
        WHERE $($primaryKey.column) = @$($primaryKey.column);
    IF @@ROWCOUNT = 0
            INSERT INTO $tableName ($($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
    $sqlQuery = "$($sqlQuery)
            VALUES (@$($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$($sqlQuery)@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
    $sqlQuery = "$($sqlQuery));

    $sqlCmd=new-object system.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn)
    $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($primaryKey.column)",$primaryKey.dataType))) | OUT-NULL
    $sqlCmd.Parameters[0].Value = $primaryKey.value
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($values[$i].column)",$values[$i].dataType))) | OUT-NULL
        $sqlCmd.Parameters[$i+1].Value = $values[$i].value

    for($i=0;$i -lt $sqlCmd.Parameters.count;$i++){
        if($sqlCmd.Parameters[$i].Value -eq $null){
            $sqlCmd.Parameters[$i].Value = [System.DBNull]::Value
    if($sqlCmd.ExecuteNonQuery() -ne 1){
        Throw $_

An example of how to connect from an Azure Function before using this command:

using namespace System.Data.SqlClient
using namespace System.Net
$msi_authenticationResult = Invoke-RestMethod -Method Get -Headers @{'Secret' = $env:MSI_SECRET} -Uri ($env:MSI_ENDPOINT +'?resource=')
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source =; Initial Catalog = yourdatabase"
$sqlConn.AccessToken = $msi_authenticationResult.access_token

Exchange Hybrid lockdown to O365 IP’s only

With the recent Exchange vulnerabilities comes a moment to reflect on further ways to reduce the attach surface of Exchange Servers.

Many organizations still host an Exchange Server solely to maintain a hybrid connectivity link to Office 365. The server therefore has to be publicly accessible, but only to Microsoft. Often this is not the case.

If you don’t have a professional firewall to restrict traffic to only that coming from Microsoft, you can also do so at the IIS level. Microsoft publishes a list of IP’s they use here:

We can then take that source address data and add each IP in it to an Allow entry at the global level in IIS using PowerShell:

$allRanges = @("fe80::946:a60c:3d5:ec11%3","","::1")
$o365IPs = Invoke-RestMethod -Method GET -UseBasicParsing -Uri "" 
$o365IPs | % {$_.ips | %{if($allRanges -notcontains $_){$allRanges += $_}}}
$allRanges | % {
        $payLoad = @{ipAddress=$_.Split("/")[0];allowed="true";subnetMask=$(([ipaddress]([double]4294967296-(1-shl32-$($_.Split("/")[1])))).IPAddressToString);}
        $payLoad = @{ipAddress=$_;allowed="true";}
    try{$null = Add-WebConfigurationProperty  -Filter 'system.webServer/security/ipSecurity' -PSPath "IIS:\" -Name "." -Value $payLoad -ErrorAction SilentlyContinue}catch{$Null}

Finally, set IIS’s IP Address and Domain restriction mode to Deny:

note: you can add additional ranges to $allRanges as needed for internal management, monitoring etc.

Enable WVD diagnostics for Azure Monitor using ARM Templates

Now that Azure Monitor has support for Windows Virtual Desktop, I wanted to enable this for all WVD workspaces, host pools and application groups.

Christiaan beautifully described how to do this through the Azure Portal, but of course, in an enterprise environment we don’t click around, we define everything as Code!

This example ARM template will enable the Diagnostics setting for the hostpool, workspace and application group vectors. To get data from VM’s (hosts), use your preferred method (plenty of examples exist already and depend on your setup).

After applying / including / redeploying this ARM template in your WVD environment, you’ll be able to see metrics coming in to the Azure Monitor Insights dashboard for WVD 🙂