Obtain OAuth token using Service Connection

Question

I'm using Azure DevOps to deploy a web app and perform database migrations for an SQL Database. I have a Service Connection setup, and am able to deploy Azure resources using the Azure CLI, e.g.

- task: AzureCLI@2
  displayName: 'Deploy Azure resources'
  inputs:
    azureSubscription: 'My Service Connection'
    scriptType: 'bash'
    scriptLocation: inlineScript
    inlineScript: ./deploy.sh

Now, I want to perform database migrations, and I want to use the Service Connection to authenticate (the App Registration has been granted access to the SQL Database). My understanding is that I can login to the SQL Database using an OAuth token - my problem is how to get that token using the Service Connection.

Note that I can't add a client secret to the Service Principal in Azure, and so can't make a REST call to /oauth2/token with a client_secret (I work in the enterprise space, and things are locked down).

Given the pipeline has access to the Azure DevOps Service Connection, my feeling is there must be some way to use it to get an OAuth token that's valid for the https://database.windows.net/ resource - but how?

Answer 1

You may try the solution in the following case: Azure Pipeline connect to SQL DB using service principal

Adding an Azure CLI task which retrieved the bearer token. 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")