Category Archives: Azure

Distributing Teams Backgrounds to all users using MEM

I’ve seen a few examples of distributing a set of teams backgrounds to users in MEM, mostly they seemed a little overly complex, especially in targetting all local users and/or packaging the script into an application. I wanted to:

  • Deploy using MEM PS script in user context
  • Avoid access controls on the storage location of the backgrounds
  • Simplify the creation of required Azure resources
  • Have a simple update procedure

Here’s my take, first autocreate a blob storage location using my template:

Note down the name you used for the storage account (which was the only parameter to the template), and use it to configure this PowerShell script:

Finally, deploy the script to a group of your users in MEM, in their own context:

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

Add remoteapp to WVD hostpool using an ARM template

As I couldn’t find anyone who had written about adding a RemoteApp to a Windows Virtual Desktop hostpool (existing) in an existing Workspace, I figured I’d share my template.

  • It automatically creates an application group if it doesn’t exist yet
  • It takes into account existing applications if the application group already exists
  • The group has to be a remoteapp group, not a desktop group
  • This template is idempotent
  • This template can authorize any existing principal you specify
  • It creates 1 or more remoteapps in the group specified
  • It automatically manages the link to an existing hostpool/workspace

Incremental WVD deployments or redeploying persistent WVD hosts

When you add hosts to a WVD hostpool, and want to do so programmatically, this can be done by seperately deploying new hosts.

Redeploying an existing host manually is easy if it isn’t persistent, but if the disk should be retained, you’ll want to disconnect and reconnect it. This would require a lot of scripting (or third party tools).

As I wanted to keep our WVD solution contained to a single ARM template, some creativity was needed. For example, when deploying a new host that has a newer version of a gallery image, ARM would normally complain that the existing hosts don’t have the correct image version and it can’t change that for you since it is a read-only property (imageReference). Another example would be to move the VM between availability zones.

Linked WVD ARM template solves all these issues, but has a couple of important parameters to be aware of if you’re going to use it;

  1. redeploy: set to true in a scenario where you want to upgrade existing hosts, e.g. from a different VM type, or want to redeploy to another availability Zone. It will use the provided list of disk names to create new VM’s based on these existing disks
  2. existingDiskNames: if redeploy = true, supply the list of disk names of the persist VM’s you just deleted.
  3. vmNumberOfInstances: the number of VM’s you want to have (including existing VM’s)
  4. existingNumberOfInstances: the number of VM’s already present in the hostpool when running this arm template.
    #3 and #4 should be the same if redeploy = true, the template does not support redeploying and expanding the hostpool in the same deployment, this should be done in order.

After redeploying hosts, make sure to reconnect the users as they were assigned before you deleted them.

Download the ARM template here from GIT:

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 🙂