SQL Server 2005: Restoring the backup of a database that uses encryption


I have addressed this topic in previous threads and comments (here, here, and here, for example), both on this blog and on various forums, but it looks like when you need the answer, it can be hard to dig out. So I’m hoping that by placing these steps in a dedicated post, they will become easier to find.


When you restore a database that uses encryption features, there is only one thing you need to take care off – if the database master key (DbMK) needs a service master key (SMK) encryption, you need to regenerate this encryption. Note that this encryption is made by default when you create the DbMK, but it may be intentionally dropped, if you want tighter control of access to the encrypted data. Anyway, if you did have such SMK encryption for the DbMK, the steps to regenerate it are the following:


OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘password’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY


That’s it – the database encryption features should now work as when the backup was taken. Also note that it doesn’t matter if you restore the database on the server where the backup was taken or elsewhere. The only thing that matters for this procedure is that you know one of the passwords protecting the DbMK (yes, there can be more than one – see this post).

Comments (12)

  1. dhuvachai says:

    What should we do in the case the we lost database master key password? Do we have a way to recover it?

  2. lcris says:

    It depends.

    If the DbMK had a SMK encryption, you can restore the database on a server that has that SMK set, and then you can add a new password encryption to the DbMK. So you can basically recover the database on a server on which you have the same SMK that was used at the time you took the database backup.

    If you don’t have a SMK encryption (if you dropped that encryption) or you don’t have access to that particular SMK anymore, you can only recover the database if you happen to have a DbMK backup protected with a password that you remember. Or alternatively, if your DbMK was encrypted by more than one password, then you can access it via one of these other passwords.

    If you have no SMK encryption or no way to recover the SMK that protects the DbMK AND you have no DbMK backup AND you only had one password protecting the DbMK AND you forgot it, then you can consider the DbMK lost together with all data depending on it.

    There are no workarounds or backdoors for bypassing encryption – if you lose a key (and the password is just another key that is supposed to be easier to remember), then you lose all data protected by it. There are applications for managing passwords and you may want to look into buying one or implementing one yourself. If you’re using encryption heavily, I would suggest to manage two extra separate databases: one for storing backups of keys and one for storing the passwords of those backups. These should be stored on different machines and they would only be needed for recovery scenarios.

    As for what to backup, you should backup all SMKs that you generate. DbMK backups are not needed if you always keep the SMK protection for each DbMK, but if you don’t, then you should also backup those DbMKs that are only protected by passwords.

    Don’t forget that you can also keep multiple password encryptions for a DbMK.

    Hope this helps.

  3. lcris says:

    Appendix: I wrote the above under the assumption that the lost DbMK password was a strong password. You always have available the approach of brute force searching the password by attempting to open the master key with passwords that you pick from a dictionary or generate otherwise – but this approach will not be feasible if the password is well picked.

  4. dhuvachai says:

    Could you please give me more information what do you mean "We can also keep multiple password encryptions for a DbMK" and command?

  5. lcris says:

    See: http://msdn2.microsoft.com/en-us/library/ms186937.aspx

    I’m referring to the use of:

    ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = ‘new_password’

    I had mentioned this previously here: http://blogs.msdn.com/lcris/archive/2005/09/23/473464.aspx

  6. dhuvachai says:

    Thank you, I got it now.

    By the way, is there any solution or support tool when 2 users put the password in query analyzer and see only asterisk. Regarding to security and control concern, when we alter DMK and put new password, our company has policy to reset password by using 2 factors of password. 1st user put the 1st half of password and 2nd user put the 2nd half of password. It does not work if both users can see the password character when they type in query analyzer.

  7. lcris says:

    I don’t know of one, but you could easily write an application to do that using one or two password edit fields and then just issuing the DDL to the database.

    However, I am not sure what that would achieve in the case of the database master key. Even if the password is initially set this way, any dbo can add a new password that he knows and use that one – the two factor password that you have set doesn’t prevent access to the DbMK by a single person.

  8. njahncke says:

    First off, thank you so much for providing the information that you do; your blogs are great to read. I hope your transition went well!

    I have a question for you regarding the use of an external database for key storage, though I hope I’m not just missing something… The DDL for making SMK, DbMK or Certificate backups seems to allow only the option of specifying a backup file. Would you mind providing a brief example of how one might backup a Service Master or Database Master Key to a separate instance?

    Thanks in advance!

  9. lcris says:

    There is currently no support for external key storage (such that you can use a key without having it persisted in the current database) and you cannot make a backup of a key directly into a different instance. When I wrote the comment about storing key backups on a different machine, I was just having custom application code in mind.

    We have had some requests for allowing backups of keys to be output to a varbinary variable rather than to a file, which would allow for easier programmability by avoiding going to the filesystem. If you would find such feature useful, I encourage you to open a request on the SQL Server customer feedback site – customer feedback plays a big role in deciding whether to add a small feature like this or not.

    Also, in the next version of SQL Server, there will be a new feature allowing the storage of keys on external devices manufactured by third party vendors. The feature is called EKM for Extensible Key Management. See this article for some information about it: “http://msdn2.microsoft.com/en-us/library/bb510411(sql.100).aspx“. This feature was targeted to address the management of cryptographic keys in enterprise scenarios.

  10. njahncke says:

    Indeed, I think varbinary key output could be quite useful, but I think EKM would be closer to what I’m after.

    Thanks once again for the information!

  11. Andrew says:

    The sample given is not correct (at least not if you are using certificate decryption to encrypt data).  There is a line missing in the example:  Should read:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

    ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

    CLOSE MASTER KEY

    I have no idea why the drop word is required but you won't be able to decrypt your data unless you include that line…

  12. lcris says:

    Dropping the SMK encryption is not required – the ADD command will replace the SMK encryption directly. What version of SQL Server are you using and what kind of errors are you getting?