LLI Wiki — My Digital Knowledge Base

Azure SQL: query with a managed identity token

PowerShell script that gets a token from IMDS for a managed identity and queries an Azure SQL database (version, collation, size).

This PowerShell script gets an access token for a user-assigned managed identity from the Azure Instance Metadata Service (IMDS) and uses it to query an Azure SQL database. The query returns the SQL Server version, the database collation, the sort order and the data size of the database.

${TargetDB} = "appdb01"
${SourceServerNameFullPath} = "sqlserver01.database.windows.net"
$AppID = "https://database.windows.net"
$UamiID = "00000000-0000-0000-0000-000000000000"
$metadataUri = "http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=$AppID&object_id=$UamiID" 
$request = Invoke-RestMethod -Uri $metadataUri -Headers @{Metadata = "true" }

$AccessToken = $request.access_token

# Query for the version, the collation, the sort order, the database name and the database size
$Query = @"
SELECT 
    SERVERPROPERTY('ProductVersion') AS Version,
    DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS Collation,
    SERVERPROPERTY('SqlSortOrder') AS Sort_Order,
    DB_NAME() AS DatabaseName,
    (SELECT SUM(size * 8 / 1024.0) FROM sys.database_files WHERE type_desc = 'ROWS') AS DatabaseSizeMB
"@

$ConnectionString = "Server=tcp:${SourceServerNameFullPath},1433;Initial Catalog=${TargetDB};Encrypt=True;TrustServerCertificate=False;"

# Use the access token with System.Data.SqlClient (no password in the connection string)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.AccessToken = $AccessToken

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.CommandText = $Query
$SqlCommand.Connection = $SqlConnection

try {
    $SqlConnection.Open()
    $Reader = $SqlCommand.ExecuteReader()
    while ($Reader.Read()) {
        Write-Output ("Version: " + $Reader["Version"])
        Write-Output ("Collation: " + $Reader["Collation"])
        Write-Output ("Sort Order: " + $Reader["Sort_Order"])
        Write-Output ("Database Name: " + $Reader["DatabaseName"])
        Write-Output ("Database Size (MB): " + $Reader["DatabaseSizeMB"])
    }
} catch {
    Write-Error "Error executing query: $_"
} finally {
    $SqlConnection.Close()
}

Notes

Mastodon LLIMastodon SocialFosstodon