Automating SQL Server Configuration On Azure VMs With Run Command Script

Aug 1, 2025 by Kenji Nakamura 73 views

Introduction

Hey guys! Let's dive into a super cool way to automate the configuration of SQL Server on Azure Virtual Machines. If you're like me, you're probably deploying SQL Server VMs on Azure all the time. And you probably have a bunch of company standards that you need to apply to each one. Now, doing this manually? Total drag, right? So, I've been exploring using Azure's Run Command Script feature to handle this, and I'm excited to share what I've learned. Using Run Command Script not only saves time but also ensures consistency across all your SQL Server deployments. This means fewer headaches down the road and more time to focus on the fun stuff. We'll be focusing on how to invoke SQL commands on a newly provisioned SQL Server VM using the NT AUTHORITY\SYSTEM account, which is a nifty trick for getting things done without a ton of manual setup. So, buckle up, and let's get started!

The Challenge: Automating SQL Server Configuration

Alright, so here's the deal. Deploying a SQL Server VM on Azure is pretty straightforward, but the real challenge begins after the deployment. You've got to configure it to meet your company's standards. This usually involves a bunch of tasks like restoring databases, setting up security, configuring performance settings, and more. Doing this manually for each VM is not only time-consuming but also prone to errors. Imagine setting up a dozen VMs and having to repeat the same steps over and over. It's a recipe for mistakes, and nobody wants that.

That's where automation comes in. We need a way to automate these configurations so that every SQL Server VM is set up exactly the same way, every time. This ensures consistency, reduces errors, and saves a ton of time. Plus, automation frees you up to focus on more strategic tasks, like designing new databases or optimizing existing ones. So, how do we tackle this? Well, Azure's Run Command Script feature is a fantastic tool for this job. It allows us to run PowerShell scripts directly on the VM without having to RDP into it. This is a game-changer for automation.

But there's a catch. We need to run these scripts in a way that they have the necessary permissions to make changes to SQL Server. That's where the NT AUTHORITY\SYSTEM account comes in. This account has high privileges on the VM and can be used to perform administrative tasks on SQL Server. So, the trick is to figure out how to invoke SQL commands using this account from our Run Command Script. Let's dive into how we can make this happen.

Understanding Azure Run Command and NT AUTHORITY\SYSTEM

Before we get into the nitty-gritty, let's quickly break down what we're working with. Azure Run Command is a feature that allows you to remotely execute scripts on an Azure VM. Think of it as your remote control for VMs. You can run PowerShell scripts, batch scripts, or even shell scripts, all without needing to log in to the VM. This is super handy for automation because you can trigger these scripts as part of your deployment process or even on a schedule.

Now, let's talk about NT AUTHORITY\SYSTEM. This is a built-in Windows account that has a ton of privileges on the system. It's like the super-admin of the VM. When you run a script under this account, you can do pretty much anything. This is crucial for our SQL Server configuration because we need to be able to create databases, restore backups, and configure security settings. These are all tasks that require administrative privileges.

The challenge is that we can't just directly specify NT AUTHORITY\SYSTEM when we run a Run Command script. Azure Run Command typically runs scripts under a different account. So, we need a way to elevate the script's permissions to use NT AUTHORITY\SYSTEM. This involves a bit of PowerShell magic, which we'll get into in the next section. But first, it's important to understand why we're going through this extra step. Using NT AUTHORITY\SYSTEM gives us the power we need to configure SQL Server, but it also means we need to be careful. We want to make sure our scripts are secure and only do what they're supposed to do.

PowerShell Magic: Invoking SQL Commands as NT AUTHORITY\SYSTEM

Okay, time for the fun part: the PowerShell magic! This is where we'll craft the script that actually invokes SQL commands using the NT AUTHORITY\SYSTEM account. The key here is to use the Invoke-Sqlcmd cmdlet within a specific context that allows us to run as the system account. This involves a few steps, but don't worry, we'll break it down.

First, we need to create a PowerShell script that will execute our SQL commands. This script will use the Invoke-Sqlcmd cmdlet, which is the standard way to run SQL queries from PowerShell. However, we can't just run Invoke-Sqlcmd directly. We need to make sure it runs under the NT AUTHORITY\SYSTEM account. To do this, we'll use the Pester module to run our tests in the context of the NT AUTHORITY\SYSTEM account.

Here's a basic example of how the PowerShell script might look:

