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

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

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/ARM%20templates/add-remoteAppToWVDHostPool.json

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.

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:

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/ARM%20templates/wvdDynamicHostpool.json

Microsoft 365, Azure, Automation & Code