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):

BY PASSWORD = '123456'

WITH SUBJECT = '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)


-- 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?

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.