SQL Server 2005 encrypted database works on new PC without restore keys!

Question

Something tells me that it was not normal.

Let me take it from the begining. In an SQL Server Express 2005 I needed to encrypt one column. I did the following.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='blahblah'; 

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah'
WITH SUBJECT = 'Password',
EXPIRY_DATE = '12/31/2099';

CREATE SYMMETRIC KEY my_symmetric_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE my_certificate;

I created procedures to encrypt, decrypt the column and everything worked OK.

I then took the database (SQL Server 2005, Mixed Mode) and restored it to a new PC with an SQL Server 2008 Express.

WITHOUT restoring KEY and Certificate, I launched my application which use the stored procedures to encrypt/decrypt and SURPRISINGLY everything worked in the new database!! I could decrypt previously encrypted data successfully.

Is this normal??

What is the use of backing-up the Key and the Certificates then?

Thanx in advance

Answer 1

Yes, it is normal. The certificates and keys are part of you database.

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah' ...;

CREATE SYMMETRIC KEY my_symmetric_key
... ENCRYPTION BY CERTIFICATE my_certificate;

Your posted code creates a symmetric key encrypted with a certificate encrypted with a password. The master key is not involved in any way. This arrangement requires you to provide the certificate decryption password in the session. Your application must provide it, or your stored procedures do.

Answer 2

You may be thinking of Transparent Database Encryption, in which the certificate lives in the master database.

In your example, you did restore the key and certificate because they are both stored in your database.