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
