Last night I was nearly out of my famous luck, I’d accidently deleted the wrong Computer Association for an OSD build that had successfully completed on a VIP’s laptop, only to find that some software (Non-MS Disk Encryption) was installed onto it post-OSD which caused it to lock down water tight and become unusable, making the restored VIP’s data inaccessible on the device, and the MIG file was no use due to the Recovery Key disappearing along with the deleted Computer Association. Fortunately, we recovered the laptop, and the data was lifted off of it and restored to another device successfully. Win, but, many hours where lost exploring various options when we could be well on the way to recovering the data onto another device shortly after the original device got locked down.


Not a good place to ever be in.


It raised an interesting question around how much risk is there in using User State Migration, the answer to which is, it depends on if the Perfect Storm has just arrived to kick your butt, with everything lining up so as to put you deep into the Incident Pit. What can put you there is losing either the Recovery Key or the MIG file, both need to be well looked after during the window between creation of a Computer Association and a Migration file and when the Server and Database are backed up next, this is a critical time for both the Recovery Key and the Migration file as one literally exists in only one place, the ConfigMgr Database, in a table called StateMigration, and the other is not in a backup set (if it’s on a locally attached disk for example, and you lose it).


The Recovery Key


There are several approaches to getting your Recovery Key back, If you have backed up your Database since you created the Computer Association you’d be able to restore it somewhere so that you can get at that Recovery Key, job done, or if you’ve backed up the OS since the Computer Association was created, and the OS backup backed up the Database you’ve got another way to get at the Recovery Key.


There isn’t much else you can do once you lose your Encrypt\Decrypt key that was used to secure a Migration file, other than taking it to a security company to see if you can get it cracked open, most unlikely unless they can compromise the encrypt algorithm (EAS) used to encrypt it. But hey, this is what we want right? Secured data.


Preventative measures


This is really the core of the posting, I produced a SQL-side solution to this problem so that the Recovery Key is kept in a secured Database post-deletion via the ConfigMgr Console, it will be deleted from the StateMigration table in the ConfigMgr Database but it won’t be gone forever, as it’ll have been copied to a table in a separate Database at the moment the deletion takes place using a SQL Trigger added to the ConfigMgr Database itself. This is real-time cloning of a deleted Computer Association for archival purposes, adding a ‘Just in case’ option to your repertoire for restoring user data during a Perfect Storm.


The TSQL code below will do the following:


  • Create a new Database called CUSTOM_StateMigrationDatabase
  • Create a new user in the newly created CUSTOM_StateMigrationDatabase Database called NT AUTHORITY\SYSTEM
  • Adds the newly created user NT AUTHORITY\SYSTEM to the db_owner role for CUSTOM_StateMigrationDatabase Database
  • Create a StageMigration_BACKUP table in the CUSTOM_StateMigrationDatabase
  • Create a Trigger called StateMigrationTrigger in the ConfigMgr Database that is fired when a deletion takes place on the StateMigration table
  • Displays the newly created Trigger to confirm it has been created
  • Renders the rows in the StateMigration and StateMigration_BACKUP tables


Before we get to the action let’s talk about supportability. Normally a solution like this I’d leave at a customer and not blog, like many other solutions that I’ve implemented that have fixed something or got customers out of trouble such as locking themselves out of their Hierarchy entirely, simply because we’re manipulating the ConfigMgr Database which breaches the cardinal rule of not altering objects there unless under guidance from MS CSS. Does this mean I treat the ConfigMgr Database as if it’s a sand-pit to manipulate with Management Studio? Heck no, and neither should you, but with this solution I’ve put the table receiving the archived Computer Associations in a different database on the same Site server, and the only change on the ConfigMgr Database itself is the addition of a new Trigger that will fire when a DELETE DML request is processed for the StateMigration table. Very understandable stuff, nothing exotic here, a DBA would be able to add value by explaining the consequences of adding a trigger to a database, If you want to implement this solution but have questions around creating a Trigger on the ConfigMgr Database I’d suggest opening an Advisory case with MS CSS to get a supportability statement for setting this solution up. I will not provide you with any support, or accept any responsibility for making this change in your environment, this is for you to think through and implement if you desire, as I said open a MC CSS case if you want sign off from Microsoft to take this outside of your development environment and into production.


Now to implement the solution. Open SQL Management Studio, connect to your ConfigMgr Database server with owner rights and paste the TSQL code found in the following attachment


 TSQL Script download


You will need to do two things to this TSQL script before you execute it:


  1. Replace <YOUR SITE SERVER DATABASE NAME> with your Site servers database name then execute the SQL script.
  2. Replace <HOW LONG TO KEEP ARCHIVED COMPUTER ASSOCIATIONS> with a numerical value


NOTE: Make sure you remove the <> characters, but then if you are not a sharp enough cookie to already know that, should you even be doing this?


Next time you delete a Computer Association it’ll be copied to the new table StateMigration_BACKUP in the newly created database, allowing you to all importantly get at the Recovery Key again, and using further techniques turn it into a viable key which can be used with the Windows Easy Transfer tool to manually unpack secured Migration (MIG) files, or to use the Recovery Key with the LOADSTATE executable.


The Migration file (MIG)


Migration files are deleted by the State Migration Points retention harvesting schedule, which you set as a configuration property of the State Migration Point, it’s probably unlikely that you have it set to 1 day, but if you do then that’s all you’ve got before the file is deleted, the most common value for this setting is 30 days, however for those of you doing a lot of deployments who will run out of storage space for Migration files this value can be set a lot lower. Other than this there isn’t much else to consider other than making sure the MIG file hangs around long enough on the file system for you to perform any required recovery using it. It is less prone to being accidently removed than a Computer Association that’s for sure.


I hope this guide helps you to get something in place that prevents you from losing your customers data when that Perfect Storm arrives. Keep in mind boys and girls, the Database is the holy of holies and not to be manipulated beyond creating something as simple as a Trigger as part of your database administration and maintenance regime.

Parents Comment Children
No Data