Introduction
Azure SQL Database is a useful SaaS database service provided by Microsoft Azure. As this service is integrated with other Azure services including Azure Active Directory, it is highly efficient to use Azure security principals to connect to the database that works for both users and applications. The post describes how to use token-based authentication to run SQL queries against Azure SQL Database.
The original post is written for Svitla blog.
Background
Solution uses Azure SQL Database, Azure PowerShell 9.3 and PowerShell Core v.7.3.
Setting up AAD, Azure SQL Database, Virtual Machine and providing permissions is out of scope of the post. Resource diagram is demonstrated below:
Task
Periodically, DevOps engineers perform operations on Azure SQL Database by PowerShell scripts. Azure SQL Database service provides several connection strings to a particular database, but it requires username and password. On the one hand, there could be a credential disclosure issue if the script is run manually by several DevOps engineers. On the other hand, if the script is included into automatic processing, it should be run as managed identity associated with the virtual machine. Azure database security best practices recommend using Azure AD authentication to manage user and application access to the database.
To authorize access to user or application, we use token-based approach that is covered in details by Microsoft identity platform access tokens. Access token that could be used to provide identity to Azure SQL Database service is returned by Get-AzAccessToken function, called for resource url https://database.windows.net/.
Solution
Function Get-SqlDatabaseAccessToken returns a raw access token to connect to Azure SQL database. Let’s start with the function that uses user identity and works on a personal laptop. Access token includes claims from Azure context that is why we need to provide tenant ID and subscription ID to authenticate user in the corresponding AAD. The script asks for authentication via default web browser, and it works even if Azure account requires MFA. If any step fails, the function returns $null.
Function Get-SqlDatabaseAccessToken
Function Get-SqlDatabaseAccessToken {
[CmdletBinding(DefaultParameterSetName = 'Default')]
Param
(
[Parameter(Mandatory = $true)]
[ValidatePattern('^[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
[string]$TenantID,
[Parameter(Mandatory = $true)]
[ValidatePattern('^[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
[string]$SubscriptionID
)
Connect-AzAccount -Tenant $TenantID | Out-Host;
if (-not $?) {
Write-Verbose "Can't connect to Azure Tenant";
return $null;
}
Set-AzContext -Subscription $SubscriptionID | Out-Host;
if (-not $?) {
Write-Verbose "Can't connect to Azure Subscription";
return $null;
}
$objectAccessToken = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/");
$accessToken = $objectAccessToken.Token;
if ( -not $accessToken) {
Write-Verbose "AccessToken is empty, can't connect to Azure SQL Database";
return $null;
}
return $accessToken;
}
Parameters
Function has the following parameters:
- string
$TenantID – Tenant ID for Azure subscription - string
$SubscriptionID – Azure Subcription ID
Return Value
Function returns access token to connect to Azure SQL Database service or $null.
Workflow
At lines 36 and 41, function connects to Azure tenant and sets an active subscription by its id. At lines 46-47, it gets raw access token using database service resource url. If any of these steps fail, the function returns $null.
Improved Function Get-SqlDatabaseAccessToken
If the script is run in unattended mode inside a virtual machine, it should get an access token for assigned managed identity. We can request token from the Azure Instance Metadata Service (IMDS), that’s available at a well-known, non-routable IP address (169.254.169.254). As IMDS address is reachable only from inside a virtual machine, the corresponding web request fails if it is run from outside Azure virtual machine. It gives the following algorithm improvements:
- request the access token via IMDS;
- if exception is catch, request the access token by using tenant and subscription IDs.
Function Get-SqlDatabaseAccessToken2 {
[CmdletBinding(DefaultParameterSetName = 'Default')]
Param
(
[Parameter(Mandatory = $false)]
[ValidatePattern('^|[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
[string]$TenantID,
[Parameter(Mandatory = $false)]
[ValidatePattern('^|[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
[string]$SubscriptionID
)
$accessToken = $null;
try {
$response = Invoke-WebRequest `
-Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' `
-Headers @{Metadata = "true" };
if (-not $? -or -not $response) {
Write-Verbose "The command is run outside Azure VM";
$accessToken = $null;
}
else {
$content = $response.Content | ConvertFrom-Json;
$accessToken = $content.access_token;
}
}
catch {
Write-Verbose $_.ErrorDetails;
$accessToken = $null;
}
if ($accessToken) {
return $accessToken;
}
if (-not $TenantID -or -not $SubscriptionID) {
return $null;
}
Connect-AzAccount -Tenant $TenantID | Out-Host;
if (-not $?) {
Write-Verbose "Can't connect to Azure Tenant";
return $null;
}
Set-AzContext -Subscription $SubscriptionID | Out-Host;
if (-not $?) {
Write-Verbose "Can't connect to Azure Subscription";
return $null;
}
$objectAccessToken = (Get-AzAccessToken `
-ResourceUrl "https://database.windows.net/");
$accessToken = $objectAccessToken.Token;
if ( -not $accessToken) {
Write-Verbose "AccessToken is empty, can't connect to Azure SQL Database";
return $null;
}
return $accessToken;
}
Parameters
Function has the same parameters:
- string
$TenantID – optional parameter, Tenant ID for Azure subscription - string
$SubscriptionID – optional parameter, Azure Subcription ID
Return value
Function returns access token to connect to Azure SQL Database service or $null.
Workflow
At lines 39-41, function requests an access token via IMDS. At lines 43-50, response is parsed. If an access token is not $null, function returns the result. Otherwise, or if an exception is thrown, function tries to use tenant and subscription IDs to obtain an access token. Lines 66-82 is the code of previously considered function, but additionally, it checks that values for Tenant ID and Subscription ID are provided.
Example
To apply that function, let’s consider an example script that get an access token and run simple database query that returns database name.
@('Az', 'SqlServer' ) |
ForEach-Object {
$moduleName = $_;
Write-Host "Check $moduleName module";
if ( $(Get-InstalledModule | Where-Object { $_.Name -eq $moduleName }).Count -eq 0) {
Write-Verbose "Import $moduleName module";
Find-Module -Name $moduleName -Verbose:$false | `
Install-Module -Scope CurrentUser -Repository PSGallery -Force -Verbose:$false;
}
else {
Write-Verbose "$moduleName module is installed";
}
}
$TenantID = 'ad488fc7-65a6-4a23-8ea1-08ae48e4e2f1';
$SubscriptionID = '998419f1-5d94-4627-814a-cb2bcd6eee42';
$AzureSqlServer = "tcp:test-server.database.windows.net,1433";
$AzureDatabaseName = "test-database";
$ConnectionTimeout = 600;
$Timeout = 600;
$fileName = $(Split-Path -Path $PSCommandPath -Leaf);
$startDateTime = $([DateTime]::Now);
Write-Host "Script '$fileName' start time = $([DateTime]::Now)" -ForegroundColor Blue;
$ScriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path;
. "$ScriptDir\functions.ps1";
$result = $true;
try {
$accessToken = Get-SqlDatabaseAccessToken2 `
-TenantID $TenantID `
-SubscriptionID $SubscriptionID;
if (-not $? -or ($null -eq $accessToken)) {
Write-Error "AccessToken is empty, can't connect to Azure SQL Database";
return $false;
}
try {
$dbquery = "SELECT DB_NAME()";
Write-Host $dbquery;
Invoke-Sqlcmd `
-ServerInstance $AzureSqlServer `
-Database $AzureDatabaseName `
-AccessToken $accessToken `
-Query $dbquery `
-QueryTimeout $Timeout `
-ConnectionTimeout $ConnectionTimeout `
-OutputSqlErrors $true `
-ErrorAction Stop `
-ErrorVariable scriptError | Out-Host;
$result = $?;
}
catch {
Write-Host $_;
$result = $false;
}
if ($scriptError) { $scriptError | Write-Host; }
Write-Host $(if ($result) { "Query run successfully" } else { "Query failed" });
}
finally {
$scriptDuration = [DateTime]::Now - $startDateTime;
Write-Host "Script '$fileName' ends, total duration $($scriptDuration.Days) day(s), " `
"$($scriptDuration.Hours):$($scriptDuration.Minutes):$($scriptDuration.Seconds).$($scriptDuration.Milliseconds)" `
-ForegroundColor Blue;
}
Workflow
At the beginning of the script, we check and install Az and SqlModule PowerShell modules, because without them, the script fails. Then provide hardcoded values of parameters:
- string
$TenantID – Tenant Id for Azure subscription - string
$SubscriptionID – Azure Subcription ID - string
$AzureSqlServer – DNS name or IP address of Azure SQL Server (from Connection Strings tab) - string
$AzureDatabaseName – Azure SQL Database name - number
$ConnectionTimeout – database connection timeout, that equal 10 minutes - number
$Timeout – query timeout, that equal 10 minutes
At lines 35-41, the script calls Get-SqlDatabaseAccessToken2 function. If the function returns not-null value, the script runs the SQL query and if it is successful, associated security principal has access to the SQL database. Query is executed by Invoke-Sqlcmd function at lines 44-61. If access token is correct, database name is returned.
- All used IP-addresses, names of servers, workstations, domains, are fictional and are used exclusively as a demonstration only.
- Information is provided «AS IS».
• Have more than 25 years of the architecting, implementing, and supporting various applications from small desktop and web utilities up to full-fledged cloud SaaS systems using mainly Microsoft technology stack and implementing the best practices.
• Have significant experience in the architecting applications starting from the scratch and from the existent application (aka “legacy”) where it is required to review, refactor, optimise the codebase and data structure, migrate to new technologies, implement new features, best practices, create tests and write documentation.
• Have experience in project management, collecting business requirements, creating MVP, working with stakeholders and end users, and tasks and backlog management.
• Have hands-on experience in the setting up CI/CD pipelines, the deploying on-premise and cloud systems both in Azure and AWS, support several environments.
• As Mathematician, I interested much in the theory of automata and computer algebra.
Submit an article or tip
