How to Copy Production SCSM Data to a Lab Environment in Different Domains

Author by Christopher Mank

This past week I was working with a client to help setup a DEV and QA SCSM environment for their testing and validation.  To ensure their DEV environment was configured exactly like PROD, we wanted to copy the PROD database from PROD to DEV.  Since Microsoft has the instructions posted on TechNet (found here), I figured this would be a pretty straightforward task, right? Well...yes and no.  The instructions are correct in getting the PROD database moved over to DEV, but what if your DEV environment is in a totally different, untrusted domain than PROD? If this is the case for you, what you'll find is that when the database has been moved over to DEV, since all the user accounts are from the PROD domain, there doesn't exist a user in the database that allows you access to login to the console!  So now what?  Below you'll find step-by-step instructions on how you can modify User Role access right from within the CMDB directly. NOTE:  This is provided "AS-IS" with no warranties at all. 1.  The TechNet article describes how to backup and restore the PROD database into your DEV environment.  So at this point, the assumption is you have the PROD ServiceManager database in DEV and you have full permissions to the database itself.  These actions are being performed against the database in DEV, not PROD. 2.  Open up SQL Server Management Studio (SSMS) and connect to the server that is hosting the ServiceManager database. 3.  Find the ServiceManager database and under the Tables section, find the "dbo.AzMan_Role_SIDMember" table.  Right-click on the table and choose "Select Top 1000 Rows".  You should get an output similar to the screenshot below. AzMan_Role_SIDMember Table Image Let's take a minute to understand what this table is showing us.  We'll start with the second column (MemberSID).  This column stores the AD SID of the User or Group that has some sort of role assignment set in SCSM.  The SID is stored in hexadecimal format.  The first column (RoleID) indicates what type of role the User or Group is assigned to.  This ID joins back to the "dbo.AzMan_AzRoleAssignment" table.  If you select from that table you will see something similar to this: AzMan_AzRoleAssignment Table Image So if you look at the yellow highlighted record, you will see that ID 2 is the Administrators role.  If we look back at the first table, the record highlighted in green, we'll see that User or Group 0x01050000000000051500000020433C2971BE009D6D9DBE853A400000 has a RoleID of 2, meaning they are assigned to the Administrators group in SCSM.  Still with me? 4.  So what we need to do is get the SID of a User/Group in the DEV domain, in hexadecimal format, and add a record for that user to the AzMan_Role_SIDMember table.  To do that, we first need to know the SID of the User/Group that we wish to grant Admin access to in DEV SCSM.  To do this, we can run the following PowerShell command to get the SID of our User or Group (highlighted in yellow below). Get-ADUser Image 5.  Once we have the SID, we need to convert it to hexadecimal.  For that, we can also run a PowerShell script that will do the conversion for us.  Simply enter the SID into the $sidstring variable (highlighted in green) and it will output the hexadecimal equivalent (highlighted in yellow). ConverSidToHex Image 6.  Jump back to SSMS.  Now all we need to do is insert a record into the AzMan_Role_SIDMember table by using the following SQL command.  You will notice the value highlighted in yellow is the same output from the previous PowerShell command with "0x" in front of it. Insert Into AzMan_Role_SIDMember Image And that's it!  You show now be able to access the console without issues. Until the Whole World Hears, Christopher
Author

Christopher Mank

Systems Architect