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.

__init__.py:

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

resource_uri="https://database.windows.net/"
sql_server="XXXXXX.database.windows.net"
sql_database="primary"

def get_bearer_token(resource_uri):
    identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]
    logging.info('identity_endpoint: {}'.format(identity_endpoint))
    logging.info('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']
    logging.info('response received from token endpoint: {}'.format(access_token))
    return access_token  

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Function Starting')
    try:
        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) })
        logging.info('connected to {} on {}'.format(sql_server,sql_database))
        cursor = conn.cursor()
        cursor.execute("select @@version")
        row = cursor.fetchall()
        logging.info('sql data: {}'.format(row[0])) 
        logging.info('finished')              
    except BaseException as error:
        logging.info('An exception occurred: {}'.format(error))   
    return func.HttpResponse("done!")

requirements.txt:

astroid==2.4.2
azure-functions==1.2.1
certifi==2020.6.20
chardet==3.0.4
colorama==0.4.3
idna==2.9
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
pylint==2.5.3
pyodbc==4.0.30
requests==2.24.0
six==1.15.0
toml==0.10.1
urllib3==1.25.9
wrapt==1.12.1

AADSTS50131: Device is not in required device state: known. Or, the request was blocked due to suspicious activity, access policy, or security policy decisions with WDATP

If you’re trying to use the Windows Defender Advanced Threat Protection through the API or through PowerBI and get an AADSTS50131 error, you’ll probably check your sign in logs to see if you’re being blocked by conditional access. If there’s nothing there, as I had the joy of discovering (tsk Microsoft, you really should log this) then check your classic policies and disable if present (old anyway):

OnedriveMapper 4.00 with Modern Auth released!

Modern Authentication in OnedriveMapper

With version 4.00 OnedriveMapper now fully supports ‘Modern’ Authentication!

As I wrote started this script in my early programming days and there’s a lot of crap code in there I also finally cleaned up a little, reducing the total number of lines by ~50% πŸ™‚

The result is a much faster, leaner script that will map and/or redirect to anything in Office 365 for any of your users. MFA? Not a problem. External MFA? Also not a problem. Conditional Access? Totally fine.

Some older rarely used legacy features were removed, and login is only silent if no MFA / SSO is configured for IE.

The older version (3.X) will remain in my git repo but will not be actively maintained.

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.

Requirements:

  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:

<#
    .SYNOPSIS
    Retrieve graph or other azure tokens as desired (e.g. for https://main.iam.ad.ext.azure.com) 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.

    .EXAMPLE
    $graphToken = get-azResourceTokenSilently -userUPN nobody@lieben.nu
    .PARAMETER 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. "https://graph.microsoft.com" 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
    
    .NOTES
    filename: get-azResourceTokenSilently.ps1
    author: Jos Lieben
    blog: www.lieben.nu
    created: 09/04/2020
#>
Param(
    $refreshTokenCachePath=(Join-Path $env:APPDATA -ChildPath "azRfTknCache.cf"),
    $refreshToken,
    $tenantId,
    [Parameter(Mandatory=$true)]$userUPN,
    $resource="https://graph.microsoft.com"
)

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

if(!$tenantId){
    $tenantId = (Invoke-RestMethod "https://login.windows.net/$($userUPN.Split("@")[1])/.well-known/openid-configuration" -Method GET).userinfo_endpoint.Split("/")[3]
}

if($refreshToken){
    try{
        write-verbose "checking provided refresh token and updating it"
        $response = (Invoke-RestMethod "https://login.windows.net/$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"
    }catch{
        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){
    try{
        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 "https://login.windows.net/$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"
    }catch{
        Write-Output "Failed to use cached refresh token, need interactive login"
        $refreshToken = $False
    }
}

#full login required
if(!$refreshToken){
    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"
    if($tenantId){
        $Null = Login-AzAccount -Tenant $tenantId -ErrorAction Stop
    }else{
        $Null = Login-AzAccount -ErrorAction Stop
    }

    #if login worked, we should have a Context
    $context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext
    if($context){
        Write-verbose "logged in, checking local refresh tokens..."
        $string = [System.Text.Encoding]::Default.GetString($context.TokenCache.CacheData)
        $marker = 0
        $tokens = @()
        while($true){
            $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 "https://login.windows.net/$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"

    }else{
        Throw "Login-AzAccount failed, cannot continue"
    }
}

if($refreshToken){
    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"
}else{
    Throw "No refresh token found in cache and no valid refresh token passed or received after login, cannot continue"
}

if($AccessToken){
    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
}else{
    Throw "Failed to translate access token to $resource , cannot continue"
}

return $resourceToken

This post was inspired by a use case Mark had for Senserva.com

PS Oneliner to get local device compliance state

Getting local Azure AD / Intune device compliance state with a PowerShell Oneliner

The Graph API and Intune portal(s) give insight into device compliance status, but what about a local equivalent? How can we locally detect from e.g. a script on a Windows 10 laptop if the device is compliant or not?

I couldn’t find any documentation, WMI properties or registry keys, but I did find that the Company Portal shows the compliance status and caches this in a file. So, although it isn’t pretty, I’ve settled for this method for now and created a UserVoice item requesting a local W10 API/regkey/WMI property to query Intune compliance status of the device.

((get-content -Path (Get-Childitem –Path (Join-Path $env:LOCALAPPDATA `
-ChildPath "Packages\Microsoft.CompanyPortal_8wekyb3d8bbwe\TempState\ApplicationCache") `
-Include *.tmp* -File -Recurse | sort-object -Descending -Property lastWritetime)[0] | convertfrom-json).data | convertfrom-json).ComplianceState

Office 365, Azure, Enterprise Mobility and DevOps