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?
In the Cloud Shell, sign in to SQL Database by using the SQLCMD command. Replace <server-name> with your server name, <db-name> with the database name your app uses, and <aad-user-name> and <aad-password> with your Azure AD user's credentials. <identity-name> is the name of the managed identity in Azure AD.
Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add a user to in the dropdown. Add Azure Active Directory user '[email protected]' then add it to the db_datareader and db_datawriter roles.
This indicates that the password is incorrect. Error: 18456, Severity: 14, State: 8. When SQL Server is installed using Windows Authentication mode and is later changed to SQL Server and Windows Authentication mode, the sa login is initially disabled.
To make this user read-only, we need to assign a role. This is done by granting this user the “db_datareader” role under the selected database context. Now that we have understanding on what the script would do for us, we can execute the script and create the read-only user account.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With