謝謝俊宇對 SQL 2005 加解密的提醒

MSDN forum 對 certificate expire 和遺失鑰匙有很棒的解答
 
 
 
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.

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: