Use SMA Complex Type asset to store product keys in encrypted form

I’m working on a project were we install SQL on VMs via an SMA runbook. The runbook generates a configuration.ini and then kicks of a scripted install. We support SQL 2008R2 Standard and Enterprise and SQL 2012 Standard and Enterprise.

Before this project started, the customer had multiple install sources to support all 4 installation types. This was due to the fact they used the defaultsetup.ini to hardcode which SQL edition was to be installed via the PID property. The sources were available on a share which was available to half the company, so half the company had access to the Product Keys as well. I don’t think this is a very good practice.

To circumvent duplicate install files per SQL version and to better secure the product keys, I decided to add them to the SMA database as an SMA complex type asset.

What is an SMA complex type asset?

Well, first of all it cannot be added using the Azure Pack Admin site. You can add it via the PowerShell cmdlet Set-SMAVariable. The complex type can store hash arrays and other objects. I found out about the complex type via the Building Clouds blog here:

So now you know what a complex type is, here is how I utilized it to store the SQL Product Keys in one SMA asset as encrypted values. Note that Encrypted values in this context mean, the values are encrypted in the SMA database but can be accessed by runbooks directly. Therefore every SMA Admin (there is no other role at the moment) can access the Keys.

First off, I create a hash array like so:

$keys = @{

Next I add it to the SMA database as an encrypted complex type.

Set-SmaVariable -Name SQLKeys -Value $keys -WebServiceEndpoint https://localhost -Encrypted 

When you access the variable using Get-SMAVariable, you will not get the values back since they are encrypted.


When accessed through Azure Pack Admin site:


If you like you can convert the hash array to a PS custom object before committing it to the database, but I don’t see any advantages in doing this right now. The reason the type is unknown is because of the encryption. When the encryption would be turned off, you would see it was recognized as a complex type.


Now that this complex type is committed, you can use a runbook to access its content.


And as an example on how to use it in an actual runbook:


Good stuff!

Tagged , , , ,

SMA Database purge job and SQL AlwaysOn AG

As part of the SMA installation, a SQL job gets created which runs a stored procedure to purge old SMA jobs from the database every 15 minutes by default. See for more information.
When you use SQL AlwaysOn AG for your SMA database this job should be created on the “secondary” node as well to make sure it runs no matter which node is acting as Active.

To copy the job over you can simply right click the Job on the source server (Server which was Active during SMA installation) and select Script Job as -> Create to -> New Query Editor Window from the SQL management studio.

export job

This will generate TSQL code which can be run on the target SQL server which creates the Job for you.

Secondly we want to adjust the jobs (on both servers) with some logic to identify if the job runs on the Active node or not. When it’s not, the job should skip the stored procedure call because it will result in an error.

Navigate to the job steps and edit the only step which is available.
Modify the database connection to master or the job will generate an error on the “Passive” node no matter what extra tsql we put in.
Next modify the command. Replace it with the following code:

exec SMA.Purge.PurgeNextBatch

After this is done on both nodes, the job will only run the stored procedure when the database is in Read_Write mode (no more errors!)
sma sql job alwayson

SCVMM 2012 R2 UR4, move host group assigned services to a cloud (The supported way)!

A while back I posted an item about how to move VMM services (deployed instances of service templates) from host groups to clouds in an unsupported way using some tsql against the VMM database. A link to this post can be found here: Last week I received a comment on this post by David Padley who took my idea and constructed a PowerShell module around it. This script can be found here: Yesterday I had a call with PMs from the VMM product group and I mentioned the lack of this functionality as one of my pain points. I explained that in some cases VMM gets introduced into an environment utilizing host groups only but the implementation will eventually evolve by adding Clouds for self service purposes. During this maturity process of VMM (but before the first cloud gets created) Service templates are used against the host groups. In my experience in Enterprise environments, Service templates are mainly used to deploy Infrastructure components which have a long lifecycle (3+ Years). Eventually a cloud gets created because the company wants to segregate control over the fabric from the controls over VMs (not at the Azure Pack level yet). The company will try to assign VMs to a cloud but the VMs deployed by the Service template cannot be moved. The error message states that if possible to redeploy the Service to the cloud or else let it be in it’s current state as it cannot be moved in this manner. This can come as a shocker because this SQL server cannot be removed as it has become a business critical component. Apparently Microsoft had already listened 🙂 They had another use case where a customer had 1 Cloud with multiple host groups which were geographically dispersed. This company deployed there Service to the host group level to make sure it landed in the correct geographical site and then they needed to move the Service to the Cloud level to facilitate self service (start, stop, etc). I’m happy to tell you Microsoft added functionality as of UR4 which can move deployed services from a host group level to a Cloud using native tooling (supported!). The functionality is not exposed at the GUI level but can be utilized using PowerShell.

Get-SCService -Name "Service"| Set-SCService -Cloud (Get-SCCloud -Name "Cloud")

As of now I’m only able to move the Service to a Cloud from the host group level but not back again. Also moving between Clouds looks unsupported (I cannot do it, NO_PARRAM error). I guess it’s not implemented in it’s final form yet but this implementation is already a mayor improvement! As a side note:

  • fill in surveys,
  • get contacted for a lync session
  • rant for an hour with PMs
  • make another appointment to rant again for an hour

Microsoft listens! It may take a while but if your case is valid, you will be heard.

Losing UDP traffic in a Hyper-V environment with NVGRE and can’t explain why?

I was working on a project where I had to setup Hybrid connectivity. NVGRE isolated tenant network connected up to an Azure vNET using IPSEC VPN over a HNV gateway. Awesome stuff 🙂

After the IPSEC connection was established I deployed a VM in Azure and tested the connection. All looked fine until I tried to domain join the Azure VM to the tenant AD.

I started basic troubleshooting using DNS lookups. Resolve-DnsName resulted in error but Resolve-DnsName -TcpOnly came through perfectly. Other unexplained connectivity issues between tenant VMs which were spread over different Hyper-V hosts where experienced by other project members as well.

As I didn’t setup the fabric, I asked which physical network cards were being used. I was informed that a mix of Broadcom 10G cards where installed. I remembered that Broadcom cards have the encapsulating task offload setting enabled by default and this had caused me issues in the past with another costumer. After disabling this setting on the Hyper-V hosts used with the HNV gateway and the one hosting the tenant DC, DNS lookups where functioning properly. I joined the VM in Azure and verified solid connectivity.

The customer then disabled this setting on all Broadcom NICs on every Hyper-V hosts. The lesson learned here is to test, test, test and do not rely on the default settings of a NIC vendor.

HTH, Ben


WAPack hoster intercepts tenant user provided local admin credentials during VM role deployment (fixed in UR4)

I started this proof of concept because I was testing the integration capabilities of SMA with WAPack. During my initial investigation I found this blog post by Michael Rueefli which saved me a lot of work.

The goal at first was to get more information from the tenant user during VM role deployment and make that information actionable. For example, the tenant users checks a box for an extra service.

I managed to expand the VM role resource definition file with some parameters which where unassigned and exposed them in the view definition sections. After publishing and assigning the VM role I tried to trap the extra information with an SMA runbook which proofed to be very simple. All parameter declaration data is send to the runbook as the $Params object.

Besides the information I was looking for I also saw the local administrator credentials which are provided by the user during VM role deployment. Even though the parameter type of credential is used, the username and password is send in clear text. This really surprised me which is why I eventually wrote this blog post.

Thankfully communication between WAPack, SPF and SMA is secured on the wire using SSL/TLS which makes sure the password can’t be snooped of the wire (easily). What could be kind of a big deal though is the fact that a WAPack hoster could easily intercept the credentials typed by the tenant user who is unaware of this. I noticed the same holds true for parameters of the type secure string.

Note that the data send to the $Resourceobject parameter holds the same data but the actual passwords and secure strings are replaced with “__**__” which of course is a good thing!

I’ve created a simple proof of concept (see below) which stores the local administrator username and password including a value from a secure string in a simple database. I used the Domain Controller example (unmodified) downloaded with the WebPI utility to illustrate.

Before I decided to post this information to my blog, I decided together with my colleague Hans Vredevoort to contact Microsoft about this finding. Microsoft investigated and reported the behavior was due to a bug which apparently had resurfaced as it was patched before. I decided to postpone posting my findings until it was fixed. Now I can safely post this information because Microsoft just fixed this behavior in UR4. For the release notes see:

Create the Passwords Database

Fist lets create a simple database in SQL called passwords. Then add a table to it called PWD. Add the columns as illustrated in the screenshot and save the table. I added the id column and enabled the identity specification so SQL will automatically assigns an integer, this is however not necessary for this proof of concept.


You can use the following TSQL script to create the table:

USE [passwords]
/****** Object:  Table [dbo].[PWD]    Script Date: 9-9-2014 16:34:18 ******/
	[id] [int] IDENTITY(1,1) NOT NULL,
	[UserRoleID] [text] NOT NULL,
	[Username] [text] NULL,
	[Password] [text] NULL,
	[Computername] [text] NOT NULL,
	[Owner] [text] NOT NULL,
	[SecureString] [text] NULL,
	[id] ASC


Add the SMA Runbook Service Service Account to the SQL logins and grant it DBO rights over the passwords database by running the following TSQL script (don’t forget to change the domain and samaccountname of the service account):

USE [master]

USE [passwords]

Create the Runbook

Import the following runbook into SMA. Replace the variable data at the start of the workflow to reflect your own environment and add the SPF tag to the runbook so a trigger can be configured for it.

The runbook depends on a credential asset ‘SCVMM Service Account’ and string variable ‘VMMServer’ to be present to connect to SCVMM. I discovered when using ## symbols with the computername (which you should for scaling purposes), the actual computer names are not reported back. This is why the runbook connects to SCVMM and looks for them.

workflow Store-LocalAdminCredInDatabase
#change the following variables so they match your environment
$ServerInstance = "\cloudos"
$Database = "passwords"
$tableName = "pwd"
$vmm = Get-AutomationVariable -Name 'VMMServer'
$vmmcred = Get-AutomationPSCredential -Name 'SCVMM Service Account'

# with thanks to Michael Rueefli who did this part on his blog post before me
# I adapted his solution so it can handle the Admin:Password combinations as well.
$toexpend = $params.ResourceConfiguration
$toexpend = $toexpend.ParameterValues
$toexpend = $toexpend -split (',') -replace ('"','') -replace ('{','') -replace ('}','')
$output = inlinescript {
$paramhashtable = @{}
Foreach ($p in $using:toexpend)
$value = ""
$key = ($p -split (':'))[0]
#check if key has multiple values
if ($p -split (':').count -gt 2)
#if multiple values exist, append them with : as a separator
$p -split (':') | ?{$_ -ne $key} | %{
$value += $_ + ":"
$value = $value.trimend(":")
$value = ($p -split (':'))[1]
return $paramhashtable

#Gather computernames from SCVMM
$vmnames = inlinescript {
import-module virtualmachinemanager
(get-cloudresource -id $
} -pscomputername $vmm -PSCredential $vmmcred

inlinescript {
$ConnectionTimeout = 15
$QueryTimeout = 600
$BatchSize = 50000
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $using:ServerInstance,$using:Database,$ConnectionTimeout
$VMRoleAdminCredential = $using:output.VMRoleAdminCredential
foreach ($VM in $using:vmnames)
$query = "INSERT INTO dbo.$using:tableName (userroleid,username,password,ComputerName,Owner,SecureString) VALUES ('$($using:resourceObject.UserRoleID)','$($VMRoleAdminCredential.split(":")[0])','$($VMRoleAdminCredential.split(":")[1])','$VM','$($using:resourceobject.owner)','$($using:output.DomainControllerWindows2012SafeModeAdminPassword)')"
Write-Output "adding row to database using query"
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand
$command.Connection = $conn
$command.CommandText = $query
$command.ExecuteNonQuery() | out-null

Add SPF as a tag to the runbook.


A trigger can now be added by going to VM Clouds -> Automation. In this case we target the VMRole object with action create.



I’ll assume you have implemented the Domain Controller resource and made it available through a plan for the tenant user.

The tenant user will deploy a Domain Controller VM role through the gallery which triggers the runbook. The runbook will parse the data, check for the correct computer names and add the data to the database.



As you can see this implementation is very simple and only mend to show how easy it is to intercept supposedly secure data (until UR4).

Note that another runbook should be built for scaling-up and down actions because very little data is provided to SMA when this happens.


Another example is the 2012R2 workgroup gallery item which I also did not modify. In this case I deployed 2 instances during initial deployment.





Update November 9th, 2014.

Today I upgraded my test environment to UR4 and indeed verified the fix is implemented by removing the values from the Params object.
However, secure strings are still send in the Params object in the clear. I send an e-mail to Microsoft to notify them the issue is fixed only partially.


SMA Runbook: Enable Kerberos Constrained delegation for Hyper-V Live Migration and ISO sharing in AD and SCVMM

Today I’ll share a little SMA runbook which enables Kerberos Constrained Delegation for all your Hyper-V hosts which are available in SCVMM.
It does this by configuring the Active Directory side of things and the SCVMM side of things and it is based on my earlier post which can be found here.
When a tasks fails, an error will be logged in the SMA runbook history.

For this runbook to work you need to register some SMA assets:
String Variable VMMServer and DomainController
PSCredentials “AD Service Account” and “SCVMM Service Account”

Note that the credentials added as asset should have enough privileges to perform the tasks!

The runbook enables live migration for all subnets which can be adjusted quite easily if needed.
I’ve named the runbook Reset-HyperVConstrainedDelegation because this is exactly what it does, it RESETS the constrained delegation (overwrites) to include all known Hyper-V hosts and library servers.

Hope this helps you! Ben

workflow Reset-HyperVConstrainedDelegation
$vmmcreds = Get-AutomationPSCredential -Name 'SCVMM Service Account'
$vmmserver = Get-AutomationVariable -Name 'VMMServer'
$domaincontroller = Get-AutomationVariable -Name 'DomainController'
$adcreds = Get-AutomationPSCredential -Name 'AD Service Account'
$hosts = inlinescript {
Import-Module virtualmachinemanager
get-scvmmserver -computername $using:vmmserver | out-null
} -pscomputername $vmmserver -pscredential $vmmcreds
$libraryservers = inlinescript {
} -pscomputername $vmmserver -pscredential $vmmcreds

foreach -parallel ($h in $hosts) {
inlinescript {
Import-Module activedirectory
$kcdentries = $using:hosts | ?{$_.fqdn -ne $using:h.fqdn}
[array]$ServiceString = @()
foreach ($k in $kcdentries)
$ServiceString += "cifs/"+$k.fqdn
$ServiceString += "cifs/"+$k.computername
$ServiceString += "Microsoft Virtual System Migration Service/"+$k.fqdn
$ServiceString += "Microsoft Virtual System Migration Service/"+$k.computername
foreach ($l in $using:libraryservers)
$ServiceString += "cifs/"+$l.fqdn
$ServiceString += "cifs/"+$l.computername
[string]$filter = 'samaccountname -like "' + $h.computername + "$" + '"'
Get-ADObject -Filter $filter | Set-ADObject -replace @{"msDS-AllowedToDelegateTo" = $ServiceString; "userAccountControl"="16781344"} -ErrorAction Stop
$fqdn = $using:h.fqdn
Write-Error "Failed AD Configuration for host $fqdn"

} -pscomputername $domaincontroller -pscredential $adcreds
foreach -parallel ($h in $hosts) {
inlinescript {
Set-SCVMHost -VMHost $using:h.fqdn -EnableLiveMigration $true -MigrationPerformanceOption "UseCompression" -MigrationAuthProtocol "Kerberos" -UseAnyMigrationSubnet $true -ErrorAction Stop| out-null
$fqdn = $using:h.fqdn
Write-Error "Failed SCVMM Configuration for host $fqdn"

} -pscomputername $vmmserver -pscredential $vmmcreds


Bare Metal Post Deployment Blog series on

I’m very pleased to announce I have released a 5 part guest blog series on about Hyper-V Bare Metal Post Deployment.

The crew kindly offered to publish this huge peace of work on their platform for which I am very grateful and honored.
A big thank you to Hans Vredevoort and Marc van Eijk for this opportunity.
And thanks you Darryl van der Peijl and Mark Scholman for reviewing.

Enjoy! Ben

Hyper-V Hyper-threading Enabled or Disabled?

Ben Armstrong said at TechEd Australia 2013 that from a Hyper-V perspective it doesn’t really make a difference, performance-wise, if you use HT or not for Hyper-V. You can find the session here and at 35:40 the HT subject begins.

After careful study and testing I’ve come to the conclusion that enabling or disabling HT actually can impact performance and configuration in some cases, especially when high density is NOT the prime objective.

In this blog I share my notes about the subject hoping it can help you when you are at the design phase.

First, enabling HT will impact the way you size high demand / big scale VMs like SQL servers because the Hyper-V vNUMA calculation algorithm does not take into account that half of the LPs (Logical Processors) are actually HT LPs, this will force you to assign (socket LPs + socket HT LPs +1LP from another socket to cross the NUMA boundary) vCPUs when you want to make use of vNUMA.

Second, enabling HT will impact the scheduling engine of Hyper-V because it will actually take into account that HT is enabled and facilitates, on a best effort basis, a sort of virtual HT to the VM. In a high demanding environment, this can lead to unpredictable performance on a positive or negative way. You can read more about the way scheduling works in the Hypervisor Top Level Functional Specification v4.0a published by Microsoft.

And last, HT will impact the way the RSS / VMQ indirection table is build up because when enabled, you have to skip the HT LPs in your assignments (you should therefore enable or disable HT on all your hosts to have a consistent configuration).

The real answer to enable HT or not will depend on the type of workloads and demand you will be running. This is in no way intended to be the Holy Grail in deciding to enable HT or not.
These are just my notes, it always depends!


Live Migration with Fibre Channel HBA Blocked: No Physical Port Available

Was confronted today with a Hyper-V host which did not accept inbound Live Migrations of VMs equipped with virtual fibre channel.


At first I thought about the vPort issue I blogged about earlier here. This was however not the case (not only because the error states no PHYSICAL ports are available instead of VIRTUAL ports, I should read error messages before I start troubleshooting them!). My function described in the earlier blog post didn’t show any results (which is good 🙂 ) but for one host an error was raised: Get-CimInstance : The WS-Management service cannot process the request. The class MSFC_FibrePortNPIVAttributes does not exist in the root/wmi namespace.

I triple checked: gwmi -Namespace root\wmi -List | ?{$ -like “MSFC*”} the class was indeed missing… what happened?

Something (or somebody) had decided to rebuild the WMI repository. There are actually a lot of dangerous blogs written about this wonder method to fix e.g. VMM connectivity issues. Better think twice is my advice before you do something like this. Rebuilding the WMI repository can have some nasty side effects like missing classes 😐 To fix this issue run the following command: mofcomp -N: “C:\Program Files\Microsoft System Center 2012 R2\Virtual Machine Manager\setup\NPIV.mof” A warning is actually given that the MOF file is missing information for a potential future rebuild and because of this, the content won’t survive the rebuild!

Reboot the host and you are back in business!



Migrate Azure Pack databases to new SQL Instance

In my environment I have a very simple Azure Pack implementation. It only serves as an administration point for SMA so I have set it up very minimalistic.
My environment exists out of the following components:

  • WindowsAuthSite
  • AdminAPI
  • AdminSite
  • TenantAPI

The TenantAPI is mandatory, otherwise the AdminSite won’t function.

In my case I thus have only 3 databases:

  • Microsoft.MgmtSvc.Config
  • Microsoft.MgmtSvc.PortalConfigStore
  • Microsoft.MgmtSvc.Store

I will describe here how to move these database to a new SQL instance (like with my previous post about the SMA database). This procedure should also work for fully implemented Azure Pack environments, including distributed setups (you just have to visit some extra servers 😛 ).

The Azure Pack database migration is a lot simpler than the SMA database migration since the only database referenced I have found existed in the connection strings of the web.config files.

First you stop all Azure Pack web services. Then you move over the databases and logins + SQL Accounts (see my previous post about SMA database migration). The “old” databases will be configured offline for the time being and detached / deleted later on.

Then the real challenge begins J

The web.config files are encrypted so they should be decrypted first. You can do this through the following PowerShell code:

Get-MgmtSvcNamespace | %{
Unprotect-MgmtSvcConfiguration -Namespace $_

This code will unencrypt all web.config files associated with the namespaces installed on the local server. When this has run you can go to IIS manager and reconfigure the connection strings.

(Side note) When you screwed up your SQL accounts associated with Azure Pack or you need to do something else with them, here you can find there passwords!

When every connection string is adjusted, you can run the following PowerShell code to encrypt the web.config files again:

Get-MgmtSvcNamespace | %{
Protect-MgmtSvcConfiguration -Namespace $_

Start the web sites and you’re done!

HTH, Ben