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.

Comments (18)

  1. mikki says:

    Thanks, useful information

  2. Catherine Seto says:

    In Replication, do I create symmetric in this way, so the source database is the publisher, then I setup replication, with data pushed to the subscriber, then re-generate the symmetric key at the subscriber (i.e. destination database in your script)?

  3. Yes, because replication cannot be set to replicate encryption keys, you would have to replicate them manually. You could also replicate the keys programatically, by storing the information used to generate them in a table and publishing that table, but such a solution would be less secure, because gaining access to the information in this table would compromise the keys.

  4. Cihat says:

    First of all, thank you for your blogs on sql server 2005 encryption. It is very useful for me (and lots of others).

    What are the possibilities to restore a database on another sql 2005 database server? My main concern is that I encrypt table columns (with certificates) in server A and for a variaty of reasons I have to restore the database on server B. Is this possible, and if yes, can this be done without re-encrypting a whole table column which can contain millions of records?

  5. Yes, you can restore a database without having to re-encrypt the data in it. See last paragraphs of http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx and my answer to Regan Wick from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx.

  6. Felipe Cembranelli says:

    Hi,

    I have the following scenario:

    1) SQL SERVER 2005 (central database server)

    2) Several SQL SERVER 2005 Express (plant side databases)

    I want to create a sql certificate in the “central” side and share it with my plants. This certificate will be used to encryp/decrypt data in the databases.

    Is that possible? I have seem a lot of documentation about to share certificate in databases running in the same server.

    Felipe Cembranelli

  7. Felipe Cembranelli says:

    Is that possible to do the same using just certificate, i mean, regenerate the same certificate in two differents databases? Is that make sense?

  8. You can just backup the certificate to files and then restore it on the Express machines. Look at BACKUP/RESTORE CERTIFICATE in Books Online for additional information.

  9. Robert Byrne says:

    Is there any explanation about how KEY_SOURCE is turned into a key for the given algorithm? Now that the format of the encrypted message is (tentatively) known, this is the last piece of the puzzle I need to perform encryption on the client (C#) and maintain compatibility with DecryptByKey

  10. The KEY_SOURCE string is hashed using SHA1 and that hash is fed into CryptDeriveKey.

  11. Adam says:

    Regarding using the SHA1 of the KEY_SOURCE and CryptDeriveKey, what are the other CryptDeriveKey parameters?

  12. Nothing special from what I remember: the key was just marked as exportable. Remember to pass the key length in the flags parameter.

  13. Adam says:

    Your response is greatly appreciated!

    I know the key generated by SQL is 128bits by looking at sys.symmetric_keys

    But CryptDeriveKey will not work with 128 passed as a parameter, only 192.

    (I can pass in 128 if I use RC2 instead of "TripleDES".)

    byte[] key = pdb.CryptDeriveKey("TripleDES", "SHA1", 64*3, tdes.IV);

    How is the salt created?  

    Since the same KEY_SOURCE on different servers creates the same key, the salt must be deterministic.

    Here is the .Net code I have tried, can you point me in the right direction:

           string strPwd = "really long passphrase";

           byte[] pwd = (new SHA1CryptoServiceProvider()).ComputeHash(Encoding.ASCII.GetBytes(strPwd));

           byte[] salt = new byte[8];

           // Create a TripleDESCryptoServiceProvider object.

           TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();

           try

           {

               Console.WriteLine("Creating a key with PasswordDeriveBytes…");

               // Create a PasswordDeriveBytes object and then create

               // a TripleDES key from the password and salt.

               PasswordDeriveBytes pdb = new PasswordDeriveBytes(pwd, salt);

               // Create the key and set it to the Key property

               // of the TripleDESCryptoServiceProvider object.

               byte[] key = pdb.CryptDeriveKey("TripleDES112", "SHA1", 64*3, tdes.IV);

               Console.WriteLine(BitConverter.ToString(key).Replace("-", ""));

               Console.WriteLine("Operation complete.");

               Console.ReadLine();

           }

           catch (Exception e)

           {

               Console.WriteLine(e.Message);

           }

  14. You are working with managed code and I have no experience with the cryptographic wrappers in managed code – I don't know if their behavior matches that of the CAPI calls used in SQL Server. The CryptDeriveKey call is described here: msdn.microsoft.com/…/aa379916(VS.85).aspx. Note that for that API, the CRYPT_EXPORTABLE flag needs to be ORed with the key length shifted left by 2 bytes – you don't just pass the key length as a parameter. This is what the API page says: "The sizes of a session key can be set when the key is generated. The key size, representing the length of the key modulus in bits, is set with the upper 16 bits of this parameter."

    Also, I don't remember any salt value being used when generating a key from a passphrase.

    You should probably try first to reproduce the SQL Server key generation using C++ code and the native CAPI calls. If you are successful, then you can try to reproduce the same behavior with managed C# code. But I never used C# for cryptographic operations, so I cannot advise you on this aspect.

    You should ask for further help on the security forum at: social.msdn.microsoft.com/…/threads. Raul Garcia might be able to help you there. I think he had worked in the past with a customer attempting to do the same thing as you, so he may have some useful advice to offer.

  15. One more thing: if a key length of 128 doesn't work, try 112: the CryptDeriveKey page I already mentioned has a table of key lengths and uses 112 rather than 128 (the difference between the two numbers represents parity bits, which are apparently not counted as part of the key by the API).

  16. Dean Jones says:

    What if we accidently delete a symmetric key which was not created using KEY_SOURCE or IDENTITY_VALUE? How do we recover this key? I was testing this in a non-prod server. I  restored the backup with a different db name and tried to copy the key from there.. it didn't work. I wasn't able to decrypt the data. What do you suggest this scenario? thanks

  17. If you delete a key not created with KEY_SOURCE and the key was not captured in a database backup, then it is lost. If you restore the backup, you can get access to the key within the backed up database, but there is no functionality for moving keys across databases.

    You might be able to edit database metadata and copy paste the encrypted key blob from one database to the other, but now we are talking about unsupported operations that can lead to database corruption.