I’d like to run some SQL scripts against an Azure SQL Database during deployment. I currently have this working using SQL Authentication, but I’d like to move away from this and authenticate using AAD.
Originally, I’d been hoping to use Invoke-SqlCmd or sqlcmd from within the Azure Powershell Script step which has already authenticated with Azure, but this doesn’t seem to be possible since the domain of the Octopus Server isn’t federated with AAD (?).
So as an alternative, I’m hoping to generate an Access Token and use the REST API. I’ve seen the topic here: REST token from PS Script, however there wasn’t a solution.
Is there any way to access the Password of the Azure Subscription Account within my own script? I can see that it’s passed in to AzureContext.ps1 which sets up the PS environment for Azure. If I can access those properties I’ll be able to use them to generate a token.
This is an example script that worked for us. The biggest limit we found is you can not use an AAD user to add and AD user to the database (we were writing a bootstrap script). But if you just want to kick off some SQL calls it works great. If you do need to add AD users you have to use a real AD account that can do AD Auth (and run your tentacle as that user)
The token this scrip gets can also be used for azure rest API call, useful if you need something not in there cmdlets
This will work for what I’m trying to do, I will create a secure variable in Octopus to store the AAD Key.
I was hoping there would be a way to use the implicit authentication of the ‘Azure Powershell’ step which Octopus automatically authenticates based on the Account in the step config. That would mean I wouldn’t need to manage the key separately from the Service Principle.
Yea I hear you on the implicit auth, if there is a way to do it I haven’t found it since MS changed the auth model in one of the powershell updates.
Glad it helps and good luck!