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

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.

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/".

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.

I know this is a slightly old question, but I had this exact same problem and in my case it was due to trying to run both the token request and the SQL command within the same Azure CLI task in our Azure DevOps pipeline.

When I switched out the SQL command to a standard PowerShell task and passed in the token as a parameter, this resolved the login issue against the database for some reason. I kept the token request in the existing Azure CLI task but set an output variable with the contents of the token.

I can't really work out why this is... I know you've indicated your token request and SQL calls were in separate scripts already, but I wonder if potentially the issue was that the SQL call was in an Azure PowerShell task as opposed to just a vanilla PowerShell one? Maybe there's some odd interaction between the authenticated service connection the Azure PowerShell task is using and the call to open the DB connection or something.

Hopefully this helps someone anyway - felt compelled to post as this was the one SO question that let me know I wasn't alone

--- EDIT ---

I've switched to running a single script as an Azure PowerShell task that gets the token using:

(Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

instead of the Azure CLI command az account get-access-token.

This can then open the SQL connection in the same script without issue.