How can I use Terraform to run a script to initialise the database on a newly-created Azure mssql_virtual_machine?

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:

1. The VM is created fine
2. I can run the scripts just fine
3. The database scripts can't authenticate as they're running as the wrong user
4. All attempts to switch to the right user appear to fail until the user has logged in through the UI.

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:\MyScripts\vm_setup\setup_databases.ps1 '${db_username}' '${db_password}' '${env}'
}