Click here to Skip to main content
16,020,990 members
Articles / Hosted Services / Azure

Connect to Azure SQL Database with PowerShell Core

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
25 May 2023CPOL4 min read 3.3K   1  
The post describes how to use token-based authentication to run SQL queries against Azure SQL Database.

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:

Image 1

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

PowerShell
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 Subcription 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

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.
PowerShell
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 catch, 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 Subcription 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

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.

PowerShell
@('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 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.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Ukraine Ukraine
• 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.

Comments and Discussions

 
-- There are no messages in this forum --