I’ve created an Azure Windows virtual machine using the MicrosoftSQLServer/sql2022-ws2022 source image, and I’ve attached to it an E: drive containing pre-existing SQL Server databases. I have a script that will perform a several initialisation tasks. One is to attach those databases and set up the SQL authentication so that the DBs can be used by my application. If I log onto the machine via RDP and run that script manually, everything works well.
Now I want to automate this, and have Terraform run the script. This isn’t working.
I’ve been able to use azurerm_virtual_machine_extension to run the script. This is all working for the non-database parts. For example, I can successfully change the timezone of the server to match my local timezone using Powershell’s Set-TimeZone.
The problem comes with running the database script. The scripts run as NT Local Authority, but in order to be able to run Invoke-SqlCommand, I need to be running as the admin user I specified when creating the VM.
I’ve seen a lot of suggestions online as for ways to run under a different user account. None of these worked properly for me. The one that was most promising was to set up a scheduled task to run as a different user. This initially seemed to work!
However, I then discovered that when this script is run on a freshly-created VM, it doesn’t run. If I then log into the VM as the admin user, then delete the Terraform azurerm_virtual_machine_extension resource, then run terraform apply again, then the script runs. So it appears I need to log in through the UI to “activate” the admin account. This seems very wrong!
If I can’t fully automate this, I might as well just automate the placing of the script onto the VM’s C: drive (which works fine), and then have someone manually log in and run this. But it feels like there must be a better solution.
I have a lot of code to back this up (Terraform and Powershell), most of which won’t be very illuminating. I can post code if that helps.
To summarise:
- The VM is created fine
- I can run the scripts just fine
- The database scripts can’t authenticate as they’re running as the wrong user
- All attempts to switch to the right user appear to fail until the user has logged in through the UI.
5
Turns out there’s a very simple answer – the azurerm_mssql_virtual_machine resource allows you to specify a username and password that are then set up with SQL authentication, on top of the Windows authentication that is granted automatically. It’s then straightforward for a script to be run that connects using those credentials.
For example:
resource "azurerm_mssql_virtual_machine" "db_mssql_vm" {
virtual_machine_id = azurerm_windows_virtual_machine.db_vm.id
sql_license_type = "PAYG"
sql_connectivity_update_username = "myDbUser"
sql_connectivity_update_password = "s3cr3t"
...
}
This also has the benefit of making the script shorter, as it doesn’t need to set up this user itself.
I also found an alternative approach, using Invoke-Command in the Powershell to run as the desired user. I couldn’t get this to work first time, but I think the problem was that I didn’t get the quoting of values injected by Terraform into the PS script right:
$securePassword = ConvertTo-SecureString '${admin_password}' -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ('${db_host}${admin_username}', $securePassword)
Invoke-Command -ComputerName '${db_host}' -Credential $credential -ScriptBlock {
c:MyScriptsvm_setupsetup_databases.ps1 '${db_username}' '${db_password}' '${env}'
}