I have a scenario where I'm restoring a db from one server to another. On the source server, the database master key (DMK) is encrypted with both a password and the service master key (SMK). When I go to restore it to the new server, the row in sys.key_encryptions still says that it's encrypted by the SMK. This isn't true though since the SMKs don't match between the two servers. Is there any programmatic way to verify that the DMK is indeed encrypted with this server's SMK?

up vote 5 down vote accepted

In order to programmatically determine if the current SMK was used to protect the DMK, you should be able to simply attempt an operation that would require the DMK. Such an operation would need to first decrypt the DMK in order to use it. Assuming that you have not opened the DMK explicitly (using the password supplied when creating it), decrypting the DMK will require the SMK. If the current SMK is not the correct SMK, then the DMK won't be automatically decrypted and the operation will fail. So:

  • If you have a Certificate that is guaranteed to exist in the Database being restored, try using it:

    SELECT SIGNBYCERT( CERT_ID( '{certificate_name}' ), 'test' );
    

    That should return a non-NULL VARBINARY value. If the return value is NULL, then the DMK needs to be regenerated (per the instructions below).

  • If no Certificate is guaranteed to exist in the Database being restored, then try to create one. If the SMK can be used to automatically decrypt the DMK, then the Certificate will be created, else the operation will fail:

    BEGIN TRY
      CREATE CERTIFICATE [TestCert] WITH SUBJECT = 'yadda yadda yadda';
      DROP CERTIFICATE [TestCert];
      PRINT 'All good, yo!';
    END TRY
    BEGIN CATCH
      PRINT ERROR_MESSAGE();
    END CATCH;
    

However, since you just restored a database coming from another instance and did not restore that other instance's SMK into the new instance, it is safe to assume that the answer is: "no, the DMK is not encrypted with this server's SMK."

This is an expected scenario that requires the following steps to remedy:

USE [newly_restored_db];
OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
CLOSE MASTER KEY;

The MSDN page for CREATE MASTER KEY states (emphasis added):

For SQL Server and Parallel Data Warehouse, the Master Key is typically protected by the Service Master Key and at least one password. In case of the database being physically moved to a different server (log shipping, restoring backup, etc.), the database will contain a copy of the master Key encrypted by the original server Service Master Key (unless this encryption was explicitly removed using ALTER MASTER KEY DDL), and a copy of it encrypted by each password specified during either CREATE MASTER KEY or subsequent ALTER MASTER KEY DDL operations. In order to recover the Master Key, and all the data encrypted using the Master Key as the root in the key hierarchy after the database has been moved, the user will have [to] either use [the] OPEN MASTER KEY statement using one of the password[s] used to protect the Master Key, restore a backup of the Master Key, or restore a backup of the original Service Master Key on the new server.

The MSDN page for OPEN MASTER KEY states:

When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

The MSDN page for ALTER MASTER KEY states:

The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.

  • You're right that post-restore it's unlikely to be recruited with the right SMK. What I'm trying to do is provide my team with a verification script that will tell them if they missed a step in the documented process. – Ben Thul Jan 6 '17 at 3:26
  • @BenThul Answer has been updated with 2 options to try :-) – Solomon Rutzky Jan 6 '17 at 3:47
  • 1
    There is a pending edit from a low-rep user on your answer with the following note: "The key to the "remedy" is not ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}', but instead ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY" I don't know whether that is correct or not, but figured I'd mention it. – BradC Jun 13 '17 at 18:37
  • 3
    I think it would be fair for you to roll back your answer to your verified revision. Once you've got time to verify the update, you can roll back to the newer revision just as well. – Andriy M Jun 13 '17 at 21:10
  • 1
    @AndriyM Thanks for that input. Perhaps that is what I should do, and that is fine. I hadn't thought that I could revert the revert, so it seemed messy / wasteful to revert something that might be correct. FWIW, I just posted Undo accepted edit back to pending, not full revert in meta to solicit opinions, so please add your suggestion above as an answer there :-). – Solomon Rutzky Jun 13 '17 at 21:20

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.