Azure DevOps Pipeline not authenticating Az SQL powershell commands

Question

When using the Azure CLI task within an Azure DevOps pipeline to run a powershell script as per the below:

$resourceGroupName = "rgname"
$serverName = "sqldbname"

$databases = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName

foreach ($database in $databases) {
    Write-Host "Deleting database $($database.DatabaseName)..."
    Remove-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $database.DatabaseName -Force
}

I'm seeing the below error thrown on the Get-AzSqlDatabase command:

Your Azure credentials have not been set up or have expired, please run Connect-AzAccount to set up your Azure credentials. No certificate thumbprint or secret provided for the given service principal '***'.

I've also seen the same with the managed instance equivalent command 'Get-AzSqlInstanceDatabase'

What is the best way to authenticate these types of requests that does not require interaction? (given that it is being run within a pipeline as part of an automated process).

As an FYI the pipelines are being run on a self-hosted windows agent and the scripts within the job have been given access to the OAuth token.

Answer 1

Your Azure credentials have not been set up or have expired, please run Connect-AzAccount to set up your Azure credentials. No certificate thumbprint or secret provided for the given service principal '***'.

Based on the error message, the Connect-AzAccount need to be run before the Get-AzSqlDatabase command.

To solve this issue, I suggest that you can change to use Azure PowerShell task.

It will automatically running the Connect-AzAccount command.

Here is an example:

steps:
- task: AzurePowerShell@5
  displayName: 'Azure PowerShell script: InlineScript'
  inputs:
    azureSubscription: 'xx'
    ScriptType: InlineScript
    Inline: |
     $resourceGroupName = "xxx"
     $serverName = "xxx"
     
     $databases = Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName
     
     echo $databases
    azurePowerShellVersion: LatestVersion

Result: