Category Archives: Automation

Grouping devices in MDATP based on registered users

Microsoft Defender Advanced Threat Protection seems to be becoming the defacto leader in the A/V industry, at least when Windows is concerned, but other OS’es seem to be following quickly 🙂

At one of my international customers, many different locations and departments exist and we’d like to group devices in MDATP based on their primary user so we can assigned different administrators automatically, and apply different web filtering policies.

MDATP has the following options available for grouping:

These membership rules don’t say anything about the user, and the machine domains are all cloud native (no hybrid joins). So we need to use Tags to gain flexible targeting in MDATP.

The following PowerShell script can be scheduled as an Azure Runbook to automatically tag all your MDATP devices based on the ‘Company’ attribute of the device’s primary user. It could also be modified easily to e.g. parse a user’s group membership or UPN’s domain.

If you have a lot of devices, it may take a while for the first run (beyond Azure Automation limits), in that case run it locally first and then schedule it.

Using Azure Function MSI to connect to Azure SQL in Python

Since I had to spend a few hours figuring this out, and all examples/docs are wrong, here’s an example of how to use Python in an Azure Function to connect to an Azure PaaS database without credentials by utilizing the managed identity of the azure function app.

import logging
import os
import pyodbc
import requests 
import struct
import sys
import azure.functions as func


def get_bearer_token(resource_uri):
    identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]'identity_endpoint: {}'.format(identity_endpoint))'identity_header : {}'.format(identity_header))
    token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&api-version=2017-09-01"
    head_msi = {'X-IDENTITY-HEADER':identity_header}
    resp = requests.get(token_auth_uri, headers=head_msi)
    access_token = resp.json()['access_token']'response received from token endpoint: {}'.format(access_token))
    return access_token  

def main(req: func.HttpRequest) -> func.HttpResponse:'Function Starting')
        access_token = get_bearer_token(resource_uri)
        accessToken = bytes(access_token, 'utf-8')
        exptoken = b""
        for i in accessToken:
                exptoken += bytes({i})
                exptoken += bytes(1)
        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken  
        conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=tcp:{},1433;Database={}".format(sql_server,sql_database), attrs_before = { 1256:bytearray(tokenstruct) })'connected to {} on {}'.format(sql_server,sql_database))
        cursor = conn.cursor()
        cursor.execute("select @@version")
        row = cursor.fetchall()'sql data: {}'.format(row[0]))'finished')              
    except BaseException as error:'An exception occurred: {}'.format(error))   
    return func.HttpResponse("done!")



Calling Graph and other API’s silently for an MFA enabled account

The Graph and other Microsoft API’s should be called using a Service Principal whenever possible. But some endpoints (such as the ‘hidden’ azure api) don’t support service principals and require an actual user to call it.

Of course, users that have privileges in your organisation are protected with MFA / conditional access or you wouldn’t be reading my blog 🙂

Below script circumvents MFA by hijacking a refresh token which normally isn’t returned/exposed to the user. It then encrypts and caches it locally and refreshes and reuses it the next time it is called. As refresh tokens expire after 90 days of inactivity by default, you won’t see an MFA prompt again as long as tthe script runs at least once every 90 days.


  1. Az.Accounts module
  2. User account
  3. Onetime MFA prompt completion

Method 1: Code at Gitlab (requires Az.Accounts module)

Method 2: Code at GitLab (does not require any modules)