# Install the Pester Module (if not already installed)
If (-NOT (Get-Module Pester)) {
 Install-Module Pester -Force
}

# Create a Pester test to run Invoke-Sqlcmd as NT AUTHORITY\SYSTEM
Describe "Run SQL Command as NT AUTHORITY\SYSTEM" {
 It "Should execute SQL command" {
 # SQL Server details
 $SqlServer = ".\\SQLEXPRESS" # Replace with your SQL Server instance
 $SqlCmd = "SELECT @@SERVERNAME" # Replace with your SQL command

 # Execute SQL command using Invoke-Sqlcmd
 try {
 $result = Invoke-Sqlcmd -ServerInstance $SqlServer -Query $SqlCmd -ErrorAction Stop
 Write-Output "SQL Command Result: $($result)"
 } catch {
 Write-Error "Error executing SQL command: $($_.Exception.Message)"
 }
 }
}

In this script, we're first checking if the Pester module is installed and installing it if it's not. Then, we're creating a Pester test that will run our SQL command. Inside the test, we're using Invoke-Sqlcmd to execute a simple SQL query. The -ErrorAction Stop parameter tells PowerShell to stop the script if there's an error, which is a good practice for automation. The try-catch block allows us to handle any errors that might occur during the SQL command execution.

Executing the Script via Azure Run Command

Now that we have our PowerShell script, the next step is to execute it using Azure Run Command. This involves a few steps in the Azure portal or using the Azure CLI. We'll walk through both methods.

Using the Azure Portal

Navigate to your Virtual Machine: In the Azure portal, find the SQL Server VM you want to configure and click on it.
Go to Run command: In the VM's blade, under the "Operations" section, click on "Run command".
Select RunPowerShellScript: You'll see a list of pre-defined command scripts. Choose "RunPowerShellScript".
Paste your script: A pane will open where you can paste your PowerShell script. Copy the script we created in the previous section and paste it here.
Execute the script: Click the "Run" button. Azure will execute the script on the VM.
Monitor the output: After the script runs, you'll see the output in the portal. This will show you whether the script executed successfully and any output from the SQL command.

Using Azure CLI

If you're more of a command-line person, you can use the Azure CLI to execute the script. This is especially useful for automation scenarios where you want to script the entire deployment and configuration process.

Here's the Azure CLI command to run the script:

az vm run-command invoke \
 --resource-group <your-resource-group> \
 --name <your-vm-name> \
 --command-id RunPowerShellScript \
 --scripts <path-to-your-script.ps1>

Replace <your-resource-group> with the name of your resource group, <your-vm-name> with the name of your VM, and <path-to-your-script.ps1> with the path to your PowerShell script. When you run this command, Azure CLI will execute the script on the VM and display the output in the console.

Best Practices and Considerations

Before we wrap up, let's talk about some best practices and things to consider when using Azure Run Command to configure SQL Server VMs.

Security

Security is always a top priority. When you're running scripts as NT AUTHORITY\SYSTEM, you need to be extra careful. Make sure your scripts are well-tested and only do what they're supposed to do. Avoid hardcoding sensitive information like passwords in your scripts. Use Azure Key Vault to store secrets and retrieve them in your scripts. Also, consider using managed identities to authenticate to Azure services instead of using service principals or credentials.

Error Handling

Robust error handling is crucial for automation. Your scripts should be able to handle errors gracefully and provide informative error messages. Use try-catch blocks to catch exceptions and log errors. Consider implementing retry logic for transient errors. Also, make sure to monitor the output of your scripts to identify any issues.

Idempotency

Make your scripts idempotent. This means that running the script multiple times should have the same effect as running it once. This is important for automation because you might need to run the script multiple times, for example, if there's an error during the initial run. You can achieve idempotency by checking the state of the system before making changes and only making changes if necessary.

Logging and Monitoring

Implement proper logging and monitoring. Your scripts should log all important actions and errors. Use Azure Monitor to collect logs and metrics from your VMs. Set up alerts to notify you of any issues. This will help you identify and resolve problems quickly.

Conclusion

Alright, guys, that's a wrap! We've covered how to use Azure Run Command to automate SQL Server configuration on Azure VMs. We've talked about the importance of automation, how to invoke SQL commands using the NT AUTHORITY\SYSTEM account, and some best practices to keep in mind. By using Azure Run Command, you can save a ton of time and ensure consistency across your SQL Server deployments. So, go ahead and give it a try. I'm sure you'll find it as useful as I have. Happy automating!