SQL Database Migration

If you need to move your Keyfactor Command database from one SQL server to another, the process is similar to a controlled disaster recovery. You will need a backup of your Keyfactor Command database and the ability to decrypt the encrypted content within the database (see SQL Encryption Key Backup). By default, a new SQL server will have a different service master key (SMK) than your original SQL server. To support the migration, you have a few options:

• Set the SMK on the new server to match that of the old server and do a simple restore of the database. This may not be a feasible solution if there are any other applications on the new server that use SQL encryption.
• Temporarily add a known password to the database master key (DMK) on the Keyfactor Command database (if one is not known already).

To transfer a Keyfactor Command database between two SQL servers that do not share the same SMK, as a user with control permission on the Keyfactor Command database:

1. Add a known password to the DMK by issuing the following SQL command in the Keyfactor Command database. You can specify any password you want that meets the Windows password complexity rules.
   ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'SecurePassword#1234'
   Important:  Note that at this point, in addition to the backup you are about to manually make, any automated backups of the Keyfactor Command database will contain this DMK password and anyone with access to the backup media and the password would be able to decrypt the sensitive information within the Keyfactor Command database.
2. Use your preferred SQL server tools to back up the database, copy the backup media to the target server, and restore the database on the target server.

3. Use the following SQL commands on the target server to manually open the DMK, protect the DMK with the target server’s SMK, and remove the DMK password (referencing the password you used on your DMK):

   OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SecurePassword#1234'
   ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
   ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'SecurePassword#1234'

4. Open a new query window on the target server and use the following SQL to validate that the DMK is properly encrypted by the SMK and that the Keyfactor Command application will be able to ask SQL server to decrypt information in the database. The commands should run without error.

   OPEN SYMMETRIC KEY [CMS_SecretsSymmetricKey] DECRYPTION BY CERTIFICATE [CMS_SecretsCertificate];
   CLOSE SYMMETRIC KEY [CMS_SecretsSymmetricKey]

5. On the source server, if you are not going to remove the Keyfactor Command database, issue the following SQL command to remove the DMK that was added (referencing the password you used on your DMK):

   ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'SecurePassword#1234'
6. Delete the backup or securely store the backup media that was used, along with the temporary DMK password, as it can be used to obtain the encrypted Keyfactor Command information.