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?