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.

This is a bit of the opposite of how this question is usually asked-- I used the following to create a database master key, a certificate, and a symmetric key:

Here's how I created the certs/keys (obviously with real passwords):

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '123456'

CREATE CERTIFICATE EncryptionCert
WITH SUBJECT = 'EncryptionCert'

CREATE SYMMETRIC KEY SymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptionCert;

I then encrypted some data using that key, backed up the database, and restored it to a different box.

--
-- Add a new varbinary column to the customers table to hold the encrypted
-- version of the password
--
ALTER TABLE customers2
ADD password2 VARBINARY(256)

OPEN SYMMETRIC KEY SymmetricKey DECRYPTION
BY CERTIFICATE EncryptionCert


--
-- Save the encrypted version of the password to the table
--
UPDATE customers2
SET password2 = ENCRYPTBYKEY(KEY_GUID('SymmetricKey'),password)

My expectation was that I wouldn't be able decrypt the data until I first migrated the service master key from the old box, but I actually was able to do that immediately after restoring without providing a single cert or password.

I deduced that this was happening because I was using an Amazon EC2 image to create the box, so I'm assuming that EVERY box created from that image had the same service master key.

To try to force the service key to change, I ran: alter service master key regenerate

on both boxes.

I started over with a new database, new keys/certs etc, and this time, when I moved the backup to the new box, I wasn't able to automatically read the data, but I had to first provide the password for the database master key that I created. Once I did that, I was able to get to the data.

From everything I read, I would have thought that I wouldn't be able to decrypt the master key without moving the service master key first.

I'm concerned that the service master key not being required is still due to an oddity of my test environment, and that I might get screwed in a year when I try to do this for real.

Can anyone shed light on what's happening here? Did something change in SQL that made it unnecessary to move the service master key, or did I create the database master key in a way that made moving it unnecessary? Or am I getting back potentially false results?

share|improve this question
    
This seems isolated to EC2... but thinking about it, that's a pretty big security flaw. If you had access to someone else's database backup from EC2, you'd be able to decrypt the data in it, unless they had randomized the service master key. Interesting. –  Jon Seigel Oct 30 '13 at 22:10
    
FWIW, I was able to restore the backup to a non-EC2 instance, and again, all I needed was the password for the key to get going. So it looks like that part isn't specific to EC2. –  user1279779 Oct 30 '13 at 23:45
    
Also, I created the two boxes at the same time, so that might be part of it-- if I get more time, I'll try to reproduce with boxes created at different times and update. –  user1279779 Oct 30 '13 at 23:46
1  
Hmmm... could you please edit the question to include all of the code necessary to reproduce this? This seems very odd. –  Jon Seigel Oct 31 '13 at 2:59
1  
Using the test script, I had to provide the database master key password before I could decrypt the data. My knowledge in this area is a bit lacking... I would say this is an EC2 issue. –  Jon Seigel Oct 31 '13 at 17:53

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.