The MSDN page for OPEN MASTER KEY states: 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 either use OPEN MASTER KEY statement using one of the password 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. 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. The MSDN page for CREATE MASTER KEY states (emphasis added):įor SQL Server and Parallel Data Warehouse, the Master Key is typically protected by the Service Master Key and at least one password. If you have a Certificate that is guaranteed to exist in the Database being restored, try using it: SELECT SIGNBYCERT( CERT_ID( '' If the current SMK is not the correct SMK, then the DMK won't be automatically decrypted and the operation will fail. Assuming that you have not opened the DMK explicitly (using the password supplied when creating it), decrypting the DMK will require the SMK. Such an operation would need to first decrypt the DMK in order to use it. 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.
0 Comments
Leave a Reply. |