How to encryt a Date field?

How do we encrypt/decrypt a datetime field? For e.g. Date of Birth

The way to encrypt any data type that cannot be implicitly converted to varbinary (such as datetime, integers, etc.) is to explicitly convert or cast the value to varbinary.

For example:

CREATE SYMMETRIC KEY key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

OPEN SYMMETRIC KEY key_01 DECRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

DECLARE @.mySecretDate datetime

DECLARE @.mySecretDateBlob varbinary(100)

SET @.mySecretDate = '01/02/2008 12:45PM'

-- Explicitly convert/cast the datetime variable to a varbinary

SET @.mySecretDateBlob = EncryptByKey( key_guid('key_01'), convert(varbinary(100), @.mySecretDate))

SELECT @.mySecretDateBlob as 'datetime ciphertext'

-- To decrypt, do teh same trick, after decrypting, convert/cast to datetime

SELECT Convert( datetime, DecryptByKey( @.mySecretDateBlob )) as 'datetime plaintext'

go

I hope this information is useful.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul garcia,

How should the date field be stored in the database, should it be datetime or varbinary?

I am inserting data using a stored procedure that encrypts the date using encryption keys. I am using a view to decrypt the date.

Table design

Firstname -- varbinary(50)

secretdatevarbinary(100)

Stored procedure:

DECLARE @.FIRSTNAME varchar(100)

DECLARE @.secretdate datetime

OPEN SYMMETRIC KEY key_01 DECRYPTION BY PASSWORD = '50m3 p4xw0Rd&'

go

insert into tbltest firstname, lastname, secretdate values(

EncryptByKey( key_guid('key_01'), @.FIRSTNAME),

EncryptByKey( key_guid('key_01'), convert(varbinary(100), @.secretdate ))

close symmetic keys

How do i decrypt the date field?

Please help.Thanks in advance

Ks

|||

Hi Ks,

I'm not sure if I understand your question.

Encrypted data will be varbinary so the encrypted text should be stored in a varbinary column. As for decrypting the data, I am copying and pasting Raul's response from before:

DECLARE @.mySecretDateBlob varbinary(100)

-- To decrypt, do teh same trick, after decrypting, convert/cast to datetime

SELECT Convert( datetime, DecryptByKey( @.mySecretDateBlob )) as 'datetime plaintext'

This should convert the encrypted text back into a plaintext datetime format. Please let us know if this helps you out and please post any further questions you may have.

Sung

edit - copied the wrong "DECLARE" method, the DECLARE should now be correct