Connect to Azure SQL Database with PowerShell Core

Azure SQL Database is a helpful 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.

Source code: GitHub

Background

The 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 the post's scope. The 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 they include a 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 in 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 a user or application, we use a token-based approach covered in detail by Microsoft identity platform access tokens. The access token that could 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, so we need to provide tenant ID and subscription ID to authenticate a user in the corresponding AAD. The script asks for authentication via the default web browser, which works even if the Azure account requires MFA. If any step fails, the function returns $null.

Function Get-SqlDatabaseAccessToken

Function Get-SqlDatabaseAccessToken {
    <#
    .SYNOPSIS
    Function Get-SqlDatabaseAccessToken returns access token to connect to Azure SQL Database service.
    .DESCRIPTION
    Function Get-SqlDatabaseAccessToken returns access token to connect to Azure SQL Database service.
    The script authenticate user via default browser. If any step fails, the function returns $null.
    .PARAMETER TenantID
    Tenant Id for Azure subscription
    .PARAMETER SubscriptionID
    Azure Subscription Id
    .INPUTS
    None. You cannot pipe objects to Get-SqlDatabaseAccessToken.
    .OUTPUTS
    Get-SqlDatabaseAccessToken returns $null or access token to Azure SQL Database service
    .EXAMPLE
    PS> Get-SqlDatabaseAccessToken -TenantID ad488fc7-65a6-4a23-8ea1-08ae48e4e2f1 -SubscriptionId 998419f1-5d94-4627-814a-cb2bcd6eee42
    Returns access token to connect to Azure SQL Database service in Azure subscription '998419f1-5d94-4627-814a-cb2bcd6eee42'
    #>
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    Param
    (
        # Azure TenantID
        [Parameter(Mandatory = $true)]
        [ValidatePattern('^[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
        [string]$TenantID,
 
        # Azure SubscriptionID
        [Parameter(Mandatory = $true)]
        [ValidatePattern('^[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
        [string]$SubscriptionID
    )
 
    #region Connect to Azure and get access token
    # NOTE: connection command will authenticate via default browser
    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;
    }
    #endregion
 
    return $accessToken;
}

Parameters

The function has the following parameters:

• string $TenantID – Tenant ID for Azure subscription.
• string $SubscriptionID – Azure Subscription ID.

Return value

The function returns an access token to connect to Azure SQL Database service or $null.

Workflow

At lines 36 and 41 function connects to the Azure tenant and sets an active subscription by its ID. At lines 46-47, it gets raw access tokens using database service resource URL. If any of these steps fail, the function returns $null.

Improved function Get-SQL Database Access Token

If the script is run in unattended mode inside a virtual machine, it should get an access token for the assigned managed identity. We can request a token from the Azure Instance Metadata Service (IMDS) available at a well-known, non-routable IP address (169.254.169.254). The IMDS address is reachable only from a virtual machine, so the corresponding web request fails if run from an outside Azure virtual machine. It gives the following algorithm improvements:

• request the access token via IMDS;

• if an exception is caught, request the access token by using the tenant and subscription IDs.

Function Get-SqlDatabaseAccessToken2 {
    <#
    .SYNOPSIS
    Function Get-SqlDatabaseAccessToken2 returns access token to Azure SQL database service.
    .DESCRIPTION
    Function Get-SqlDatabaseAccessToken2 returns access token to Azure SQL database service.
    At first step it requests the access token via IMDS;
    if exception is caught, request the access token by using tenant and subscription IDs.
    If any step fails, the function returns $null.
    .PARAMETER TenantID
    Tenant Id for Azure subscription
    .PARAMETER SubscriptionID
    Azure Subscription Id
    .INPUTS
    None. You cannot pipe objects to Get-SqlDatabaseAccessToken2.
    .OUTPUTS
    Get-SqlDatabaseAccessToken2 returns $null or access token to Azure SQL Database service
    .EXAMPLE
    PS> Get-SqlDatabaseAccessToken2 -TenantID ad488fc7-65a6-4a23-8ea1-08ae48e4e2f1 -SubscriptionId 998419f1-5d94-4627-814a-cb2bcd6eee42
    Returns access token to connect to Azure SQL Database service in Azure subscription '998419f1-5d94-4627-814a-cb2bcd6eee42'
    #>
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    Param
    (
        # Azure TenantID
        [Parameter(Mandatory = $false)]
        [ValidatePattern('^|[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
        [string]$TenantID,
 
        # Azure SubscriptionID
        [Parameter(Mandatory = $false)]
        [ValidatePattern('^|[0-9A-F]{8}[-]{1}(?:[0-9A-F]{4}-){3}[0-9A-F]{12}$')]
        [string]$SubscriptionID
    )
 
    #region Get an access token for managed identities for Azure resources
    $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;
    }
    #endregion
 
    #region Connect to Azure and get access token
    # NOTE: connection command will authenticate via default browser
    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;
    }
    #endregion
 
    return $accessToken;
}

Parameters

The function has the same parameters:

• string $TenantID – optional parameter, Tenant ID for Azure subscription.
• string $SubscriptionID – optional parameter, Azure Subscription ID.

Return value

The function returns an access token to connect to Azure SQL Database service or $null.

Workflow

At lines 39-41, the function requests an access token via IMDS. In lines 43-50, the response is parsed. The function returns the result if an access token is not $null. Otherwise, or if an exception is thrown, the process uses tenant and subscription IDs to obtain an access token. Lines 66-82 are the code of the 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 gets an access token and runs a simple database query that returns the 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";
    }
}
 
# Azure subscription
$TenantID = 'ad488fc7-65a6-4a23-8ea1-08ae48e4e2f1';
$SubscriptionID = '998419f1-5d94-4627-814a-cb2bcd6eee42';
# Azure SQL Database
$AzureSqlServer = "tcp:test-server.database.windows.net,1433";
$AzureDatabaseName = "test-database";
# others
$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";
 
#region Run trivial query to database
$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" });
    #endregion
}
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 Subscription 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, which equals 10 minutes.
• number $Timeout – query timeout, which equals 10 minutes.

At lines 35-41, the script calls the Get-SqlDatabaseAccessToken2 function. If the function returns a not-null value, the script runs the SQL query, and if it is successful, the associated security principal has access to the SQL database. The query is executed by the Invoke-Sqlcmd function at lines 44-61. If the access token is correct, the database name is returned.

______________________

1. All used IP addresses, names of servers, workstations, and domains are fictional and are used as a demonstration only.
2. Information is provided «AS IS».