9

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

oli
  • 682
  • 1
  • 12
  • 21
  • You are not showing what you have tried and the errors you are having. You cannot use anything in MSOL without connecting and authenticating first. There several ways to connect to AAD depending on what Auth type your are trying to leverage. Meaning, Integrated, token, MFA or certificate. – postanote Apr 12 '19 at 04:01
  • @postanote I have tried many things and cannot possibly post all my attempts here. I am asking for usage guidance in the case I describe. I am not using MFA nor certificate. I have tried Connect-AzureAD with token and then Invoke-Sqlcmd. But not sure how to use Invoke-Sqlcmd in my scenario. One of the error I experienced is: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. – oli Apr 12 '19 at 08:01

4 Answers4

6

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
bryanbcook
  • 16,210
  • 2
  • 40
  • 69
3

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.

postanote
  • 15,138
  • 2
  • 14
  • 25
  • 1
    Get-Credential pops up a dialog and is not an option for a fully automated pipeline. Using 'Authentication=Active Directory Password' with UID=service principal id and PWD=service principal key, I now get: AdalException: Could not discover a user realm – oli Apr 12 '19 at 10:39
  • 1
    With "Active Directory Password" you put a username and password. Not a service prinicpal key. To use a service principal, fetch your own Access token and set it on the connection before opening it, as documented here: https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi – David Browne - Microsoft Apr 12 '19 at 13:35
  • What David Brown said, as I obviously just blew right past that detail in the last sentence. – postanote Apr 12 '19 at 23:02
1

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.

Owain Esau
  • 1,876
  • 2
  • 21
  • 34
0

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

Mazin
  • 51
  • 3