2005 encryption - symmetric keys

Hi There

We have been playing around with encryption in 2005. I cannot find a BOL topic that discusses dropping encryption objects such as keys.

We do the followign steps:

Create master key with password, then we create a certificate using the master key, we then create a symmetric key using this certificate and encrypt data columns.

But what i find worrying is that you can then drop the symmetric key , there are no warnings that you have objects dependant on this key for decryption.

Once you have dropped the key you cannot decrypt the data anymore?

Also the key defults the expiration date to 1 year.

WHat happens after 1 year when you have encrypted data and an expired key, or someone drops the key ? How can you ever decrypt the data after that ?

You can backup master keys nd certificates but not symmetric keys?

It seems to be that youc an very easily orphan encrypted data by the loss of the symmetric key for whatever reason, is this correct ?

Thanx

In SQL Server 2005 we have support to explicitly encrypt/decrypt data using builtins, but there is no way to mark a column as being used to store encrypted data, therefore there is no way to bound any key to the data encrypted by it. Unfortunately this means that the system doesn’t know if any given symmetric key is being used or not, and they can be accidentally dropped.

As you mentioned, you can create backups for master key and certificates, but in SQL Server 2005 there is no support for backing up individual symmetric keys. To prevent symmetric key loss you can create a database backup or alternatively create your symmetric keys using the KEY_SOURCE and IDENTITY_VALUE syntax. This syntax will allow you to recreate the keys in case they are dropped by mistake, if you don’t want to permanently store the key in the database or if you need to create the same symmetric key in a different database. Example:

CREATE SYMMETRIC KEY key_demo2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE cert_demo2

go

-- Create a symmetric key with KEY_SOURCE and IDENTITY_VALUE so it can be recreated

CREATE SYMMETRIC KEY key_demo_recreate WITH ALGORITHM = TRIPLE_DES,

KEY_SOURCE = 'Key source - keep this source secret protected!',

IDENTITY_VALUE = 'My Key identity'

ENCRYPTION BY CERTIFICATE cert_demo

go

-- Create a table to store some encrypted data

CREATE TABLE t_secret_data( mySecret varbinary( 100 ) )

go

OPEN SYMMETRIC KEY key_demo_recreate DECRYPTION BY CERTIFICATE cert_demo

go

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 1' ))

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 2' ))

INSERT INTO t_secret_data values( encryptbykey( key_guid( 'key_demo_recreate' ), 'Secret data 3' ))

go

SELECT convert( varchar(100), decryptbykey( mySecret)) FROM t_secret_data

go

CLOSE SYMMETRIC KEY key_demo_recreate

go

-- We can drop the key, either by mistake or on purpose

DROP SYMMETRIC KEY key_demo_recreate

go

-- At this point we cannnot recover the encrypted data.

-- We need to recreate the symmetric key to recover it.

-- notice that the key name is not necessarily the same

CREATE SYMMETRIC KEY key_demo_recreate2 WITH ALGORITHM = TRIPLE_DES,

KEY_SOURCE = 'Key source - keep this source secret protected!',

IDENTITY_VALUE = 'My Key identity'

ENCRYPTION BY CERTIFICATE cert_demo

go

-- Notice that we use the new key name

OPEN SYMMETRIC KEY key_demo_recreate2 DECRYPTION BY CERTIFICATE cert_demo

go

-- Same select statement

-- As the key ring uses the key identifier (key_guid),

-- and not the key name we don't need to change the decryption statement

SELECT convert( varchar(100), decryptbykey( mySecret)) FROM t_secret_data

go

CLOSE SYMMETRIC KEY key_demo_recreate2

go

The certificate expiration date is stored in metadata so it can be used/enforced by any application, but it won’t be used by SQL Server for any of the encryption features. You will be able to encrypt/decrypt data and keys using an expired certificate, but be aware that other SQL Server features such as Service Broker will enforce this expiration date.

I hope this information was able to answer your questions, please feel free to send us follow up questions and additional feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul

That does help alot, exactly what i wanted to know did you get this from BOL, i had trouble searching for a BOL topic with this information, if so do youhave the name of the topic for me ?

Thanx

|||

I am part of the test team for this particular feature and I am quite familiar with it; I really didn’t look for this information on BOL; but I think you are right, we don’t have an article describing symmetric keys and potential data loss in case of an accidental DROP SYMMETRIC KEY in detail. I will open a bug for BOL.

In the meantime, I recommend the following resources:

* CREATE SYMMETRIC KEY (Transact-SQL): http://msdn2.microsoft.com/en-us/library/ms188357.aspx

* DROP SYMMETRIC KEY (Transact-SQL) : http://msdn2.microsoft.com/en-us/library/ms182698.aspx

* How to: Create Identical Symmetric Keys on Two Servers: http://msdn2.microsoft.com/en-us/library/ms366281.aspx

* How to: Encrypt a Column of Data : http://msdn2.microsoft.com/en-us/library/ms179331.aspx

* Laurentiu Cristofor's blog : http://blogs.msdn.com/lcris/

* Raul Garcia’s blog: http://blogs.msdn.com/raulga/

Thanks a lot for the feedback, and feel free to send us more questions and feedback. We really appreciate it.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Excellent, thanx a million Raul.

Yes a BOL topic that discussed dependancies of encryption objects, as well as considerations when dropping Keys etc would definately be valuable, thanx for the feedback.

Cheers