SQL Server encryption/decryption

Question

As part of disaster recovery testing I am trying to ensure that I am able to recreate my DB and still be able to decrypt previously encrypted strings.

So here is what I am doing as a test...

1. Creating a database certificate.
2. Backing up the certificate and private key to disk.
3. Creating a symmetric key, encrypted by certificate.
4. Using EncryptByKey to encrypt "Hello World" into a hex string. Keep a hold of this encrypted string for use below.
5. Using DecryptByKey to decrypt the hex string into "Hellow World".

This all works great, but then I'm trying this...

1. DROP the key and the cert.
2. Recreate the cert again from the backed up cert.
3. Create a new symmetric key exactly as before.
4. Try and decrypt a previously encrypted string and it doesn't work.

The only way I can get it to work is by specifying a KEY_SOURCE and IDENTITY_VALUE when creating the symmetric key but MSDN says that IDENTITY_VALUE is for creating "temporary keys" so not sure about using this.

Any ideas on this?

Answer 1

There cannot be a disaster recovery plan that requires the user to provide the symmetric key material to recover the data. Symmetric keys are stored together with the data in the database and the disaster recovery plan that recovers the data will recover the keys as well in the process.

All encryption schemes use a key hierarchy that starts from an user provided artifact: a password or an external key. This is used to encrypt other keys in turn just to simplify management and operations: 1) to allow for easy key change w/o re-encrypting all the data and 2) to allow use of a fast symmetric key to encrypt large data volumes. The only thing needed after a recovery is the password to access the master key at the top of the hierarchy. Your testing by dropping the keys used to encrypt the data is, basically, non-sense.

You are down a very very bad and dangerous path of using known key material (KEY_SOURCE) to encrypt your data. Your keys sources are very likely to leak in scripts and other media that stores key material (emails, config files etc), so in practice you'd be better to simple not encrypt anything, since your keys are known by too many and therefore useless.