Sample Code (T-SQL) - Protecting Identity Data with SQL 2005 Data Encryption

There are multiple ways to protect (encrypt) data with SQL 2005: either using certificate or password.

Here is my code sample to use a password to encrypt identity data (assuming the identity table name as tblIdentity_SmartCard table, the identity data column as Identiy_PIN, and GUID as column with primary key):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'replace_with_real_password'

CREATE ASYMMETRIC KEY asymPW1 WITH ALGORITHM = RSA_1024 ENCRYPTION by Password = 'Str0ngPa$$w0rd'

CREATE SYMMETRIC KEY symPW1 WITH ALGORITHM = DES

ENCRYPTION BY ASYMMETRIC KEY asymPW1

OPEN SYMMETRIC KEY symPW1 DECRYPTION BY ASYMMETRIC KEY asymPW1 WITH Password = 'Str0ngPa$$w0rd'

Declare @keyGUID UNIQUEIDENTIFIER

SET @keyGUID = (Select key_guid from sys.symmetric_keys WHERE name = 'symPW1')

Insert Into tblIdentity_SmartCard (Identiy_PIN) Values (EncryptByKey(@keyGUID, 'replace_with_PIN_data',1))

Select GUID, Identiy_PIN from tblIdentity_SmartCard

Select GUID, Convert(varchar,DecryptByKey(Identiy_PIN, 1))

from tblIdentity_SmartCard

Close SYMMETRIC KEY symPW1