'Invoke-Sqlcmd with AAD authentication

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)?

Solution 1:^[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.

Solution 2:^[2]

Oil - Understood.

Try something like this example, just tweak for your needs...

$Creds = Get-Credential -Credential '[email protected]'
$Username = $($Creds.GetNetworkCredential().UserName)
$Password = $($Creds.GetNetworkCredential().Password)
$Database = "testg"
$Server = 'test.database.windows.net'
$Port = 1433
$cxnString = "Server=tcp:$Server,$Port;Database=$Database;Authentication=Active Directory Password;UID=$UserName;PWD=$Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
$query = "select count(*) from dbo.Authors"
$cxn = New-Object System.Data.SqlClient.SqlConnection($cxnString)
$cxn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($query, $cxn)
$cmd.CommandTimeout = 120
$cmd.ExecuteNonQuery()
$cxn.Close()

Update for OP

That was just an option. If you don't want the popups, you can create a secure file and pull creds from that or store creds in the Windows credential store and pull them from there. Yet, you still have to create those first.

Solution 3:^[3]

I couldn't find a way to do this with Invoke-SQLCmd, but the Module DBATools which i use primarily has a very easy method explained here (Example 8): https://docs.dbatools.io/#Connect-DbaInstance

I have been using this for a while now and have had no issues with it connecting, however, as far as i know, MFA is not supported yet.

Solution 4:^[4]

How about creating an AAD Application and giving certificate as its authorization. Setup the App to have access on your database and then you can use the same for

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Nick.McDermaid
Solution 2
Solution 3	Owain Esau
Solution 4	Mazin

'Invoke-Sqlcmd with AAD authentication

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]

Solution 4:^[4]