Updated posting on regenerating symmetric keys
I came across a great posting by Laurentiu Cristofor [MSFT] called “SQL Server 2005: How to regenerate the same symmetric key in two different databases”. I modified his “column encryption”example slightly to include DecryptByKey and reposted it below. The sample wraps in blog posting but is readable after you paste it into SQL Server Management Studio query window.
-- 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;
name principal_id symmetric_key_id key_length key_algorithm algorithm_desc create_date modify_date key_guid key_thumbprint provider_type cryptographic_provider_guid cryptographic_provider_algid
----------------------------------- ------------ ---------------- ----------- ------------- ----------------------------------- ----------------------- ----------------------- ----------------------------------- ----------------------------------- ----------------------------------- ----------------------------------- -----------------------------------
skey 1 256 128 D3 TRIPLE_DES 2011-10-24 18:27:58.367 2011-10-24 18:27:58.367 0A66A100-03D2-B6C6-9A26-0F67D629A39 NULL NULL NULL NULL
(1 row(s) affected)
--Display open symetric keys
select * from sys.openkeys;
database_id database_name key_id key_name key_guid opened_date status
----------- ----------------------------------- ----------- ----------------------------------- ----------------------------------- ----------------------- ------
50 db_source 256 skey 0A66A100-03D2-B6C6-9A26-0F67D629A39 2011-10-24 18:34:30.933 1
(1 row(s) affected)
-- 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;
--data with symmetric key closed
select * from t;
data
-------------------------------------
0x00A1660AD203C6B69A260F67D629A39301
(1 row(s) affected)
-- 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;
--Attempt to retrieve encrypted data
select * from t;
data
-------------------------------------
0x00A1660AD203C6B69A260F67D629A39301
(1 row(s) affected)
-- 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;
--Display the keys
select * from sys.symmetric_keys;
name principal_id symmetric_key_id key_length key_algorithm algorithm_desc create_date modify_date key_guid key_thumbprint provider_type cryptographic_provider_guid cryptographic_provider_algid
----------------------------------- ------------ ---------------- ----------- ------------- ----------------------------------- ----------------------- ----------------------- ----------------------------------- ----------------------------------- ----------------------------------- ----------------------------------- -----------------------------------
##MS_DatabaseMasterKey## 1 101 128 D3 TRIPLE_DES 2011-10-24 18:31:16.230 2011-10-24 18:31:16.230 6E23AA00-2372-4A83-9100-703C9B20FB9 NULL NULL NULL NULL
skey2 1 256 128 D3 TRIPLE_DES 2011-10-24 18:31:16.447 2011-10-24 18:31:16.447 0A66A100-03D2-B6C6-9A26-0F67D629A39 NULL NULL NULL NULL
(2 row(s) affected)
--use the symmetric key skey2 to decrypt the data
--open the key first, protected by the certificate, then run query
OPEN SYMMETRIC KEY skey2
DECRYPTION BY CERTIFICATE cert_skey;
GO
SELECT data as 'encrypted',
CAST(DecryptByKey(data) AS VARCHAR(1000)) as 'decrypted'
from t
encrypted decrypted
------------------------------------- -----------------------------------
0x00A1660AD203C6B69A260F67D629A39301 Top Secret!
(1 row(s) affected)
--Display open symmetric keys
select * from sys.openkeys;
database_id database_name key_id key_name key_guid opened_date status
----------- ----------------------------------- ----------- ----------------------------------- ----------------------------------- ----------------------- ------
51 db_destination 256 skey2 0A66A100-03D2-B6C6-9A26-0F67D629A39 2011-10-24 19:20:28.250 1
(1 row(s) affected)
--close the symmetric key
close symmetric key skey2;
--If you attempt to display the encrypted data without KEY, you'll only see NULL
SELECT data as 'encrypted',
CAST(DecryptByKey(data) AS VARCHAR(1000)) as 'decrypted'
from t
encrypted decrypted
------------------------------------- -----------------------------------
0x00A1660AD203C6B69A260F67D629A39301 NULL
(1 row(s) affected)
-- Use the certificate cert_skey key to decrypt symmetric key display encrypted data
select data as 'encrypted', convert(varchar(256),
decryptbykeyautocert(cert_id('cert_skey'), NULL, data)) as 'decrypted'
from t;
encrypted decrypted
------------------------------------- -----------------------------------
0x00A1660AD203C6B69A260F67D629A39301 Top Secret!
(1 row(s) affected)
-- Cleanup
--
use master;
drop database db_source;
drop database db_destination;
