Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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 involve din 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.

share|improve this answer
    
Thanx a lot Remus! –  PanosPlat Mar 17 '13 at 20:56

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.