Azure SQL Database Token-based authentication with PowerShell


How to connect to Azure SQL Database using token-based authentication in PowerShell native apps
This guide assumes you already have a deployment of an Azure SQL Database, your PowerShell environment configured and you have an app registration for a native app in Azure Active Directory.

How to configure a native app registration: https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/10/30/how-to-create-a-native-powershell-app-registration-to-connect-to-azure-sql-db/

Problem summary:
There might come a time when you want to connect to your database with token-based authentication for some business need or for automation purposes.

Explanation of this guide:
This guide will explain how to connect to Azure SQL Database using token-based authentication in PowerShell using Native application registrations.

Tenant Name
First, we need to determine what our AAD Directory ID is. For that, please go to your Azure Active Directory blade and go to Properties.

$tenantName = "{AAD DIRECTORY ID}" # Tenant Name this is found in AAD > Properties > Directory ID

Application ID
Now we need to determine what our Application ID is and for that we also need to create a new App Registration and provide the proper permissions.
In your Azure Active Directory, go to App Registrations and create a new +New application registration.
Please capture the Application ID field and change it for the {APPLICATION ID} in the code snippet.

$clientId = "{APPLICATION ID}" # Application ID is found in AAD > App Registrations > Application ID

Redirection URI and Resource URI
The redirection URI is determined from your portal, if it's a native Application I'd suggest you to put https://localhost/.

# Rediret URI is found in AAD > App Registrations > Settings > Redirect URIs</span>

$redirectUri  = "{REDIRECT URI}"

# Resource URI is the resource that you are trying to connect to.
# Resource URI > Found in AAD > App Registrations > Settings > Required Permissions ></span>
# Add > Search Bar *Type in* > Azure SQL DB > Add Rights > OK >
# OK > Grant permissions

$resourceUri  = "https://database.windows.net/"

Authority URI
This is the STS link that we will use to authenticate our Tenant.

$authorityUri = "https://login.microsoftonline.com/$tenantName" # Authority URI https://login.microsoftonline.com/{DirectoryId|TenantName}

Script

$tenantName   = "{AAD DIRECTORY ID}"              # Tenant Name this is found in AAD > Properties > Directory ID
$clientId     = "{APPLICATION ID}"                # Application ID is found in AAD > App Registrations > Application ID
$redirectUri  = "{REDIRECT URI}"                  # Rediret URI is found in AAD > App Registrations > Settings > Redirect URIs
$resourceUri  = "https://database.windows.net/"   # Resource URI > Found in AAD > App Registrations > Settings > Required Permissions >
                                                  # Add > Search Bar *Type in* > Azure SQL DB > Add Rights > OK > OK > Grant permissions
$authorityUri = "https://login.microsoftonline.com/$tenantName" # Authority URI https://login.microsoftonline.com/{DirectoryId|TenantName}

# Credit to Ray Held for this function, great stuff! 
function GetAuthToken {

    # 64 bit make sure to change the version to the one available on your machine
    $adalPath  = "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.profile\5.5.1"

    # 32 bit uncomment if this is your scenario
    # adalPath  = "${env:ProgramFiles(x86)}\WindowsPowerShell\Modules\AzureRM.profile\5.3.4"

    $adal      = "$adalPath\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
    $adalforms = "$adalPath\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
        
    [System.Reflection.Assembly]::LoadFrom($adal) | Out-Null
    [System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null 
        
    $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authorityUri
    $authResult = $authContext.AcquireToken($resourceUri, $clientId, $redirectUri, "Always")   # Not sure when to use AcquireToken() or AcquireTokenAsync()
         
    return $authResult
}

# Call GetAuthToken to acquire token based on credentials entered in prompt
$authResult = GetAuthToken
$authResult.AccessToken

# Server name, database name and the connection string that will be used to open connection
$sqlServerUrl = "fcobo.database.windows.net"
$database = "fcobo"
$connectionString = "Server=tcp:$sqlServerUrl,1433;Initial Catalog=$database;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;"

# Create the connection object
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

# Set AAD generated token to SQL connection token
$connection.AccessToken = $authResult.AccessToken
$connection.AccessToken

# prints the connection information, it's still closed at this point, will open later on. The token is already attached.
$connection 

# Query that will be sent when the connection is open. I had a 4,000 record table and I was able to truncate with this script
$query = "TRUNCATE TABLE TEST1"

# Opens connection to Azure SQL Database and executes a query
$connection.Open()
# After this, the token is no longer there, I believe this is because the authentication went through already, so it gets rid of it. 
$connection
$command = New-Object -Type System.Data.SqlClient.SqlCommand($query, $connection)
$command.ExecuteNonQuery()
$connection.Close()
Comments (2)

  1. eti_3007 says:

    Hello Fernando !
    Thank you for this useful article.
    I tried to use this script to add new External Provider db user but it doesn’t work.
    Did you have an idea or links to help me in this challenge, please ?

    Etienne

    1. Hello Etienne,

      Thank you for your question and time to go over my blog post.

      Currently, token based and Service Principal accounts do not support AAD based commands, such as CREATE USER [asdf@domain.com] FROM EXTERNAL PROVIDER; or group like CREATE USER [AAD_Group_Name] FROM EXTERNAL PROVIDER;

      If you wish to add AAD contained database users, please create them through an AAD-based connection.

      Thanks.

      – Fernando

Skip to main content