Azure VM Custom Script Extension allows you to download PowerShell scripts (along with supporting files) from your storage account and execute them. Although the feature sounds simple, with the power of PowerShell, you can automate VM configurations in flexible ways to achieve various VM customization scenarios. In this blog I’ll walk you through the steps of using Custom Script Extension to customize a SQL Server 2014 VM created from the Azure VM image gallery. We’ll use a SQL PowerShell script to enable SQL Server Managed Backup to Windows Azure, which allows you to backup your SQL Server 2014 database to Azure Blob Storage service.

 

SQL Server configuration script

The configuration script is straightforward, as shown in the following code list. You’ll need to replace [your Azure storage account] and [your Azure storage account key] with your Azure storage credential. Save this script as CustomScriptSQLPS.ps1.

#import SQL Server powershell module
import-module  sqlps -DisableNameChecking

#global variables - replace storage account name and key
$credentialName = "AzureStorageCredential_"+(RANDOM)
$storageAccountName = "[your Azure storage account]"
$storageAccountKey = "[your Azure storage account key]"

# start SQL Agent Service                                                                
write-host "Starting SQL Server Agent service ..."
Start-Service  sqlserveragent -verbose

CD SQLSERVER:SQL$env:COMPUTERNAMEDEFAULT

write-host "Creating SQL credential ..."
$secureString = convertto-securestring $storageAccountKey  -asplaintext -force    

# Create Credential
New-SqlCredential –name $credentialName –Identity $storageAccountName –secret $secureString 

$encryptionOption = New-SqlBackupEncryptionOption -NoEncryption

write-host "Enabling Managed SQL Server Backup..."

get-sqlsmartadmin | set-sqlsmartadmin -BackupEnabled $True -BackupRetentionPeriodInDays 7 -SqlCredential $credentialName -EncryptionOption $encryptionOption 

write-host "Managed SQL Server backup current configuration:"
get-sqlsmartadmin | fl

However, we can’t directly hand this script to Custom Script Extension yet. Custom Script Extension uses NTAUTHORITYSYSTEM account to execute scripts, but the account doesn’t have necessary privileges to execute the SQL configuration commands. To work around this, we’ll create a separate bootstrap script, which impersonates an administrator account before invokes the script.

 

Invoke the script with a different credential

Create another script named start.ps1 with the following content. For ease of explanation, I’ve added line number to each line:

1: $password =  ConvertTo-SecureString "[your admin account user password]" -AsPlainText -Force
2: $credential = New-Object System.Management.Automation.PSCredential("$env:COMPUTERNAME[your admin account]", $password)
3: $command = $file = $PSScriptRoot + "CustomScriptSQLPS.ps1"
4: Enable-PSRemoting –force
5: Invoke-Command -FilePath $command -Credential $credential -ComputerName $env:COMPUTERNAME
6: Disable-PSRemoting -Force

The first line converts the password of the administrator to be impersonated from plain text to secured string. Of course, it’s not the best practice to have passwords in plain text in your script – I’ll come back to that later. The second line creates the credential to be used to run the main script. You can use the same account you’ve chosen when you provisioned the VM. Line 3 gets the absolute path to the main script. To execute the main script under an impersonated credential, we’ll need to use Windows Remote Management (WinRM). The script turns on WinRM on at line 4 and turns WinRM off after use (line 6). And finally, line 5 invokes the main script using the Invoke-Command cmdlet, which takes a Credential parameter.

 

Use Custom Script Extension

Now we are ready to hand both scripts to Custom Script Extension. Upload both scripts to a scripts container under your storage account. Please refer to the first referenced link for more details of how Custom Script Extension works, especially if you want to use a different storage account other than your default storage account and a different container other than scripts. Then, from your workstation, you can run a script that looks like this (again, with line numbers):

1: $servicename = "[cloud service that hosts the VM]"
2: $vmname = "[name of the VM]"
3: $vm = Get-AzureVM -ServiceName $servicename -Name $vmname
4: Set-AzureVMCustomScriptExtension -ContainerName scripts -StorageAccountName '[your storage account name]' -VM $vm -FileName 'start.ps1', 'CustomScriptSQLPS.ps1' -Run 'start.ps1' | Update-AzureVM -Verbose
5: $status = Get-AzureVM -ServiceName $servicename -Name $vmname
6: $result = $status.ResourceExtensionStatusList.ExtensionSettingStatus.SubStatusList | Select Name, @{"Label"="Message";Expression = {$_.FormattedMessage.Message }} 
7: $result |fl

The key line in above script is line 4. It ensures the Custom Script Extension is installed on the VM, and then instructs the extension to download both start.ps1 and CustomScriptSQLPS.ps1 and then execute start.ps1.

 

Read password from an external file

One good practice in writing scripts is to avoid including passwords in plain texts. Here I’ll use a certificate for password encryption/decryption. I’ll assume that you (or your IT) have already deployed a certificate with private key to the target virtual machine, and you’ve used the public key of the same certificate to encrypt the password into a password.txt file in base-64 format:

$cert = Get-ChildItem Cert:LocalMachineMy[certificate thumbprint]
$bytes = [Text.Encoding]::UTF8.GetBytes("abcdefg")
$encrypted = $cert.PublicKey.Key.Encrypt($bytes, $true)
$base64 = [Convert]::ToBase64String($encrypted)
Set-Content .password.txt $base64

Consult this link if you need help in creating a new Management Certificate using makecert. And this blog article shows you how to deploy a certificate with Azure VM.

Modify start.ps1 and replace the first line with the following lines:

$cert = Get-ChildItem Cert:LocalMachineMy[certificate thumbprint]
$base64 = Get-Content .password.txt
$encrypted = [Convert]::FromBase64String($base64)
$bytes = $cert.PrivateKey.Decrypt($encrypted,$true)
$password = [Text.Encoding]::UTF8.GetString($bytes)

At last, make sure the password.txt file is delivered to Custom Script Extension by adding the file name to the file list:

Set-AzureVMCustomScriptExtension -ContainerName scripts -StorageAccountName '[your storage account name]' -VM $vm -FileName 'start.ps1', 'CustomScriptSQLPS.ps1', 'password.txt' -Run 'start.ps1' | Update-AzureVM -Verbose

 

Conclusion

In this blog article I walked you through the steps of using Custom Script Extension to customize a SQL Server 2014 VM. The article shows Custom Script Extension in action with a practical scenario. And the article covers a couple of typical scripting tasks such as impersonation and password encryption.

Credits

This blog article is a collaboration of multiple people. Big thanks to Madhan Arumugam and Sethu Srinivasan for their contributions.