Moving a WMDB across environments

By Max Ranzau


mblsFrom the Desperate Measures Dept. This article is a result of half a days work resurrecting a WM database back from the grave, or more to the point, an old environment in a different domain. There were no buildingblocks, only a full database backup. Ever the optimist, I figured it would be an easy win: A few minutes of restoring, running the /lockedout on the Workspace Manager console, adding a new user to the Technical Manager admin role. Boy, was I wrong… When the full scope of what I had to do dawned on me, I was shouting repeated references to the cocktail above.

Here’s the skinny, I had to move a Workspace Manager database from one environment to another, this meant a new domain too. On the new SQL server, which was going to be the new home for the WM datastore, I restored the db backup and set up a SQL user for it as well, finally installing a WM console and configured it to hit the datastore. Obviously locked out, run pwrtech.exe /lockedout. Then this happens:

Here it is again for the search engines: Access is only permitted from the following domains: Yes, the truth slowly dawns on you: You’re locked out of your own DBMS for real and the only saving grace (/lockedout) meant to pull you out of that s***hole, does absolutely bupkis! I’ll spare you all the futile attempts I went through to solve this and cut straight to the chase to what worked. It’s more than likely not be the quick solve you’re hoping for, but at least it’ll get your WM statastore back in one piece.

Step 1: Identify the former domain FQDN

While I recalled the netbios name of the old environment, I was unsure of the FQDN of the old domain where the WM database used to live. This can be identified like this: Open the WM backup in SQL Mgment studio, open the table tblObjects, look for objects of lngObjectType=64 (click to enlarge)

Step 2: Build a sandbox

In a VM sandbox build, a new clean Domain Controller with the same fqdn as found in step 1. Yes, there’s no use crying about it. It will take the time it takes and it’s the only way as things are as of WM 2015 SR1. This box is temporary and can be tossed out when you’re done with this long sad exercise. OS shouldn’t matter too much as we only need this box to import and cleanse the WM datastore of whatever gunk is preventing the unlock.

Step 2 – Prep the sandbox

  • On top of the sandbox DC, install same or newer version of the DBMS which the database came from or you likely won’t be able to restore your WM database. I’m presuming this to be MSSQL. Be sure to set it up for mixed mode authentication.
  • Restore the WM database into the sandbox DC/SQL.
  • Install the WM Console MSI, preferably the same version as you had in the old environment, however a newer one should [probably] work nicely as well.
  • Run pwrtech.exe /lockedout (copy the path from the console shortcut) and enter your SA credentials and login in the dialog shown above.

And poof! Another roadblock went up. I vaguely recalled from the old environment, that Domain admins were technical managers, but I’d have expected Workspace Manager to be smarter than this:

Sure enough, for the S&Giggles, I tried creating another domain admin and got the same result. Only one way forward then…


Step 3 – Create a non-admin user and restore access

  • Create a normal user member of domain users. You can’t make local users here since we’re on a domain controller.
  • Make sure to add this user to print operators as these guys can log on locally, we’ll need this on a server.
  • Log off , log back in with the new normal user
  • start pwrtech.exe /lockedout, fill in the sql credentials to the db and Voilá! Access should be restored.

Step 4 – Prep the sandbox database

Now we have managed to pry open the sandboxed WM database, it’s a matter of getting it prepped, so we can put it back into action in the new target environment. First, let’s take a look at what the administrative roles looks like as of now:

As you can see above, it’s quite obviously pointing to the old LAB domain and the domain admins group, the latter is why logging in with any domain administrator didn’t make any change. Our normal non-admin user has been added as expected as a result of the /lockedout procedure. Next we’re going to add Local Computer as a directory service:

For good measure we’re going to bump the local computers directory service up to first priority:

Note: There’s no point in adding the target domain just yet as it probably can’t be resolved from the sandbox anyway.

  • Once the localcomputer directory service is added, hit Shift+F5 or go to the File|Reload now menu in the console, for the new directory service to be made available elsewhere.
  • Go back to the Technical manager admin role and edit it.
  • Hit Add, Users/Groups and follow the yellowbrick road below as numbered below

  1. Change the dropdown to local computer
  2. Uncheck “Limit to this computer only (NAME)”
  3. Hit search
  4. Pick the normal user you created
  5. Hit ok

If you didn’t dun goof’d, it should look like this:

Step 5 – Make a new backup

Now we’re done playing doctor on the database in the sandbox, let’s back it up and put it over in the target environment. Fire up your SQL Studio, rightclick the RES db and chose backup:

Unless you intentionally aim at overwriting the old backup, be sure to remove it and replace it with a new target backup file:

When the backup is done, go to the backup location C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup You may be prompted by this lil’ fella on the right. But not to worry just hit Continue, enter your admin credentials if prompted and everything will be fine.

I noted that for any number of inexplicable reasons the size of the datastore jumped 3-fold. Hopefully this isn’t linear for larger databases!

Step 6 – Restoring the doctored WM database

It’s now time to take the backup of the doctored WM datastore out of the sandbox and put it into the target environment. Once the SQL backup file is copied over to the target SQL box, you’ll probably want to reference the destination database name to the old busted one, so any consoles pointing to it do not have to be reconfigured.

When you delete (make sure it’s the right DB!) put a checkbox in the Close existing connections checkbox.

Now let’s restore the doctored database. Be sure your restore settings look something like this:

