Simplifying VM Provisioning with PowerCLI and SQL

Virtualization has made server deployments easier, and putting a new server into production can be as easy as right-clicking on a template and selecting Deploy VM and applying a customization spec.

Deploying a VM from a template is just one step in the process.  Manual intervention, or worse – multiple templates, may be required if the new VM needs more than the default number of processors or additional RAM.  And deployment tasks don’t stop with VM hardware.  There may be other steps in the process such as putting the server’s Active Directory account into the correct OU, placing the VM in the correct folder, or granting administrative rights to the server or application owner.

All of these steps can be done manually.  But it requires a user to work in multiple GUIs and even log into the remote server to assign local admin rights.

There is an easier way to handle all of this.  PowerShell, with the PowerCLI and Active Directory plugins, can handle the provisioning process, and .Net calls can be used to add a user or group to the new server’s Administrator group while pulling the configuration data from a SQL database.

The Script

I have a script available on Github that you can download and try out in your environment.   The script, Provision-VM.ps1, requires a SQL database for profile information, which is explained below, PowerCLI, and the Active Directory PowerShell cmdlets.  You will also need two service accounts – an Active Directory user with Administrator permissions in vCenter and an Active Directory user with Domain Administrator permissions.

This script was designed to be used with the vCenter Orchestrator PowerShell module and WinRM.  vCO will provide a graphical front end for entering the script parameters and executing the script.

This script might look somewhat familiar.  I used a version of it in my Week 1 Virtual Design Master submission.

What Provision-VM.ps1 Does

So what exactly does Provision-VM.ps1 do?  Well, it does almost exactly what it says on the tin.  It provisions a brand new VM from a template.  But it does a little more than just deploy a VM from a template.

The exact steps that are taken are:

  1. Query the SQL database for the customization settings that are needed for the profile.
  2. Prestage the computer account in the Active Directory OU
  3. Create a non-persistent Customization Spec
  4. Set the IP network settings for the customization spec
  5. Deploy a new VM to the correct resource pool/cluster/host and datastore/datastore cluster using the specified template based on the details retrieved in step 1.
    Note: The Resource Pool  parameter is used in the script instead of the host parameter because the Resource Pool  parameter encompasses hosts, clusters, and resource pools.  This provides more flexibility than the host parameter.
  6. Add additional CPUs and RAM is specified using the –CPUCount and –RAMCount parameters
  7. Power on VM and customize
  8. Add server owner user account or group to the local administrators group if one is specified using the –Owner parameter.

By using this deployment process along with some other scripts for configuring a server for a specific role after it has been deployed, I’ve been able to reduce the number of templates that need to be managed to 1 per Windows version.

WinRM and Working Around Kerberos Issues

vCenter Orchestrator is a great tool for automation and orchestration, and VMware has developed a PowerShell plugin to extend vCO management to Windows hosts and VMs.  This plugin even uses WinRM, which is Microsoft’ s preferred remote management technology for PowerShell.

WinRM setup for the vCO appliance, which I use in my environments, requires Kerberos to be used when making the remote connection.  I use a single Windows jumpbox to execute all of my PowerShell scripts from one location, so I run into Kerberos forwarding issues when using vCO and PowerShell to administer other systems.

There is a way to work around this, but I won’t spend a lot of time on it since it deserves it’s own post.  However, you can learn more about how the password information is stored and converted into a PowerShell credential from this article on

I also put together a little script that creates a password hash file using some of the code in the article above.

SQL-Based Profiles

One of the drawbacks of trying to script server deployments is that it needs to be simple to use without making it too hard to maintain.   I can make all required inputs – cluster or resource pool, datastore, template, etc, – into parameters that the person who runs the script has to enter.  But if you plan on using a script as part of a self-service provisioning model, keeping the number of parameters to a minimum is essential.  This helps limit the options that are available to users when deploying VMs and prevents them from having to worry about backend details like cluster and datastore names.

The tradeoff, in my experience, is that you need to put more into the script to compensate for having fewer parameters.   To do this, you’ll need to create “profiles” of all the customization settings you want to apply to the deployed server and code it directly into the script.

Let’s say you have one vSphere.  The cluster has three VLANs that servers can connect to, two datastore clusters where the server can be stored, and three templates that can be deployed.  To keep the script easy to run, and prevent admins or app owners from having to memorize all the details, you’d need to create 18 different profile combinations to cover the various settings.

This can make the script larger as you’ll need to include all combinations of settings that will be deployed.  It also makes it more likely that any additions or changes could introduce a script breaking bug like a missing curly bracket or quotation mark.

There is another way to reduce the size and complexity of the script while keeping parameters to a minimum – use a SQL database to store the customization settings.  These customization settings would be queried at run-time based on the profile that the end user selects.

