Note
This information was copied from http://social.technet.microsoft.com/wiki/contents/articles/1084.aspx and then edited to apply for AD RMS in Windows Server 2012.

When an organization chooses to migrate its Active Directory Rights Management Services (AD RMS) databases from Windows Internal Database (WID) on the AD RMS server to a separate SQL server, the migration process can be complex and challenging. This article steps you through the process of migrating these databases from WID in Windows Server 2012 to SQL Server 2012.  It describes the actions that you need to take to prepare for the migration, the steps of the migration itself, and help for troubleshooting SQL connection problems.

This article presents a high-level description of the tasks required to perform the migration. It is assumed that you are familiar with the various tools and methods required to administer computers running SQL Server and AD RMS.

Preparing for the migration

Before you begin the actual migration, you should first prepare your network infrastructure by performing the following tasks:

• Prepare the server that will receive the AD RMS databases by installing the Microsoft Windows Server 2012 operating system on a Windows server computer and joining it to the same domain as the AD RMS server.
• Make sure that these servers are assigned static IP addresses.
• If necessary, set a firewall exception for TCP ports 445 (for SQL Server Named Pipes) and 1433 (or the TCP port that the SQL Servers are configured to listen on) on each new SQL Server cluster. For more information about firewall settings for AD RMS and SQL Server, see AD RMS Firewall Considerations in the Microsoft TechNet Library.
• Ensure that you can access all computers and that you have administrator privileges on them.
• Create a login for the AD RMS service account on the new SQL Server.
• Plan which SQL Server backup jobs and integrity checks will be enabled on the new SQL Server after the migration is complete.
• Make sure that you have prepared a writable destination for databases that you will copy and that the destination can be accessed from the new SQL Server.
• Download the Microsoft SQL Server 2012 Native Client and Microsoft SQL Server 2012 Command Line Utilities from http://www.microsoft.com/download/en/details.aspx?id=29065 and install them on the AD RMS server.

Although not required to perform the migration, the following are best practices that you should consider implementing as part of the migration:

• Ensure that a CNAME record is registered in DNS for the AD RMS cluster and that it works for the new SQL Server computer.
• Ensure that a CNAME record is registered in DNS for the new SQL Server computer and that it works for the AD RMS server.
  In SQL Server 2008 and later, it might be necessary to disable strict name checking by setting the following registry value to 1 and then resetting SQL Server:
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters
  DWORD: DisableStrictNameChecking

Performing the migration

Perform the following procedures in the indicated order to complete the migration.

Prepare the AD RMS server

Log on to the AD RMS server and perform all of these steps.

1. To provide for failsafe recovery in case of a problem with the migration, export the Trusted Publishing Domain (TPD) to a safe location:
   1. Open the Active Directory Rights Management Services console, and then expand the AD RMS cluster.
   2. In the console tree, expand Trust Policies and then click Trusted Publishing Domains.
   3. In the results pane, select the certificate for the domain you want to export.
   4. In the Actions pane, click Export Trusted Publishing Domain.
   5. In the Publishing domain file box, type the name of the publishing domain file you are exporting or clickSave As to export it to a special location. Make sure you specify the .xml file name extension.
   6. In the Password and Confirm password boxes, type a strong password that will be used to encrypt the trusted publishing domain file. You need this password to import this file into another AD RMS cluster.
   7. Click Finish to create the trusted publishing domain file.
2. Stop IIS services:
   1. Open a command prompt with Administrator privileges.
   2. Type the following command, and then press Enter:
      iisreset /stop
3. Detach the DRMS databases:
   1. Open a command prompt with Administrator privileges.
   2. Type the following command, and then press Enter:
      cd %programfiles%\Microsoft SQL Server\110\Tools\binn
   3. Type the following command, and then press Enter:
      sqlcmd –S np:\\.\pipe\Microsoft##WID\tsql\query –E 
      The command prompt changes to 1>.
   4. Type the following commands, pressing Enter after each one:
      EXEC sp_detach_db DRMS_Config_<cluster_port>
      GO
   5. Type the following commands, pressing Enter after each one:
      EXEC sp_detach_db DRMS_DirectoryServices_<cluster_port>
      GO
   6. Type the following commands, pressing Enter after each one:
      EXEC sp_detach_db DRMS_Logging_<cluster_port>
      GO
   7. Type the following command, and then press Enter:
      Exit
   8. Type the following command, and then press Enter:
      cd %systemroot%\WID\Data
   9. Type the following command, and then press Enter:
      copy DRMS_*.?df <shared_folder>
      Where <shared_folder> is a shared folder that can be accessed from the destination SQL Server.

