Restoring Your SQL Server Databases for MDM

  • Article

2/9/2009

To restore the databases for MDM, follow these steps:

  1. On the new SQL Server computer, start Microsoft SQL Server Management Studio and connect to the local SQL Server.
  2. In SQL Server Management Studio, expand the local server, right-click Databases, and then select Restore Database.
  3. In the Restore Database dialog box, in the Destination for restore section, in the To database box, type the exact name of the database that you want to restore; for example AdminServices.
  4. In the Source for restore section, select From device, and then select the ellipsis button (…).
  5. In the Specify Backup dialog box, select Add.
  6. In the Locate Backup File dialog box, navigate to the backup destination folder that you noted in step 4 of the "Backing Up Your SQL Server Databases for MDM" section above, and then select OK.
  7. In the Specify Backup dialog box, select OK.
  8. In the Restore Database dialog box, in the Select the backup sets to restore section, select the check box next to AdminServices.
  9. In the Select a page section in the upper left, select Options.
  10. In the Restore the database files as section, select the ellipsis button (…) next to AdminServices to update the path of the .mdf file to the location on the new SQL Server computer.
  11. In the Locate Database Files dialog box, navigate to <drive:>/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data, select AdminServices.mdf, and then select OK.
  12. Repeat steps 10 and 11 for AdminServices_log to update the path of the AdminServices_log.ldf file.
  13. In the Restore Database dialog box, select OK.
  14. Repeat steps 2 through 13 for the remaining MDM databases.

Updating the Database Accounts

To update the database security login accounts, follow these steps:

  1. In SQL Server Management Studio, expand Databases, expand AdminServices, expand Security, and then expand Users.
  2. Delete the following accounts if they exist:
    • SCMDMDeviceManagementServers (<instance name>)
    • SCMDMEnrollmentServers (<instance name>)
    • SCMDMServerAdmins (<instance name>)
    • NT AUTHORITY\ANONYMOUS LOGON
    • NT AUTHORITY\NETWORK SERVICE
  3. Repeat steps 1 and 2 for the remaining MDM databases.
  4. In SQL Server Management Studio, expand the local server, expand Security, right-click Logins, and then select New Login.
  5. In the Login - New dialog box, select Search.
  6. In the Select User or Group dialog box, select Object Types.
  7. In the Object Types dialog box, select the Groups check box, and then select OK.
  8. In the Select User or Group dialog box, in the From this location box, make sure that Entire Directory is selected. Otherwise, select Locations to specify the entire directory.
  9. In the Enter the object name to select box, type SCMDMDeviceManagementServers (<instance name>), and then select OK.
  10. In the Login - New dialog box, in the Select a page section in the upper left, select User Mapping.
  11. In the Users mapped to this login section, select the check box next to AdminServices.
  12. If SQL Server is running on the same local computer as MDM, then skip this step. Or else, in the Database role membership for AdminServices section, check the following boxes:
    • ServiceAdmin
    • ServiceDriver
    • VPNAdmin
    • VPNPowerUser
  13. In the Users mapped to this login section, check the box next to TEEDB.
  14. If SQL Server is running on the same local computer as MDM, then skip this step. Or else, in the Database role membership for TEEDB section, check the following boxes:
    • PublicAPI
    • TEE
  15. In the Login - New dialog box, select OK.
  16. Repeat steps 4 through 11 to add the SCMDMEnrollmentServers (<instance name>) security login.
  17. If SQL Server is running on the same local computer as MDM, then skip this step. Or else, in the Database role membership for AdminServices section, check the following boxes:
    • ServiceAdmin
    • ServiceDriver
  18. In the Users mapped to this login section, select the check box next to MobileEnrollment.
  19. If SQL Server is running on the same local computer as MDM, then skip this step. Or else, in the Database role membership for MobileEnrollment section, check the following box:
    • EnrollmentServer
  20. Repeat steps 4 through 11 to add the SCMDMServerAdmins (<instance name>) security login.
  21. If SQL Server is running on the same local computer as MDM, then skip this step. Or else, in the Database role membership for AdminServices section, check the following box:
    • ServiceAdmin
  22. Repeat steps 4 through 11 to add the NT AUTHORITY\ANONYMOUS LOGON security login.
  23. In the Users mapped to this login section, select the check box next to MobileEnrollment.
  24. If SQL Server is running on the same local computer as MDM, then skip this step. Or else, in the Database role membership for MobileEnrollment section, select the following check box:
    • EnrollmentWebService
  25. If SQL Server is running on a separate computer from MDM, then skip steps 25 through 27. Or else, repeat steps 4 through 11 to add the NT AUTHORITY\NETWORK SERVICE security login.
  26. In the Users mapped to this login section, check the box next to AdminServices.
  27. In the Database role membership for AdminServices section, select the following check boxes:
    • ServiceAdmin
    • ServiceDriver
    • VPNAdmin
    • VPNPowerUser
  28. In the Users mapped to this login section, select the check box next to MobileEnrollment.
  29. In the Database role membership for MobileEnrollment section, select the following check box:
    • EnrollmentServer
  30. In the Users mapped to this login section, select the check box next to TEEDB.
  31. In the Database role membership for TEEDB section, select the following check boxes:
    • PublicAPI
    • TEE
  32. If SQL Server is running on a separate computer from MDM, then skip steps 32 through 34. Or else, repeat steps 4 through 11 to add the NT AUTHORITY\LOCAL SERVICE security login.
  33. In the Users mapped to this login section, select the check box next to MobileEnrollment.
  34. In the Database role membership for MobileEnrollment section, select the following check box:
    • EnrollmentWebService
  35. For the SUSDB database, repeat steps 4 through 10 to add a security login account for each MDM Device Management Server. For example, <server name>$ (<instance name>).
  36. In the Users mapped to this login section, select the check box next to SUSDB.
  37. In the Database role membership for SUSDB section, select the following check box:
    • Public

