From 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
- Change the dropdown to local computer
- Uncheck “Limit to this computer only (NAME)”
- Hit search
- Pick the normal user you created
- 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:
- Add any additional necessary users and groups to the TechAdm role
- Delete the .\normal user from the TechAdm role
- Delete the LocalComputer directory service from User Context
- Delete the ‘normal’ local user account
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.