Migrate the databases to the destination SQL Server

Log on to the target SQL Server and perform the following steps.

1. Copy the .mdf and .ldf files from the shared directory to the SQL data directory on the SQL Server (usually %programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
2. Attach the AD RMS databases in the destination SQL Servers:
   1. Start SQL Service Management Studio with Administrator privileges.
   2. Right-click Databases and then click Attach.
   3. Under Databases to add, click Add.
   4. Click one of the .mdf files that you copied in Step 1, and then click OK.
   5. Repeat Steps 3 and 4 for the remaining .mdf files that you copied in Step 1.
3. Edit the ClusterPolicies table for the new SQL Server name (or its CNAME alias)
   1. If necessary, start SQL Service Management Studio with Administrator privileges.
   2. Expand the following nodes:
      Databases
      DRMS_Config_<cluster>_<port>
      Tables
   3. Right-click dbo.DRMS_ClusterPolicies, and then click Open Table.
   4. Find the PolicyName and the PolicyData columns in the table.
   5. Under the PolicyName column, locate LoggingDatabaseServer, and then replace the corresponding value in thePolicyData column with the SQL Server name or alias.
   6. Under the PolicyName column, locate CertificationUserKeyStorageConnectionString, and then replace the server identifier betweendata source= and the next semicolon (;)with the SQL Server name or alias.
   7. Under the PolicyName column, locate DirectoryServicesCacheDatabase, and then replace the server identifier betweendata source= and the next semicolon (;) with the SQL Server name or alias.
4. Confirm that the AD RMS service account has database role membership.
   1. If necessary, start SQL Service Management Studio with Administrator privileges.
   2. Expand the following nodes:
      Security
      Logins
   3. Open the Properties for the AD RMS service account login.
   4. Select User Mapping.
   5. Confirm that the login is mapped to the following databases with public and rms_service roles enabled:
      DRMS_Config_<cluster_port>
      DRMS_DirectoryServices_<cluster_port>
      DRMS_Logging_<cluster_port>

Configure the AD RMS server to use the new SQL Server

Log on to the AD RMS server and perform the following steps.

1. Start Registry editor (click Start, type regedit.exe, and then press Enter).
2. Expand the following nodes:
   HKEY_LOCALMACHINE
   SOFTWARE
   Microsoft
   DRMS
   
3. Right-click the DRMS registry key, select Export, and save the export to a *.reg file.
   (This is a backup in case the original registry settings need to be restored.)
4. Click ConnectionString, double-click ConfigDatabaseConnectionString and then replace the server identifier following datasource= and the next semicolon (;) with the SQL Server name or alias.
5. If the AD RMS cluster is using a centrally-managed key, the KeyProtection registry key exists and the value under this key must be edited to contain the SQL Server name.  Click the KeyProtection registry key.  Right-click the PASSWORDDERIVEDKEY value name and select Rename.  Replace the WID query name between PASSWORDDERIVEDKEY_ and _DRMS_CONFIG with the SQL Server name or alias.
      Example before change:    
       PASSWORDDERIVEDKEY_NP:\\\\.\\PIPE\\MICROSOFT##WID\\TSQL\\QUERY_DRMS_CONFIG
      Example after change:
         PASSWORDDERIVEDKEY_<SQL Server name>_DRMS_CONFIG
6. Reset IIS:
   1. Open a command prompt with Administrator privileges.
   2. Type the following command, and then press Enter:
      iisreset
7. Verify that the AD RMS server can connect to the SQL Server by opening the Active Directory Rights Management Services console. If an error message appears, see “Troubleshooting SQL Server connection errors,” at the end of this article.

Troubleshooting SQL Server connection errors

Do the following if this error message appears when you open the AD RMS console: “Cannot open database … requested by the login. The login failed. Login failed for user….”

1. Log on to the SQL Server and start SQL Server Management Studio with Administrator privileges.
2. Expand the following nodes:
   Security
   Logins
3. Double-click the AD RMS service account.
4. In the Login Properties dialog box, click User Mapping.
5. Ensure that the check boxes next to the three DRMS databases are selected.
6. If selecting one or more of these check boxes fails, delete the AD RMS service account from the list of users for each DRMS database (under Databases\<DRMS_database>\Security\Users) and then select the checkbox in the AD RMS service account user mapping table again.