LLI Wiki — My Digital Knowledge Base

Azure SQL : requête avec un jeton d'identité managée

Script PowerShell qui obtient un jeton via IMDS pour une identité managée et interroge une base Azure SQL (version, collation, taille).

Ce script PowerShell obtient un jeton d'accès pour une identité managée affectée par l'utilisateur, auprès du service de métadonnées d'instance Azure (IMDS), puis l'utilise pour interroger une base Azure SQL. La requête renvoie la version de SQL Server, la collation de la base, l'ordre de tri et la taille des données de la base.

${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()
}

Remarques

Mastodon LLIMastodon SocialFosstodon