SQL Server 2005: How to regenerate the same symmetric key in two different databases

In a previous post on using symmetric keys, I mentioned that keys can be recreated using the KEY_SOURCE and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY. In this post, I'd like to expand a little on this topic and present a small demo as well.

Because keys cannot be individually backed up and restored, there is no direct way of moving a key from one database to another. However, by specifying the same values for the ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE clauses of CREATE SYMMETRIC KEY, the same key can be generated on different databases. The KEY_SOURCE is the most important clause: the passphrase specified here is used to determine the key bits, so the phrase should be protected as carefully as the key itself or the data protected by it are protected. By specifying the same KEY_SOURCE, you are guaranteed to obtain the same key, assuming of course that you specified the same encryption algorithm. However, this is not sufficient to allow us to decrypt data encrypted by the key in another database - we also need for this key to be identified by the system as the same key - that is,  the key needs to have the same identifier, because this identifier is appended to the encrypted data and is used to determine the key that should be used for decryption. (The identifier of a key is shown in the key_guid column in the sys.symmetric_keys dialog and is also the value that needs to be passed to the encryptbykey functions). This is where the IDENTITY_VALUE clause comes into place - the phrase specified here will be used to generate a key identifier. The IDENTITY_VALUE clause doesn't have to be secret, but there's no reason it should be advertised either. The KEY_SOURCE is what you need to protect carefully.

And here is a small script that shows how to create a key using these clauses and how to decrypt data encrypted by it in a different database.

-- Set up the databases used for the demo
--
create database db_source;
create database db_destination;

use db_source;

-- Keep the key_source phrase carefully protected - it''s the key!!!
--
create symmetric key skey
with algorithm = triple_des,
identity_value = 'Data encryption key 07/06/2006',
key_source = 'Now he''s a clock-punching insurance claims adjuster fighting boredom and a bulging waistline.'
encryption by password = 'Avc#ptNO$cf@o!';

open symmetric key skey decryption by password = 'Avc#ptNO$cf@o!';

select * from sys.symmetric_keys;
select * from sys.openkeys;

-- Encrypt some data in a table
--
create table t (data varbinary(1024));
insert into t values (encryptbykey(key_guid('skey'), 'Top Secret!'));
select * from t;

close symmetric key skey;

-- Now copy the encrypted data to another table in another database
--
use db_destination;

create table t (data varbinary(1024));
insert into t (data) select t_src.data from db_source.dbo.t t_src;
select * from t;

-- Recreate the encryption key, so we can decrypt
-- The key can have a different name and can be protected with a different mechanism,
-- but it has to be obtained from the same algorithm, key_source, identity_value combo
-- In this database, we'll protect the key using a certificate
--
create master key encryption by password = 'Yahtf%pt@Hwht$f!O!';
create certificate cert_skey with subject = 'Certificate for accessing symmetric keys 07/06/2006';
create symmetric key skey2
with algorithm = triple_des,
identity_value = 'Data encryption key 07/06/2006',
key_source = 'Now he''s a clock-punching insurance claims adjuster fighting boredom and a bulging waistline.'
encryption by certificate cert_skey;

select * from sys.symmetric_keys;
select * from sys.openkeys;

-- Now use the key to decrypt the copied data
--
select convert(varchar(256), decryptbykeyautocert(cert_id('cert_skey'), NULL, data)) from t;

-- Cleanup
--
use master;

drop database db_source;
drop database db_destination;

You might want to experiment with the arguments used to create skey2. You will notice that if either the KEY_SOURCE or the IDENTITY_VALUE are not identical, the key will be created, but it will be different, and the decryption will not work.