The database for this script is a simple single table database.  There is a SQL script on Github to set up a table similar to the one I use in my lab.  If you choose to add or remove fields, you will need to edit the Provision-VM.ps1 file starting around line 106.

Database Schema Screenshotimage

There are two ways that the information can be retrieved from the database.  The first method is to install SQL Server Management Studio for SQL Server 2012 or newer on the server where the script will be executed.  The other is to use .Net to connect to SQL and execute the query.  I prefer the later option because it requires one less component to install.

The code for querying SQL from PowerShell, courtesy of Iris Classon’s blog that is linked above, is:

$dataSource = $SQLServer
$user = "SQL Server User Account"
$pwd = "Password"
$database = "OSCustomizationDB"
$databasetable = "OSCustomizationSettings"
$connectionString = "Server=$dataSource;uid=$user;pwd=$pwd;Database=$database;Integrated Security=False;"
$query = "Select * FROM $databasetable WHERE Profile_ID = '$Profile'"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText  = $query
$result = $command.ExecuteReader()

$ProfileDetails = new-object “System.Data.DataTable”
You may notice that SQL Authentication is used for querying the database.  This script was designed to run from vCO, and if I use the PowerShell plugin, I run into Kerberos issues when using Windows Integrated authentication.  The account used for accessing this database only needs to have data reader rights.

Once the settings have been retrieved from the database, they can be used to determine which template will be deployed, the resource pool and datastore or datastore cluster that it will be deployed to, temporarily modify an existing customization spec NIC mapping settings at runtime, and even determine which OU the server’s AD account will be deployed in.

The benefit of this setup is that I can easily add new profiles or change existing profiles without having to directly edit my deployment script.  This gets changes into production faster.

More to Come…

This is just scratching the surface of deployment tasks that can be automated with PowerShell.  PowerShell 4.0 and Windows Server 2012R2 add a lot of new cmdlets that can automate things like disk setup.

Horizon 6.0.1 Upgrade Experience

Lsat weekend, I upgraded my Horizon View environment to Horizon 6.0.1.  I wanted to do this upgrade to take a look at the expanded printing support that VMware added in this minor release.

The major improvement included in the Horizon 6.0.1 release is support for virtual printing and location-based printing for Windows Server 2008 R2-based desktops and RDSH-hosted published applications. 

The upgrade isn’t too difficult, and prior to starting it, you should review the compatibility matrix and read the release notes and the directions for patching Horizon 6.

My home lab environment were I performed the upgrade only has one Connection Server and one Security Server.  The steps may be different if you have multiple Connection and Security Servers.


Horizon 6.0.1 has all of the same prerequisites as Horizon 6.0 as well as support for vSphere 5.5 Update 2.

Upgrade Order

The order for upgrading the Horizon components is:

  1. Composer
  2. Connection Servers
  3. Security Servers
  4. Agents
  5. Clients

Prior to upgrading the Horizon server-side components, you should take a snapshot of the server and perform a database backup.

Upgrading Horizon Composer

The first component that needs to be upgraded is Composer.  Prior to upgrading Composer, you will need to take a snapshot of the server and do a database backup.

The upgrade is essentially installing the new version over the old version.  During the upgrade process, you will be prompted for the name of the ODBC DSN connection, database username, and password that were used during the first install.  If you’re using a custom SSL certificate, you’ll need to select it when asked about certificates.

Upgrading the Horizon Connection Server

Once Composer has been upgraded, the next component that needs to be upgraded is the Connection Server.  The steps for this upgrade are fairly simple.

  1. Snapshot the Connection Server
  2. Run the Installer
  3. Click next all the way through to complete the upgrade

Although it is not required, I prefer to reboot the server after the upgrade completes.

Upgrading the Horizon Security Server

The documentation doesn’t mention much about patching Security Servers, so I treated it the same as doing an upgrade.  The process for upgrading a Security Server are much more involved than the process for upgrading a Connection Server, and there are a few extra steps that need to be taken to successfully complete the upgrade. 

Prior to upgrading Horizon, you will need to log into log into View Administrator and complete two tasks in the server section.  The first task is to set a pairing password that will be used when pairing the Security Server to a Connection Server.  The installer will ask for one when you do the upgrade.  This can be set under View Configuration –> Servers –> Connection Servers by highlighting the Connection Server that the Security Server is paired with and selecting More Commands –> Specify Security Server Pairing Password.

The other task that needs to be done before the upgrade is installed is to reset the IPSEC tunneling information.  VMware recommends using IPSEC for all communications between the Connection Server and Security Server.  The IPSEC security settings can be reset by going to View Configuration –> Servers –> Security Servers , selecting the Security Server, and going to More Commands –> Prepare to Upgrade or Reinstallation…

