Azure Pipelines connecting to Azure SQL Database AAD - Login failed for user '<token-identified principal>

Question

I am attempting to connect to a Azure SQL DB with an AAD account as part of my Azure pipeline. Roughly I have the following:

An Azure pipeline with an associated service connection. An Azure SQL DB with the AAD admin set to the service principal (of the connection). An Azure CLI task which gets the bearer token of the service principal.

I then have a Azure Powershell script that uses the bearer token to connect to the DB:

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=tcp:$($sqlServer),1433;Initial Catalog=$($sqlDB);Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" 
$conn.AccessToken = $env:ACCESSTOKEN
$conn.Open()

This results in the following error:

Login failed for user '<token-identified principal>

The Agent running the pipeline is allowed through the SQL Server firewall.

In the DB logs the error code is 18456 and the state is 132 (AAD failure).

I have tested this manually using Azure CLI logging in using the service connections service principal, requesting the bearer token and then connecting to the db (using the code above) and this works.

If I compare the bearer token of the Pipeline test and the Azure CLi manual test, they are identical (apart from exp, uti and aio)

Any ideas?

Answer 1

According to this doc:

This authentication method allows middle-tier services to obtain JSON Web Tokens (JWT)

You need to use the id_tokens for Azure AD authentication. From your script, it seems that you are using the pipeline environment access token.

On the other hand, you could navigate to Azure Portal -> Azure Sql -> Query Editor page. Then you could select the Active Directory authentication and check if you could log into the database successfully.

If you have the issue Login failed for user '<token-identified principal> in this page, you could try to add the target account as Active Directory admin on SQL Server or create contained users mapped to Azure AD identities.

In addition, you can also log in Azure SQL with Azure AD account and password.

Script:

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=tcp:$($sqlServer),1433;Initial Catalog=$($sqlDB);Persist Security Info=False;User ID:User account;Password=Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password;" 
$conn.Open()

Updates:

For access token:

You could try to run the Azure CLI command in Azure Clould shell:

az login

az account get-access-token --resource https://database.windows.net

Then you could get the Access Token.

Based on my test, if you use this Access token to connect Azure Sql , it could work as expected.

Updates2:

Get it . If you set the Service Principal as the AD Admin. The manual Service Principal type service connection could work.

For example:

- task: AzureCLI@2
  displayName: 'Azure CLI '
  inputs:
    azureSubscription: manual service connection name
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
     
     az account get-access-token --resource https://database.windows.net
  enabled: false

The Access token could be used to connect Azure Sql.

Answer 2

Try changing your Connection String to;

"Data Source=<SQLServerName>.database.windows.net,1433;Initial Catalog=<dbName>;"

The Access Token you're using should be correct as you're generating it from "https://database.windows.net/".

Answer 3

az account get-access-token did NOT work for me. then I completely changed code to this and it worked.. .here is a sample (also logout any previous logins as sometimes $authResult was in faulted state and did not have the access token within). You can tweak this to your needs.

$tenant = "xxxx-xxxx-xxxxxx"
$applicationId = $(Get-AzADServicePrincipal -DisplayName "MyServicePrincipal").ApplicationId
$clientSecret = "yyyy-yyyyy-yyyy" 
$subscriptionId = "xyxy-xyxy-xyxy-xyxy"

$secstr = New-Object -TypeName System.Security.SecureString
$clientSecret.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $applicationId, $secstr

Connect-AzAccount -Tenant $tenant -SubscriptionId $subscriptionId -ServicePrincipal -Credential $cred


$resourceAppIdURI = 'https://database.windows.net/'

$authority = ('https://login.windows.net/{0}' -f $tenant)
$ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($applicationId, $clientSecret)
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
$Token = $authResult.Result.AccessToken

$conn = New-Object System.Data.SqlClient.SQLConnection 
$SQLServerName = "sql-myApplicationDbServer-dev.database.windows.net"
$DatabaseName = 'sqldb-myApplicationDb-dev'

$conn.ConnectionString = "Data Source=$SQLServerName;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($Token)
conn.Open() -- works for me

i'd also suggest to set the Sql server AAD admin to an AAD group, and make the servcie principal as a member of that group. Its easier to manage that way.