Testing Azure SQL access with a managed identity in PowerShell
Az PowerShell test sequence with a managed identity to troubleshoot an Azure Batch job, plus a script syntax check.
When an Azure automation script (for example a database export or copy job running on Azure Batch) works from one machine and fails from another, it helps to test the same Az PowerShell calls step by step with the managed identity used by the job. This article shows a minimal test sequence and a quick syntax check for the script.
Check the script syntax without running it
Parser.ParseFile reports syntax errors with line and column, without executing anything:
$errs = $null
[void][System.Management.Automation.Language.Parser]::ParseFile(
"C:\scripts\New-BatchJobs.ps1",
[ref]$null,
[ref]$errs
)
if ($errs) {
$errs | ForEach-Object {
"{0}:{1} {2}" -f $_.Extent.StartLineNumber, $_.Extent.StartColumnNumber, $_.Message
}
} else {
"OK"
}
Test with the user-assigned managed identity
Run the following on the machine that will execute the job (an Azure Batch node, an Azure Virtual Desktop host, a VM). Connect-AzAccount -Identity only works where the identity is actually assigned to the compute resource; it fails on a workstation outside Azure, where you authenticate interactively with Connect-AzAccount instead.
# Sign in with the user-assigned managed identity (client ID of the identity)
Connect-AzAccount -Identity -AccountId "00000000-0000-0000-0000-000000000000"
# Select the subscription that holds the SQL server
Set-AzContext -SubscriptionName "Production Subscription"
Get-AzContext
# List the SQL servers of the resource group
Get-AzSqlServer -ResourceGroupName "rg-example" | Format-Table ServerName
Then check that the identity can read the server and its elastic pools:
$server = Get-AzSqlServer -ResourceGroupName "rg-example" -ServerName "sqlsrv-example-01"
Get-AzSqlElasticPool -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName |
Format-Table ServerName, ElasticPoolName
Repeat the sequence for each subscription the job has to reach (production, staging, ...) and from each place the job can run. Compare which combinations succeed and which fail: a failure on one host or in one subscription while the others succeed points to a difference in the identity assignment on that host or in the role assignments on that subscription or resource group. Verify those two points first.
Copy a database into an elastic pool
A copy of an Azure SQL database into an elastic pool of the same server is done with New-AzSqlDatabaseCopy. The log of such a job typically goes through these steps: resolve the source server, find the source database, verify the destination server and elastic pool, then start the copy. The copy is asynchronous, so a "copy initiated" message does not mean it has finished.
New-AzSqlDatabaseCopy -ResourceGroupName "rg-example" -ServerName "sqlsrv-example-01" `
-DatabaseName "AppDb" `
-CopyDatabaseName "AppDb-copy" `
-ElasticPoolName "elasticpool-example-01"
See the New-AzSqlDatabaseCopy reference for all parameters.
Notes
- Subscription names, resource groups, servers, pools and identity IDs are generic examples; the all-zero GUID stands for the identity client ID.
- The job scripts of the original notes are specific to one environment and are not reproduced.