Encryption in SQL 2005

In Sql Server 2005 we have a easy way to encrypt data by Using of Symmetric Keys

Follow the steps to Encrypt your Password

Step 1 : Create A Mater Key.
syntax : CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

Step 2 : Create Certificate For Encryption
Syntax : CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'CSCDEV Test Certificate',
START_DATE = '1/1/2007',
EXPIRY_DATE = '10/31/2009';

Step 3 : Create A Symmetric key to Encrypt data, here we need to use Certificate
Syntax : CREATE SYMMETRIC KEY CSCTestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
DECRYPTION BY CERTIFICATE TestCertificate;

Step 4 : Open The Symmetric Key to Encrypt Data
syntax : OPEN SYMMETRIC KEY CSCTestSymmetricKey

Step 5 : To Encrypt Data we have a built-in function EncryptByKey()
syntax : EncryptByKey(KEY_GUID('SymmetricKey'),'[Data to Encrypt]')
Ex : EncryptByKey(KEY_GUID('CSCTestSymmetricKey'),'Test')

Step 6 : To Decrypt already Encrypted Data we have a built-in function
syntax : DecryptByKey(['Encrypted Data'])
Ex :CONVERT(varchar(100), DecryptByKey(Password))

There is no need to Create Key to Every time, create them once and use those in all you Queries
but in every query you need to Open the Symmetric key and write your queries then Close Symmetric Key

EX :
OPEN SYMMETRIC KEY CSCTestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;


INSERT INTO [Employee] (USERID,PASSWORD)
Values ('testuser',EncryptByKey(Key_GUID('CSCTestSymmetricKey'),'sandeep')
WHERE emp_id = 19

close SYMMETRIC KEY CSCTestSymmetricKey



DROP KEYS

DROP MATER KEY
DROP Certificate 'Certificate Name'
DROP Symmetric Key 'Symmetric Key Name'