Azure Pipeline connect to SQL DB using service principal

Question

After my azure pipeline has create an azure sql db I'd like execute some sql.

The sql in question must be executed by an AAD authenticated user.

The service connection for the pipeline is an AAD authenticated user of the database.

If Im willing for the script to consume the service principals secret, then I can construct an OAuth call to retrieve a bearer token and use that to connect to the database.

However since the powershell script is running in the context of the service principal I have a gut feeling there is a better way to connect to the db using the service principal without relying on the secret.

Any ideas how I can do this?

Answer 1

The solution is went with was:

I added an Azure CLI task which retrieved the bearer token. I then passed this to Azure Powershell task which used the token.

$token= & az account get-access-token --resource=https://database.windows.net --query accessToken
Write-Host("##vso[task.setvariable variable=sqlToken]$token")

Answer 2

You can try below scripts in azure powershell task of your azure pipeline to get the accesstoken for Resource https://database.windows.net/

$context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext

$databaseAccessToken = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, $context.Environment, $context.Tenant.Id.ToString(), $null, [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, $null, "https://database.windows.net/").AccessToken

$databaseAccessToken