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
- The managed identity must have been granted access to the database. The script itself does not create the user.
- The metadata endpoint is the fixed link-local address of the Azure Instance Metadata Service; it is only reachable from an Azure resource with a managed identity.
- The connection string was redacted in the source. The form shown is the standard template for Azure SQL with
Encrypt=True; replace the server and database names with your own. $UamiIDis the object ID of the identity, as in the source; the placeholder value must be replaced.