Sep 052012

Recently I had a situation where I was trying to deploy a SQL Server from a VMware template. The sysprep process during the Windows Server 2008R2 template deployment was changing drive letters as it moved the optical drive to the end of the drive list. So, of course, it moved the SQL Server executable and database drives around and the services would fail to start after the template deployed.

So… Powershell to the rescue!

A coworker of mine helped me to create a Powershell script to change drive letters and then I placed it on the C: drive in the template.  This script also needed to be self-elevating to administrative levels, so I found a block of code that works great to do that. The script name is ChangeDriveLetters-Admin.ps1.

# Get the ID and security principal of the current user account
$myWindowsPrincipal=new-object System.Security.Principal.WindowsPrincipal($myWindowsID)# Get the security principal for the Administrator role
$adminRole=[System.Security.Principal.WindowsBuiltInRole]::Administrator# Check to see if we are currently running “as Administrator”
if ($myWindowsPrincipal.IsInRole($adminRole))
# We are running “as Administrator” – so change the title and background color to indicate this
$Host.UI.RawUI.WindowTitle = $myInvocation.MyCommand.Definition + “(Elevated)”
$Host.UI.RawUI.BackgroundColor = “DarkBlue”
# We are not running “as Administrator” – so relaunch as administrator# Create a new process object that starts PowerShell
$newProcess = new-object System.Diagnostics.ProcessStartInfo “PowerShell”;# Specify the current script path and name as a parameter
$newProcess.Arguments = $myInvocation.MyCommand.Definition;# Indicate that the process should be elevated
$newProcess.Verb = “runas”;# Start the new process
[System.Diagnostics.Process]::Start($newProcess);# Exit from the current, unelevated, process
}$DriveG = Get-WmiObject -Class win32_volume -Filter “DriveLetter = ‘G:'”
Set-WmiInstance -input $DriveG -Arguments @{DriveLetter=”T:”}$DriveF = Get-WmiObject -Class win32_volume -Filter “DriveLetter = ‘F:'”
Set-WmiInstance -input $DriveF -Arguments @{DriveLetter=”G:”}$DriveE = Get-WmiObject -Class win32_volume -Filter “DriveLetter = ‘E:'”
Set-WmiInstance -input $DriveE -Arguments @{DriveLetter=”F:”}$DriveD = Get-WmiObject -Class win32_volume -Filter “DriveLetter = ‘D:'”
Set-WmiInstance -input $DriveD -Arguments @{DriveLetter=”E:”}

I also needed to temporarily change the Powershell execution policy so that this script would execute successfully. The following batch file (C:\ChangeDrives.bat) sets the execution policy, runs the script file, and then changes the execution policy back to my default to keep things secure.

powershell -command “& {Set-ExecutionPolicy Unrestricted}”
powershell -file c:\ChangeDriveLetters-Admin.ps1″
powershell -command “& {Set-ExecutionPolicy RemoteSigned}”

All I had to do was set up a ‘Run Once’ command in the VMware Customizations Specifications Manager.

Voila! It works!

If you wanted, you could have this script start up the SQL Server services, clean up after itself (to remove the security threat that is this script), set some default SQL Server instance settings, or anything else you want Powershell to perform right after this template has been deployed.

