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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
-- 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; |