Once you’ve completed these two steps, you will need to log into the Security Server and run the installation package.  When you run the installer, you will be asked for the Connection Server that you’re pairing with and the pairing password.  You will also need to reconfirm the URLs and IPs that the security server uses.  You do not need to remove the existing Security Server before installing the upgrade – you can install it right on top of the existing Security Server instance.

Although it is not required, I prefer to reboot the server after the upgrade completes.

Horizon Agent Upgrade

Once all the server components have been upgraded, the Horizon Agent will need to be upgraded on all full clone desktops and templates, linked clone master images, and RDS servers. 

If you plan to do an upgrade of your ESXi hosts, there is a preferred upgrade order for VMware Tools and the Horizon Agent.  VMware Tools should be upgraded before the Horizon Agent.  If it is not done in that order, the VMware tools install will replace some drivers that the Horizon Agent installs, and you will have to reinstall or repair the Horizon Agent.

Horizon Client

The Horizon Client is usually the last item that gets updated in the environment.  The latest client should be downloaded from the VMware site or from the mobile device app store.

Clients aren’t necessarily tied to a specific version of Horizon.  The latest client can usually be used with an older version of Horizon.  The reverse isn’t always true, and improvements to the PCoIP protocol or other features may not be available when using an older client after an upgrade.

Horizon View 6.0 Part 8 – Installing The First Connection Server

Connection Servers are one of the most important components in a Horizon View environment.  Connection Servers come in three flavors – the standard Connection Server, the Replica Connection Server, and the Security Server – and handle multiple roles including user Authentication against Active Directory, pool management and brokering connections desktops, terminal servers, and applications.

There is almost no difference between the standard Connection Server and a Replica Connection Server.  The Standard and Replica Connection Servers have the same feature set.  The only difference between the two is that the standard connection server is the first server in the pod.

The Security Server is a stripped down version of the regular Connection Server.  It is designed to operate in a DMZ network and tunnel connections back to the Connection server, and it must be paired with a specific Connection Server in order for the installation to complete successfully.  I’ll cover the process of setting up a Security Server in another post.

Installing the First Connection Server

Before you can begin installing the Horizon View, you will need to have a server prepared that meets the minimum requirements for the Horizon View Connection Server instance.  The basic requirements, which are described in Part 2, are a server running Windows Server 2008 R2 or Server 2012 R2 with 2 CPUs and at least 4GB of RAM.

Note:  If you are going have more than 50 virtual desktop sessions on a Connection Server, it should be provisioned with 10GB of RAM.

Once the server is provisioned, and the Connection Server installer has been copied over, the steps for configuring the first Connection Server are:

1. Launch the Connection Server installation wizard by double-clicking on VMware-viewconnectionserver-x86_64-6.x.x-xxxxxxx.exe.

2. Click Next on the first screen to continue.


3.  Accept the license agreement and click Next to continue.


4.  If required, change the location where the Connection Server files will be installed and click Next.


5. Select the type of Connection Server that you’ll be installing.  For this section, we’ll select the View Standard Server.  If you plan on using Horizon View Blast to access desktops, select “Install HTML Access.”  Click Next to continue.


6. Enter a strong password for data recovery.  This will be used if you need to restore the Connection Server’s LDAP database from backup.  Make sure you store this password in a secure place.  You can also enter a password reminder or hint, but this is not required.


7. Horizon View requires a number of ports to be opened on the local Windows Server firewall, and the installer will prompt you to configure these ports as part of the installation.  Select the “Configure Windows Firewall Automatically” to have this done as part of the installation.


Note: Disabling the Windows Firewall is not recommended.  If you plan to use Security Servers to provide remote access, the Windows Firewall must be enabled on the Connection Servers to use IPSEC to secure communications between the Connection Server and the Security Server.

8. The installer will prompt you to select the default Horizon View environment administrator.  The options that can be selected are the local server Administrator group, which will grant administrator privileges to all local admins on the server, or to select a specific domain user or group.  The option you select will depend on your environment, your security policies, and/or other requirements.

If you plan to use a specific domain user or group, select the “Authorize a specific domain user or domain group” option and enter the user or group name in the “domainname\usergroupname” format.


Note: If you plan to use a custom domain group as the default Horizon View administrator group, make sure you create it and allow it to replicate before you start the installation. 

9.  Chose whether you want to participate in the User Experience Improvement program.  If you do not wish to participate, just click Next to continue.


10. Click Install to begin the installation.


11. The installer will install and configure the application and any additional windows roles or features that are needed to support Horizon View. 


12. Once the install completes, click Finish.  You may be prompted to reboot the server after the installation completes.

Now that the Connection Server and Composer are installed, it’s time to begin configuring the Horizon View application so the Connection Server can talk to both vCenter and Composer as well as setting up any required license keys and the events database.  Those steps will be covered in Part 9.