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?
SQL Server – How to Check Database Master Key Encryption Validity?
encryptionencryption-keysql server
Related Question
- Sql-server – Stored procedure encryption and master service master key, SQL Server 2008
- SQL Server Encryption – Recovering Lost DMK Password for Master Database
- Sql-server – How to backup/restore database encryption key in order to restore on new server
- Sql-server – Restore Database w Master Key and storing password in plain text
- SQL Server – Restore Database with Encrypted Column Without Overwriting Service Master Key
- SQL Server – Are Database Master Keys Protected with the Same Password Identical?
- SQL Server – When is Service Master Key Generated?
Best Answer
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:
That should return a non-
NULL
VARBINARY value. If the return value isNULL
, 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:
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:
The MSDN page for
CREATE MASTER KEY
states (emphasis added):The MSDN page for
OPEN MASTER KEY
states:The MSDN page for ALTER MASTER KEY states: