'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 |