Invoke-Sqlcmd with AAD authentication

Question

In an Azure DevOps 'release pipeline', I provision Azure resources - including SQLServer databases - and try to configure access to databases using managed identities.

After ensuring the DevOps service principal is a member of the AAD group defined as AAD administrator for the database server, I need to run some SQL to add the managed identities users and alter the roles. This is attempted in an Azure Powershell task, using the Invoke-Sqlcmd cmdlet.

Invoke-Sqlcmd has two flavors and it is not obvious to me which one I shall use and if it matters. So far all I tried failed to authenticate with AAD.

How do I communicate my intent to use AAD authentication to Invoke-Sqlcmd?

Do I need to first Connect-AzureAD?

If I need to pass a System.Management.Automation.PSCredential object, what should I use as user and password, given that we are dealing with a service principal (the Azure DevOps service user)?

Answer 1

To authenticate with AAD, you'll need the following:

• An Azure Service principal
• A database where the Service Principal is either the Azure AD Administrator, or assigned to the database
• An Azure DevOps Service Connection that uses this Service Principal

In your Azure DevOps pipeline, use an Azure PowerShell Task that uses this Service Principal. Then call the Get-AzAccessToken function to obtain a credential that you can use.

- task: AzurePowerShell@5
  displayName: Azure DB Awesomeness
  inputs:
    azureSubscription: 'MyServiceConnection'
    azurePowerShellVersion: 'LatestVersion'
    scriptType: inlinescript
    script: |

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

        Invoke-SqlCmd -ServerInstance "$(DatabaseServer)" `
                      -Database "$(Database)" `
                      -AccessToken "$token" `
                      -Query "<YOUR QUERY>"

The reason this works is that the AzurePowerShell@5 task obtains the service principal credentials from the service connection and then calls Connect-Az. The Get-AzAccessToken cmdlet obtains the credentials of the service principal which can be used accordingly.