Are you safe without your Keys?

I am not talking about your house keys, or your car keys, I am talking about your SQL Server Keys.  SQL Server 2005 allows you to use keys for protecting objects and data in the server and databases.  It is very likely that if you are doing encryption in SQL Server that you already know that you have keys in SQL, but if you have never touched encryption, you might now know that, you too, have at least one key in SQL, the Service Master Key.  For those not familiar with this, the Service Master Key is the root of encryption key hierarchy in SQL Server 2005, it is by default used to encrypt Database Master Keys, linked server security information, and any credentials created using CREATE CREDENTIAL.  If you don't use any of the above, then read no further, you are probably fine.

Personally, I learned about keys, certificates, and encryption while studying for my MCITP Database Administrator exams at the beginning of this year, and that was enough to add a backup of the SMK into a password protected file to my installation procedures for each SQL Server Instance I have.  I personally have never needed to use the backup to date.  So what sparked this blog posting?  As usual, a post on the MSDN Forums, where a backup of a database using encryption was restored to a different server, and the poster didn't know the password to the database master key.  After goofing the initial answer on it, I had to do some investigating into what could be done in the case that the password to encrypt a DbMK is lost, and the answer isn't all that complex, and it is included in the post referenced above. 

However, it is much easier to just avoid the situation completely and have available backups of the SMK, as well as all the other keys in use in your database server. These can easily be done with DDL commands:

BACKUP SERVICE MASTER KEY (Transact-SQL)
RESTORE SERVICE MASTER KEY (Transact-SQL)

BACKUP MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)

One of the best references online is Laurentiu Cristophors blog on MSDN for security and cryptography in SQL Server.  Laurentiu is a frequent answerer in the SQL Security forum on MSDN, and has wealth of information available about this subject.  His blog is definitely a recommended read and is on my personal blog roll.

Since this is such a vast subject, I plan on putting some time into it in the near future, and writing some additional information on the subject.  However, in the mean time, backup those keys and certificates.

EDIT:  One thing I realized while thinking about his blog is I do have two different Certificates implemented in my servers, one for mirroring between two servers not on a domain, and one for a certificate user to provide permissions for a dynamic SQL search procedure that uses parameterized dynamic SQL to perform optimized searching in one of our databases, since application users are required to use stored procedures and have no table level access.  The certificate user has no associated login and is used WITH EXECUTE AS to provide the needed table level SELECT rights to run dynamic SQL code in the stored procedure.  The calling user only has execute rights on the stored procedure but can still make use of its dynamic code due to this type of impersonation.  I do have backups of both of these certificates, as created when they were created.

If you want more information about using Certificates to grant elevated rights with a Certificate User look at Erland Sommarskogs article Giving Permissions through Stored Procedures.