Category Archives: Automation

Correct SessionDesktop friendlyname using AVD Rest API

When you deploy an Azure Virtual Desktop application group with the default desktop through ARM, the FriendlyName attribute is not respected, and remains at the default value of SessionDesktop.

This is easy to correct manually in the portal, but as I don’t want my admins having modify rights there, I introduced an extra pipeline step (YAML/Azure DevOps) to uses the Az module’s REST command to correctly set the FriendlyName of the SessionDesktop:

    - task: AzureCLI@2
      displayName: Correct app name
      inputs:
        azureSubscription: ${{ parameters.serviceConnection }}
        scriptType: ps
        scriptLocation: inlineScript
        inlineScript: |
          az rest --method PATCH --uri 'https://management.azure.com/subscriptions/${{ parameters.subscriptionId }}/resourceGroups/${{ parameters.resourceGroupName }}/providers/Microsoft.DesktopVirtualization/applicationGroups/ag-myappgroupname-01/desktops/SessionDesktop?api-version=2021-01-14-preview' --body '{""properties"":{""description"": ""Descriptive Tekst"",""friendlyName"": ""DevOps desktop""}}'

The API used is documented here: https://docs.microsoft.com/en-us/rest/api/desktopvirtualization/desktops/update

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):

<#
    .SYNOPSIS
    Sets custom lock screen based on file in an Azure Storage Blob container
    See blob template to automatically configure a blob container: https://gitlab.com/Lieben/assortedFunctions/-/blob/master/ARM%20templates/blob%20storage%20with%20container%20for%20Teams%20Backgrounds%20and%20public%20access.json
   
    .NOTES
    filename: set-windows10LockScreen.ps1
    author: Jos Lieben
    blog: www.lieben.nu
    created: 13/05/2021
#>

$changedDate = "2021-05-13"
$lockscreenFileURL = "https://tasdsadgsadsad.blob.core.windows.net/teamsbackgrounds/figure-a.jpg" #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")

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

[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
    $netTask.Result
}

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
		
try{
	$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
    Exit
} 
       
try{ 
    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
}catch{
    Write-Output "Failed to set lock screen image"
    Write-Error $_ -ErrorAction SilentlyContinue
} 

Write-Output "Script complete"
Stop-Transcript

Source: https://gitlab.com/Lieben/assortedFunctions/-/blob/master/set-windows10LockScreen.ps1

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.

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/add-servicePrincipalToAllCSPChildTenants.ps1

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{
    Param(
        [String]$tableName,
        [Array]$values, # example: @(@{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar})
        [PSObject]$primaryKey, # example: @{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar}
        [System.Data.Common.DbConnection]$sqlConn
    )

    $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
        BEGIN
            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));
        END
    COMMIT TRANSACTION;"
           

    $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=https://database.windows.net/&api-version=2017-09-01')
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source = yourserver.database.windows.net; Initial Catalog = yourdatabase"
$sqlConn.AccessToken = $msi_authenticationResult.access_token
$sqlConn.Open()

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:

https://endpoints.office.com/endpoints/worldwide

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","127.0.0.1","::1")
$o365IPs = Invoke-RestMethod -Method GET -UseBasicParsing -Uri "https://endpoints.office.com/endpoints/worldwide" 
$o365IPs | % {$_.ips | %{if($allRanges -notcontains $_){$allRanges += $_}}}
$allRanges | % {
    if($_.IndexOf("/")){
        $payLoad = @{ipAddress=$_.Split("/")[0];allowed="true";subnetMask=$(([ipaddress]([double]4294967296-(1-shl32-$($_.Split("/")[1])))).IPAddressToString);}
    }else{
        $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.