How to Log into Azure SQL with Security Principal

To log into your Azure SQL Database with a Security principal do the following:

1. Create an Azure AD security group
2. Add this AAD group as Azure Administrator to your Azure SQL server 
3. Obtain Access Token
4. Connect to Azure SQL Server with the access token

for step #3 use this function



#
# Based on
# https://blogs.technet.microsoft.com/stefan_stranger/2018/06/06/connect-to-azure-sql-database-by-obtaining-a-token-from-azure-active-directory-aad/
#

Function Get-AADToken {
[CmdletBinding()]
[OutputType([string])]
PARAM (
[String]$TenantID,
[string]$ServicePrincipalId,
[securestring]$ServicePrincipalPwd
)

Try {
# Set Resource URI to Azure Database
$resourceAppIdURI = 'https://database.windows.net/'

# Set Authority to Azure AD Tenant
$authority = 'https://login.windows.net/' + $TenantId

$ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($ServicePrincipalId, $ServicePrincipalPwd)
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
$Token = $authResult.Result.AccessToken
return $Token
}
Catch {
Throw $_
$ErrorMessage = 'Failed to acquire Azure AD token.'
Write-Error -Message 'Failed to acquire Azure AD token'
}
}


When you have the token to connect to the database is as simple as this



$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($SPNToken)


And putting all in one piece:

#
# Based on
# https://blogs.technet.microsoft.com/stefan_stranger/2018/06/06/connect-to-azure-sql-database-by-obtaining-a-token-from-azure-active-directory-aad/
#
#
# Prerequisites:
#    1. Azure AD Admin  assigned to the Azure SQL Server (use AAD Group)
#    2. The security principal added to the AAD group (which is Azure Admin for the SQL server)
#
#

Function Get-AADToken {
    [CmdletBinding()]
    [OutputType([string])]
    PARAM (
        [String]$TenantID,
        [string]$ServicePrincipalId,
        [securestring]$ServicePrincipalPwd
    )
    Try {
        # Set Resource URI to Azure Database
        $resourceAppIdURI = 'https://database.windows.net/'

        # Set Authority to Azure AD Tenant
        $authority = 'https://login.windows.net/' + $TenantId
        $ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($ServicePrincipalId, $ServicePrincipalPwd)
        $authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
        $authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
        #$Token = $authResult.Result.CreateAuthorizationHeader()
        $Token = $authResult.Result.AccessToken
        return $Token
    }
    Catch {
        Throw $_
        $ErrorMessage = 'Failed to acquire Azure AD token.'
        Write-Error -Message 'Failed to acquire Azure AD token'
    }
}


# Service Principal details
$SPname = "<your SP display name>"
$SPpwd = "<your SP secret>"

# SQL Server and DB
$SQLServerName = "<your Azure SQL server name>"
$DatabaseName = '<your DB name>'

$AzureCtx = Get-AzureRmContext
if (! $AzureCtx) { Connect-AzureRmAccount; $AzureCtx = Get-AzureRmContext }

# Connect to db using SPN Account

$TenantId = $AzureCtx.Tenant.ID
$ServicePrincipalId = $(Get-AzureRmADServicePrincipal -DisplayName $SPname).ApplicationId
$SecureStringPassword = ConvertTo-SecureString -String $SPpwd -AsPlainText -Force


$SPNToken = Get-AADToken -TenantID $TenantId -ServicePrincipalId $ServicePrincipalId -ServicePrincipalPwd $SecureStringPassword

Write-Output "Create SQL ConnectionString"
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($SPNToken)

Write-Output "Connect to database and execute SQL script"
$conn.Open()
$query = 'select count(*) as aaaaa from dbo.t1'
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)
$Result = $command.ExecuteScalar()
$Result
$conn.Close()

#end