Updating the Database Owners

To update the database owners, follow these steps:

  1. In SQL Server Management Studio, click the New Query button in the upper left.

  2. In the query window on the right, type the following:

    USE AdminServices
Go
sp_changedbowner ‘DomainName\Administrator’

  3. In SQL Server Management Studio, click the Execute button to run the query.

  4. Repeat steps 1 through 3 to run the following query:

    USE MobileEnrollment
Go
sp_changedbowner ‘DomainName\Administrator’

  5. Repeat steps 1 through 3 to run the following query:

    USE TEEDB
Go
sp_changedbowner ‘DomainName\Administrator’

  6. Repeat steps 1 through 3 to run the following query:

    RESTORE SERVICE MASTER KEY FROM FILE = 'c:\filename.bak'  
DECRYPTION BY PASSWORD = 'password!1' 
FORCE

    Where filename.bak is the file that you copied from the old SQL Server computer.

  7. Repeat steps 1 through 3 to run the two *.sql files that you copied from the old SQL Server computer.

  8. Close SQL Server Management Studio.

  9. Using the Services.msc console, restart the SQL Server and SQL Server Agent services.

Unexpected Error Writing to Database

If you are restoring MDM databases, you may receive either of the following event messages:

  • Event 6201: The configuration loader for the AdminDataAccess component was unable to access the database. Configuration items for this component may be out of date.
  • Event 6104: The AdminDataAccess component has failed to connect to the admin services database. The component will retry the connection.

To resolve this issue, change the database owner to a valid login or domain user. For more information about this issue, please visit the following Microsoft Web site: https://go.microsoft.com/fwlink/?LinkId=132125.

Updating the Database SCP Keywords

  1. To update the database service connection point (SCP) keywords, follow these steps:
  2. Open ADSIEdit.msc.
  3. In ADSI Edit, expand Domain, expand DC=<domain name>,DC=microsoft,DC=com, expand CN=System, expand CN=SCMDM.
  4. Right-click CN=<instance name>, and then select Properties.
  5. In the CN=<instance name> Properties dialog box, on the Attribute Editor tab, in the Attributes box, scroll down to select keywords, and then select Edit.
  6. In the Multi-valued String Editor dialog box, in the Values box, select database=<server name>, and then select Remove.
  7. In the Value to add box, update the old SQL Server computer name to the new SQL Server computer name, and then select Add.
  8. In the Multi-valued String Editor dialog box, in the Values box, select sqlinstance=, and then select Remove.
  9. In the Value to add box, update the old SQL Server instance name to the new SQL Server instance name, and then select Add.
  10. In the Multi-valued String Editor dialog box, select OK.
  11. In the CN=<instance name> Properties dialog box, select Apply, and then select OK.

Restart the SCMDM Services

Using the Services.msc console, restart all of the MDM services:

  • SCMDM ADGP service
  • SCMDM Enrollment service
  • SCMDM GCM service
  • SCMDM Password Recovery service
  • SCMDM Software Distribution service
  • SCMDM Wipe service