Updated posting on regenerating symmetric keys

Chris Skorlinski
Microsoft SQL Server Escalation Services.

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;