Password Encryption and Decryption in SQL Server 2005

Encryption of column data in SQL 2005 is a good approach while we think of some one tampering our database through some sql injection and methods like that

Here are the steps involved

1. Create a MASTER KEY ENCRYPTION for your database

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Give a Password Here>'

2.Create a Certificate

CREATE CERTIFICATE SampleCertificate  
WITH SUBJECT = '<Give some Description about the certificate>'

3.Create the symmetric Key (You'll use the certificate created above while creating a symmetric key)

CREATE SYMMETRIC KEY SamplePassword    
WITH ALGORITHM = AES_256    ENCRYPTION BY CERTIFICATE SampleCertificate

You can select an encryption algorithm of your choice while creating a Symmetric key.

Your keys for the Encryption and Decryption are ready now. Open your keys and do the encryption or decryption as follows

Encrypting the Password

OPEN SYMMETRIC KEY IMCPSPassword   
DECRYPTION  BY CERTIFICATE IMCPSCert;   

UPDATE [dbo].[Users]   
SET [Password] =  EncryptByKey(Key_GUID('IMCPSPassword'),Password)

Decrypting the Password

SELECT 
         [UserID],
         CONVERT(varchar, DecryptByKey(Password)) as Decryptedpassword      
FROM     [dbo].[Users]