Once the database has been restored, make sure you create/edit an SQL user which has DBO on the datastore. In my case, I already had a RESWM user, I just needed to configure the user mappings so the ownership and permission are okay:

If the above fails, it may be due to that an orphaned SQL user without login with the same name already exists in the RESWM datastore, go to security under the WM database and knock it out:

Step 7 – Set up a local user in target environment

We’re almost there now. Go to a computer (in the target domain environment) where you have the RES WM console available, but don’t launch it just yet. Remember we have to add that local user we configured within the WM console in the sandbox previously. If you’re on a domain controller, just create a domain user like you did before, however I’m going to presume you are doing this on a member server: If that’s the case go to Server Manager | Tools menu | Computer Management | System tools | Local users and Groups | Users | rightclick, New user:

Add the ‘normal’ user. Password doesn’t matter. For good measure, add it to the Print operators group again, not that it may be completely necessary but hey, the account is temporary anyway:

Now, hold down shift and rightclick on the Workspace Manager desktop shortcut, and chose Run as a different user on the context menu, entering the name of the computer you are on with credentials of the normal user:

Step 8 – Configure console in the target domain

The context should now launch. Go into User Context, Directory Service and add the current domain:

Note: You won’t be able to neither browse nor test the new domain, as you’re not running the console with a domain user at the moment.

Go back into the Techadm role and delete any references to the LAB domain. We can’t add the target domain just yet as we still are running the console with a local user. Make sure you don’t delete .\normal just yet.

Now, exit the console completely and run the “C:\Program Files (x86)\RES Software\Workspace Manager\pwrtech.exe” /lockedout command. Once again the console executable will prompt you for the SQL credentials, but now since we’ve added the Targetdomain as a directory service and we’ve removed all references to the old LAB doman, we will have success:

Step 9 – Cleanup

We now have full acces again to the databse There’s only a few housekeeping items left to do:

  1. Add any additional necessary users and groups to the TechAdm role
  2. Delete the .\normal user from the TechAdm role
  3. Delete the LocalComputer directory service from User Context
  4. Delete the ‘normal’ local user account
  5. Power down/Save/Revert/Kill the sandbox DC+SQL as you don’t need it anymore.

Conclusion: They said it couldn’t be done and the only way was to restore the buildingblocks that weren’t there. But as you may know, the RESguru never takes no for an answer, neither from a piece of stubborn software, nor incidentally from short managers with girly names! Be that as it may, this was by far the longest workaround I’ve had to come up with in a very long time: I can only wish that RES fixes this snafu so hopefully no one else have to jump through the same hoops as I did to get my WM database back.


Setting up a WM console on a jumpbox

By Max Ranzau


From the Multiple Hoops dept. The other day I was tasked with setting up a Workspace Manager console on a jumpbox. You know, the typical setup for a client where you VPN into a non-domainmember computer, from where you RDS to the different servers you need to access. The wish is to have the RES WM console running on this box so you don’t have to do Inception-RDS to make a few changes in WM, thus preserving screen real estate. Note: this will of course only work if your jumpbox is allowed to hit the database directly  If the jumpbox is firewalled to the hilt and only allows outbound RDS connections, stop reading right here.

Presuming you’re still with us, you might already have installed the WM console on your jumpbox and connected it to the relay server. When you launch it, you’ll get kicked right back out as the console looks for your local computername\username in the datastore and obviously it’s not there yet, so let’s add it:

The above sounds simple enough, but it appears there’s a few steps to go through, which incidentally left me wondering if there was an easier way to do it. I mean, under applications you can add users manually, but no such luck on Admin roles… (hint hint, nudge nudge dear product management ;)

  1. Assuming you already have WM running on one or more domain-enabled computers, go to one of these. Presuming it’s a Server 2012[R2], launch the Server Manager, goto the Tools menu and Computer Management.
  2. Go to System Tools | Local Users | Users and add a local user. The User name and password must be the same as for the jumpbox local user. This account is temporary and can be nuked at the end of the story.
  3. Now launch the WM console and go to User Context | Directory Services and chose New from the toolbar
  4. In the dialog, chose Local Computer from the Type dropdown and hit Ok. No further changes are necessary. WM now understands that local computer accounts can be used for access control, which also applies to Administrative Roles.
  5. Go to Administration | Administrative Roles | <your security role> | Access Control tab | Add button | Users/group
  6. From the directory services dropdown, chose local computer from the Directory Service dropdown, then search and select your username, which you added in step 2. Be sure the “Limit to this computer only (COMPUTERNAME)”-checkbox is NOT checked.
  7. If you did the above right, your account will be listed as .\username when you return to the previous dialog
  8. Now it’s time to return to your jumpbox and launch the WM console there. Since your username is now in the WM database it will let you. In practice you could stop here, however this would leave the jumpbox username able to launch the WM console from every computer. Let’s just add an ounce more of prevention by locking in the computername too:
  9. On the jumpbox’s WM console, go to Administration | Administrative Roles | <your security role> | Access Control tab
  10. Select your “.\username” and edit it. Repeat step 6, except make sure this to check the “Limit to this computer only (COMPUTERNAME)”-checkbox. When you return to the previous dialog, you’ll note that your account is listed correctly as your jumpboxcomputername\username
  11. As the last loose end to tie up, go back to the domain member computer where you created the temporary local user account and delete it.