Invoke-Sqlcmd with AAD authentication

Oil - Understood.

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

$Creds = Get-Credential -Credential 'username@domainname.onmicrosoft.com'$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.

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: 'LatestVerison'    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.

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.