Join Domain Workflow for Orchestrator

Hi There

Well finally getting to the pointy end here. VMware broke the sysprep process in version 5.1 such that a guest would not join the domain. So for my environment I needed to create a workflow that did this process for me.

The workflow shown here can be used within a larger workflow to provision a Windows guest.

So you need to have prepared your Template as stated in here.

You need to have created a similar script as stated here to join the guest to the domain.

Then you need to configure your workflow like this:

joinDomain workflow

So the first section highlighted in yellow basically checks for the existence of powershell directories on your guest. It first checks for c:\windows\system32\windowspowershell\v2.0 if found, sets the path for version 2 of powershell. If the check fails, it checks for c:\windows\system32\windowspowershell\v1.0 if found, sets the path for version 1 of powershell. If the check fails, it sets the error status and exits.

The second section highlighted in green checks the workingDirectory (c:\bin) exists. If not found Exits (probably should set the error state). If it is found, it then copies the script joinDomain.ps1 to the workingDirectory. If the copy fails, Exits (probably should set the error state on this too). If the copy is successful, then continue.

The third section highlighted in light blue, gets the environment variables from the server, then executes the joinDomain.ps1 script on the guest. It then wait until there is a return code for that process to finish. It then checks to see if the process exited successfully. If it did not exit with return code 0 then the script Exits (hmmm need to set the error state here). If it did exit with return code 0 then waits for the fully qualified name of the server to show up in vmtools. Sets the error code to success and exits.

I have included the documentation for this workflow here. All sub workflows are default library workflows found in Orchestrator.

Hope this helps.

Join Domain Script without stored credentials

Hi

Well here I am for another instalment  a script to join a server to the domain without storing any credentials on the server.

Why I might hear you ask? Well in a VMware environment, storing one of these scripts in your Windows Template will make it easy for you to execute it remotely so that it could be completed by a workflow.

Everyone stores their scripts differently. When I have scripts that I need to store on a host, my Unix background comes out as I normally have a folder located at c:\bin on a server to hold them.

As systems get smarter and you can store scripts in a script repository centrally, I still use the c:\bin folder as the execution folder for these centrally stored scripts.

For this script to run, you need to have set the executionpolicy mode to unrestricted in your powershell console (both 32bit and 64bit). If you have issues with this, I have posted an article here.

The script takes advantage of the Add-Computer powershell command found here.

As you can see in the referenced document, you could modify this script to place the computer into an OU within the domain and a lot of other options.

joinDomain.ps1

 param([string]$domain, [string]$adminUser, [string]$password)  
 $credential = New-Object System.Management.Automation.PsCredential($adminUser, (ConvertTo-SecureSTring $password -AsPlainText -Force))  
 Add-Computer -DomainName $domain -Credential $credential -passthru  

This script takes three arguments, The Domain you want to join, The Administrative User (in DOMAIN\USERNAME format) and The Administrative User Password.

All arguments are passed in as plain text.

I am not going into detail of the script as the document referenced earlier explains the functions used quite well.

Okay so I am off for now, see you in the next snippet.

Setting Power Shell Execute Modes in VM Guest Templates

Hi

Well I am slightly restructuring my posts into smaller bite sized chunks. Why? well because I wanted to re-use bits and well the easiest way to make sense was to post about smaller bits of information.

So today I am going to talk about Power Shell Execute Modes. Well not so much about them but how I have set mine up in my VMware environment to enable easy execution of scripts in my environment.

Okay so I am sure most people who use VMware know about templates so will reference this as assumed knowledge.

In my environment I currently have 3 Templates, SUSE Linux Enterprise Server 11 Service Pack 1, Windows Server 2008 R2 and Windows Server 2012.

For the Windows Servers, I set the execution policy as unrestricted both in the 64Bit and 32Bit shells.

Why? So that I don't have to sign all my scripts. Is this an issue, from my experience only if you are operating in a highly secure environment.

So how is this done? Simple:

Open each console on the Template and type:

set-executionpolicy Unrestricted

You should see something like the following

Once your done, shutdown the machine and convert back to a template and we are ready.

Well I am off now, see you in the next snippet.

Converting SQL Server SIDs to login names

In the past, whenever you used MS SQL mirroring, or log shipping, you'd get this problem when you tried to bring a database back up after a failure event. The problem was that the security principals stored in a database mapped back to security principals stored in the master database, and they did so by SID. So even if you had security principals with the same name in your master database on your fail-over node, they wouldn't match up and you wouldn't be able to log in any more.

In SQL Server 2012, Microsoft introduced the idea of contained users, which are exactly what they sound like; users exist wholly within the database in which they're defined. So when you have a fail-over event, your database is back up and accessible straight away.