Method 1 example:

    Retrieve graph or other azure tokens as desired (e.g. for and bypass MFA by repeatedly recaching the RefreshToken stolen from the TokenCache of the Az module.
    Only the first login will require an interactive login, subsequent logins will not require interactivity and will bypass MFA.

    This script is without warranty and not for commercial use without prior consent from the author. It is meant for scenario's where you need an Azure token to automate something that cannot yet be done with service principals.
    If your refresh token expires (default 90 days of inactivity) you'll have to rerun the script interactively.

    $graphToken = get-azResourceTokenSilently -userUPN
    the UPN of the user you need a token for (that is MFA enabled or protected by a CA policy)
    .PARAMETER refreshTokenCachePath
    Path to encrypted token cache if you don't want to use the default
    .PARAMETER tenantId
    If supplied, logs in to specified tenant, optional and only required if you're using Azure B2B
    .PARAMETER resource
    Resource your token is for, e.g. "" would give a token for the Graph API
    .PARAMETER refreshToken
    If supplied, this is used to update the token cache and interactive login will not be required. This parameter is meant as an alternative to that initial first time interactive login
    filename: get-azResourceTokenSilently.ps1
    author: Jos Lieben
    created: 09/04/2020
    $refreshTokenCachePath=(Join-Path $env:APPDATA -ChildPath ""),

$strCurrentTimeZone = (Get-WmiObject win32_timezone).StandardName
$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById($strCurrentTimeZone)
[datetime]$origin = '1970-01-01 00:00:00'

    $tenantId = (Invoke-RestMethod "$($userUPN.Split("@")[1])/.well-known/openid-configuration" -Method GET).userinfo_endpoint.Split("/")[3]

        write-verbose "checking provided refresh token and updating it"
        $response = (Invoke-RestMethod "$tenantId/oauth2/token" -Method POST -Body "grant_type=refresh_token&amp;refresh_token=$refreshToken" -ErrorAction Stop)
        $refreshToken = $response.refresh_token
        $AccessToken = $response.access_token
        write-verbose "refresh and access token updated"
        Write-Output "Failed to use cached refresh token, need interactive login or token from cache"   
        $refreshToken = $False 

if([System.IO.File]::Exists($refreshTokenCachePath) -and !$refreshToken){
        write-verbose "getting refresh token from cache"
        $refreshToken = Get-Content $refreshTokenCachePath -ErrorAction Stop | ConvertTo-SecureString -ErrorAction Stop
        $refreshToken = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($refreshToken)
        $refreshToken = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($refreshToken)
        $response = (Invoke-RestMethod "$tenantId/oauth2/token" -Method POST -Body "grant_type=refresh_token&amp;refresh_token=$refreshToken" -ErrorAction Stop)
        $refreshToken = $response.refresh_token
        $AccessToken = $response.access_token
        write-verbose "tokens updated using cached token"
        Write-Output "Failed to use cached refresh token, need interactive login"
        $refreshToken = $False

#full login required
    Write-Verbose "No cache file exists and no refresh token supplied, perform interactive logon"
    if ([Environment]::UserInteractive) {
        foreach ($arg in [Environment]::GetCommandLineArgs()) {
            if ($arg -like '-NonI*') {
                Throw "Interactive login required, but script is not running interactively. Run once interactively or supply a refresh token with -refreshToken"

    Import-Module az.accounts -erroraction silentlycontinue | out-null

    if(!(Get-Module -Name "Az.Accounts")){
        Throw "Az.Accounts module not installed!"
    Write-Verbose "Calling Login-AzAccount"
        $Null = Login-AzAccount -Tenant $tenantId -ErrorAction Stop
        $Null = Login-AzAccount -ErrorAction Stop

    #if login worked, we should have a Context
    $context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext
        Write-verbose "logged in, checking local refresh tokens..."
        $string = [System.Text.Encoding]::Default.GetString($context.TokenCache.CacheData)
        $marker = 0
        $tokens = @()
            $marker = $string.IndexOf("https://",$marker)
            if($marker -eq -1){break}
            $uri = $string.SubString($marker,$string.IndexOf("RefreshToken",$marker)-4-$marker)
            $marker = $string.IndexOf("RefreshToken",$marker)+15
            if($string.Substring($marker+2,4) -ne "null"){
                $refreshtoken = $string.SubString($marker,$string.IndexOf("ResourceInResponse",$marker)-3-$marker)
                $marker = $string.IndexOf("ExpiresOn",$marker)+31
                $expirydate = $string.SubString($marker,$string.IndexOf("OffsetMinutes",$marker)-6-$marker)
                $tokens += [PSCustomObject]@{"expiresOn"=[System.TimeZoneInfo]::ConvertTimeFromUtc($origin.AddMilliseconds($expirydate), $TZ);"refreshToken"=$refreshToken;"target"=$uri}
        $refreshToken = @($tokens | Where-Object {$_.expiresOn -gt (get-Date)} | Sort-Object -Descending -Property expiresOn)[0].refreshToken
        write-verbose "updating stolen refresh token"
        $response = (Invoke-RestMethod "$tenantId/oauth2/token" -Method POST -Body "grant_type=refresh_token&amp;refresh_token=$refreshToken" -ErrorAction Stop)
        $refreshToken = $response.refresh_token
        $AccessToken = $response.access_token
        write-verbose "tokens updated"

        Throw "Login-AzAccount failed, cannot continue"

    write-verbose "caching refresh token"
    Set-Content -Path $refreshTokenCachePath -Value ($refreshToken | ConvertTo-SecureString -AsPlainText -Force -ErrorAction Stop | ConvertFrom-SecureString -ErrorAction Stop) -Force -ErrorAction Continue | Out-Null
    write-verbose "refresh token cached"
    Throw "No refresh token found in cache and no valid refresh token passed or received after login, cannot continue"

    write-verbose "update token for supplied resource"
    $null = Login-AzAccount -AccountId $userUPN -AccessToken $AccessToken
    $context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext
    $resourceToken = [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, $resource).AccessToken
    Throw "Failed to translate access token to $resource , cannot continue"

return $resourceToken

This post was inspired by a use case Mark had for

Azure AD app registration and scope + roles modification (manifest) as application

As the ‘new’ Graph API does not support application credentials (client_credentials oauth2 flow) when working with most of the ServicePrincipal and Application parts of the Graph API, and I really did not want to work with a user account (background processes, MFA, etc), I had to work something out on some of the older (but still supported) API’s which I gleaned from Msft’s PS modules.

For anyone googling, the following code example allows you to create an azure ad application with serviceprincipal and allows you to modify the manifest of the application (e.g. here is the AppRoles and AppIdentifier).

$tenantId = "75d24247-6221-46a1-a651-530ae36dd399"
$clientId = "62d2235b-2ef6-4d70-b273-401c9eb450b3" #client ID (to call graph api with)
$clientSecret = "xxxxxx" #client secret

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Web")
$body = @{client_id=$clientId;client_secret=$clientSecret;resource='';grant_type='client_credentials'}
$headers = @{"Authorization" = "Bearer $((invoke-webrequest -uri "$tenantId/oauth2/token" `
-Method POST -ContentType "application/x-www-form-urlencoded" -Body $body).Content | convertfrom-json | select access_token -ExpandProperty access_token)"

#finding an application or create it if it doesn't exist
$appName = "MyApplication"
$app = (Invoke-RestMethod -Method GET -UseBasicParsing -Uri "$tenantId/applications?api-version=1.6&amp;`$filter=displayName eq %27$appName%27" -Headers $headers -ContentType "application/json").value
    $body = [pscustomobject]@{
	    'displayName' = $appName
        'availableToOtherTenants' = $True
    $app = Invoke-RestMethod -Method POST -UseBasicParsing -Uri "$tenantId/applications?api-version=1.6" -Headers $headers -Body ($body | ConvertTo-Json -Depth 100) -ContentType "application/json"

#check/correct the identifier URI of the application's published API
if($app[0].identifierUris -notcontains "api://mydomain/myApi"){
    $body = [PSCustomObject]@{
        "identifierUris" = @("api://mydomain/myApi")
    Invoke-RestMethod -Method PATCH -UseBasicParsing -Uri "$tenantId/applications/$($app[0].objectId)?api-version=1.6" -ContentType "application/json" -Headers $headers -Body ($body | ConvertTo-Json -Depth 100)

#check if a certain approle is present, if not, add it
if(@($app[0].appRoles | Where{$_.displayName -eq "Access To My API"}).Count -eq 0){
    $body = [PSCustomObject]@{
        "appRoles" = @([PSCustomObject]@{
            "allowedMemberTypes" = @("Application","User")
            "description" = "Access To My API"
            "displayName" = "Access To My API"
            "id" = [Guid]::NewGuid()
            "isEnabled" = $True
            "value" = "Api.AccessRead"
    Invoke-RestMethod -Method PATCH -UseBasicParsing -Uri "$tenantId/applications/$($app[0].objectId)?api-version=1.6" -ContentType "application/json" -Headers $headers -Body ($body | ConvertTo-Json -Depth 100)

#finding the serviceprincipal belonging to the application or creating it if it doesn't exist
$sp = (Invoke-RestMethod -Method GET -UseBasicParsing -Uri "$tenantId/servicePrincipals?api-version=1.6&amp;`$filter=displayName eq %27$appName%27" -Headers $headers -ContentType "application/json").value
    ##Adding service principal to application instance
    $body = [pscustomobject]@{
	    'appId' = $app.appId
        'tags' = @("WindowsAzureActiveDirectoryIntegratedApp")
    $sp = Invoke-RestMethod -Method POST -UseBasicParsing -Uri "$tenantId/servicePrincipals/?api-version=1.6" -Headers $headers -Body ($body | ConvertTo-Json -Depth 100) -ContentType "application/json"

Note that, for this code to work, you need to grant your application the Company Administrator role, like this:

	$app = Get-AzureADServicePrincipal -SearchString "myapplication"
	$role = Get-AzureADDirectoryRole | Where-Object { $_.DisplayName -eq "Company Administrator" }
Add-AzureADDirectoryRoleMember -ObjectId $role.ObjectId -RefObjectId $app.ObjectId