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

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

dhuvachai — Wed, 05 Dec 2007 21:50:07 GMT

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

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

lcris — Wed, 05 Dec 2007 22:54:01 GMT

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.

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

lcris — Wed, 05 Dec 2007 23:06:51 GMT

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.

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

dhuvachai — Fri, 14 Dec 2007 00:40:24 GMT

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

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

lcris — Fri, 21 Dec 2007 01:06:25 GMT

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

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

dhuvachai — Fri, 28 Dec 2007 20:03:02 GMT

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.

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

lcris — Fri, 28 Dec 2007 22:00:25 GMT

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.

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

njahncke — Thu, 10 Jan 2008 04:04:50 GMT

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!

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

lcris — Thu, 10 Jan 2008 05:46:42 GMT

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.

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

njahncke — Thu, 10 Jan 2008 21:28:11 GMT

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!