Unfortunately it created an issue. It's not really SQL Server's fault. We were developing some new applications that used Entity Framework as an ORM layer. We were seeing lots of errors turning up in the SQL Server run time logs similar to:

Login failed for user 'S-1-9-3-3987763894-1196622702-682210441-1728208786.'. Reason: Could not find a login matching the name provided. [CLIENT: XXX.XXX.XXX.XXX]

Now, for our own reasons many of our contained users had the same login name (and even the same password), but we had distinct connection strings for every database and data intent. And it would appear that connections to the SQL server were trying to cross domains and access databases they didn't have access to. Sometimes connections/queries were being attempted against the master database for no apparent (deliberate, explicit in code) reason.

I'm not sure exactly where to place the blame, that's the developer's call, but it could have been explicit code bugs, or a quirk in EF's connection pooling making assumptions where the server name and the login/user names and passwords that they must be interchangeable (which in the old world they may have been). But it would be really interesting to see which logins were trespassing. For what it's worth I also saw similar behaviour in Service Bus for Windows, despite it having distinct connection strings for Management, Container, and Gateway databases - I don't think they had contained users in mind at any point when making that software, which is odd given its origins in Azure.

Anyway, what's the problem? The problem is that the SID is locked away in the sys.database_principals table as a blob, and the only place you ever see the familiar string representation (S-1-9-3-...) is in the error log. If it were a Windows user SID (S-1-5-21-...) we could map it straight to a username easy-peasy, but not for SQL SIDs.

There are T-SQL functions for translating SIDs to login names and back again but they either didn't work for me, or expected/returned the binary representation of the SID which was useless.

So I wrote some Powershell to solve my problem. And here it is:

Get-DatabasePrincipalsList.ps1

param (
        [Parameter(mandatory=$true)]
        [string]$ConnectionString
)

function Get-SIDStringFromBytes ([byte[]] $bytes) {
  ( new-object System.Security.Principal.SecurityIdentifier($bytes,0) ).ToString()
}

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $ConnectionString
try {
 $conn.Open()
} catch {
 Write-Error "Unable to open connection to the database server."
 Exit 1
}

try {
 $conn.ChangeDatabase( "master" )
} catch {
 Write-Error "Unable to change to master database: $_"
 $conn.Close()
 Exit 1
}

$dbListQuery = "SELECT name FROM sys.databases WHERE name <> 'master'"
$sqlCommand = new-object System.Data.SqlClient.SqlCommand( $dbListQuery, $conn )

try {
 $sqlReader = $sqlCommand.ExecuteReader()

 $databaseList = @()
 while ( $sqlReader.Read() ) {
  $databaseList += $sqlReader.Item("name")
 }

 $sqlReader.Close()
} catch {
 Write-Error "Error retrieving list of databases from master: $_"
 $conn.Close()
 Exit 1
}

$databaseList

$principalsQuery = "SELECT name,type_desc,sid FROM sys.database_principals WHERE sid IS NOT NULL AND type_desc IN ('SQL_USER','WINDOWS_USER')"

$principals = @()
  
foreach ( $database in $databaseList ) {
 try {
  $conn.ChangeDatabase( $database )

  $sqlCommand = new-object System.Data.SqlClient.SqlCommand( $principalsQuery, $conn )

  $sqlReader = $sqlCommand.ExecuteReader()

  while ( $sqlReader.Read() ) {
   $principal = "" | Select Name,Database,Type,SidHex,Sid

   $principal.Name = $sqlReader.Item("name")
   $principal.Database = $database
   $principal.Type = $sqlReader.Item("type_desc")
   $principal.SidHex = $sqlReader.Item( "sid" )
   try {
    $principal.Sid = Get-SidStringFromBytes $principal.SidHex
   } catch {
    $principal.Sid = "Unknown"
   }

   $principals += $principal
  }

  $sqlReader.Close()
 } catch {
  Write-Error "Something went horribly wrong reading database principals from $database"
 }
}

$principals

$conn.Close()

It takes a single argument, which is the initial connection string. If the user you're running the script as has sa rights on the database, for example, you can invoke it like this:

.\Get-DatabasePrincipalsList.ps1 -ConnectionString "Server=MyMSSQLServer;Integrated Security=true"

Any .Net SQL data provider connection string is fair game, but the first thing the script attempts to do is change to the master database. It's going to Just Work™ if you're using an account with sa, but I'm sure there are less brutal ways to get rights; I'm not a SQL Server guy.

After that, it's going to retrieve a list of databases from the sys.databases table, then iterate through them to collect a list of principals of type SQL_USER or WINDOWS_USER. Once we've got them we invoke some System.Security.Principal.SecurityIdentifier magic on the blob containing the binary representation of the SID (which just comes back as an array of bytes, byte[]) to turn it into the string format we know and love. That's this bit:

function Get-SIDStringFromBytes ([byte[]] $bytes) {
  ( new-object System.Security.Principal.SecurityIdentifier($bytes,0) ).ToString()
}

The script outputs an array of all the security principals found, so you can Where-Object your heart out to find the one you're looking for and the database it originated in.

For me, I get stuff like (details obfuscated to protect the innocent):

Name     : someusername
Database : SomeDatabase
Type     : SQL_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-9-3-3574901072-1311533442-3147823503-3515760278

Name     : AppUser
Database : SomeDatabase
Type     : SQL_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-9-3-2165426903-1168750583-1031006359-282436966

Name     : DOMAIN\JBloggs
Database : SomeDatabase
Type     : WINDOWS_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-5-21-1039932387-1978834618-1912882637-2728

Exciting, isn't it?

Playing with Microsoft's binary encoding

A friend asked me to help him out with a project. He was performing a penetration test on a Silverlight application but had fallen at a fairly early hurdle - communications between the Silverlight front-end and the back-end web services was in a format that he didn't recognise. Firstly it was gzipped, that much was obvious, but once it had been uncompressed it appeared to be nonsense interspersed with bits of clear-text, but to me it looked suspiciously similar to something I'd seen before when I'd been tasked with reading messages out of an MSMQ dead-letter queue.

The messages on the MSMQ queue contained a WCF (Windows Communication Foundation) payload, wrapped in a bit of mystery padding. My task at the time was to decode the WCF message to extract certain pertinent bits of information so processing messages could be reconstituted and re-inserted onto the queue now that the data problems that made them wind up on the dead-letter queue in the first place had been resolved, but I digress.

Now when you're sending stuff across the wire, data packing efficiency is everything. The WCF message in this case was a SOAP message, but XML's very wordy and inefficient so Microsoft kindly, and by default, encode it in a proprietary format based on a system of dictionary substitutions; well-known XML elements are replaced with dictionary look-up references, and other data-types are efficiently packed in various cunning ways. There are some great articles out there on the tubes that describe the encoding in great detail, which carries the MIME type application/msbin1, but I was in a hurry and didn't have time to write my own decoder from first principles. There must be another way.

There is! I found a great article that described driving methods buried deep inside the System.ServiceModel namespace where mortals don't usually need to tread that, when given a binary-encoded message, spits out a System.ServiceModel.Channels.Message object, which is great and it worked for me in that case and actually, while writing this, I've come across another implementation of the same technique I used that's quite a bit better. You can find it here.

However, it's all a bit messy when the encoded payload isn't even a System.ServiceModel.Channels.Message in the first place, as was the case in the scenario I started this ridiculous story describing. I also ran into issues (probably resolvable) where my messages were already marked as read and it was a violation to read them twice, so I couldn't access the message body directly anyway and had to just call ToString(). Rubbish. Besides, we needed to be able to encode messages as well as decode them. Creating a System.ServiceModel.Channels.Message message out of thin air looked much too difficult for my very small brain. I'm only a sysadmin.

The general case for encoding and decoding arbitrary XML documents using Microsoft's binary encoding fell out of the desire to encode, which I ended up implementing like this:

using System.Xml;

public Stream GetBytes(Stream inputStream)
{
    MemoryStream ms = new MemoryStream(4096);

    var reader = XmlReader.Create(inputStream);

    var writer = System.Xml.XmlDictionaryWriter.CreateBinaryWriter(ms);
    writer.WriteNode(reader, true);
    writer.Flush();

    ms.Seek(0, SeekOrigin.Begin);

    return ms;
}

This gives you back a Stream full of nicely encoded content to do with as you wish. In our case it was then gzipped and POSTed to the service we were trying to exploit.

To decode, it's the inverse, except this time we want an XmlDocument back so we go and Load() an XmlDocument out of the XmlDictionaryReader we've created to do the decoding.

using System.Xml;

public XmlDocument GetXML(Stream inputStream)
{
    MemoryStream ms = new MemoryStream(4096);

    byte[] buffer = new byte[4096];
    int count = 0;
    while ((count = inputStream.Read(buffer, 0, buffer.Length)) > 0)
    {
        ms.Write(buffer, 0, count);
    }

    ms.Seek(0, SeekOrigin.Begin);

    var reader = XmlDictionaryReader.CreateBinaryReader(ms, XmlDictionaryReaderQuotas.Max);

    XmlDocument document = new XmlDocument();
    document.Load(reader);

    return document;
}

What's going to catch you out is that despite that the classes that do all the magic, XmlDictionaryReader and XmlDictionaryWriter, being in the System.Xml namespace, they're actually contained inside the System.Runtime.Serialization assembly, not System.Xml as you'd expect.

And there we had the makings for arbitrarily manipulating payloads for the target application which, as it turned out, had data-validation holes you could drive a truck through.

It's actually pretty simple to do this work in Powershell as well. If anyone cares, leave a comment.

Cleaning up the Lync contacts database

So I found myself in a situation where I needed to programmatically destroy the contact lists of every user in a Microsoft Lync database. Wipe the slate clean, if you will, and let everyone start again. This was the result of management changing their mind about pre-populating everyone's contact lists with a base set of contacts after the fact. It's hard to find information on how to do this in a supported way. In fact, I couldn't find any details at all. What I did find was a mention of calling a stored procedure on the Lync RTC database called ImpDeleteContactGroups2. The ImpDeleteContactGroups2 procedure takes a single argument of the form user@host and does two things.

1. First, it attempts to resolve the user@host input into a numerical ResourceId from the Resource table, and then
2. It calls the ImpDeleteContactGroups procedure with the numeric id

Its the ImpDeleteContactGroups proc that does all the heavy lifting, though in many ways it's a simpler proc than ImpDeleteContactGroups2. As you may have guessed from the name, all ImpDeleteContactGroups actually does is delete contact groups and the association of contacts to contact groups for the user passed in as the original argument. Once it's been run, the user will still have all their contacts associated with their account, but since there are no groups left, the contact list will appear to be empty! But strange things will happen, like Lync will (if it's configured to) populate Outlook with contact details for contacts you can't see any more. All of this leads us to the partner procedure ImpDeleteContacts2, which also has a sibling proc called ImpDeleteContacts. There are no prizes for guessing that ImpDeleteContacts2 takes a user@host style argument, resolves it into a numeric ResourceId, and then calls ImpDeleteContacts to do the actual work of cleaning up contacts. My weapon of choice for this task was PowerShell, but there's nothing stopping you from just firing off queries at the SQL database from inside SQL Server Management Studio. Consider this for an SQL only approach:

USE [rtc]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[ImpDeleteContactGroups2]
  @_Owner = N'some.user@yourdomain.com'

SELECT 'Return Value' = @return_value

EXEC @return_value = [dbo].[ImpDeleteContacts2]
  @_Owner = N'some.user@yourdomain.com'

SELECT 'Return Value' = @return_value

GO

Of course this will suck if you've got lots of users to process. I ended up going for something like this, which drives the Lync command Get-CSUser to provide a complete list of every user registered in your Lync database. NOTE: This will clear all the contact lists and all the groups from every user in your Lync database. I am not responsible if you run this without understanding that.

Import-Module 'C:\Program Files\Common Files\Microsoft Lync Server 2010\Modules\Lync\Lync.psd1' $LyncSQLServerInstance = "YOURSERVER\rtc"; $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "server=$LyncSQLServerInstance; Integrated Security=sspi; Database=rtc" $sqlConnection.Open() $sqlCommandDeleteGroups = new-object System.Data.SqlClient.SqlCommand("ImpDeleteContactGroups2", $sqlConnection) $sqlCommandDeleteGroups.CommandTimeout = 120 $sqlCommandDeleteGroups.CommandType = [System.Data.CommandType]::StoredProcedure $sqlCommandDeleteContacts = new-object System.Data.SqlClient.SqlCommand("ImpDeleteContacts2", $sqlConnection) $sqlCommandDeleteContacts.CommandTimeout = 120 $sqlCommandDeleteContacts.CommandType = [System.Data.CommandType]::StoredProcedure Get-CSUser |% { $_.SipAddress -replace "^sip:","" } |% { $sqlCommandDeleteGroups.Parameters.Clear() $sqlCommandDeleteGroups.Parameters.Add("@_Owner", $_) | Out-Null Write-Host "Deleting groups for: " $($sqlCommandDeleteGroups.Parameters[0].Value) $sqlCommandDeleteGroups.ExecuteNonQuery() | Out-Null $sqlCommandDeleteContacts.Parameters.Clear() $sqlCommandDeleteContacts.Parameters.Add("@_Owner", $_) | Out-Null Write-Host "Deleting contacts for: " $($sqlCommandDeleteContacts.Parameters[0].Value) $sqlCommandDeleteContacts.ExecuteNonQuery() | Out-Null }

Out for a walk

Hey, it's the day for blog posts.

I'm off for a walk on Friday. I'll be joining a friend of mine for four days on a section of the South West Coast Path along the southern face of Cornwall. Simon's doing the whole path, end to end, and he's doing so after coming straight out of a four week bicycle tour of Scotland and a cycle trek from Roscoff to Santander. He carried his home on his back the whole way, and I think he's only spent a week under bricks and mortar in about three months.

Simon's raising money for the Force Cancer Charity, and you can sponsor him here

I reckon he'll be fitter than me.