Encrypt Decrypt Password in SQL 2005

 BEGIN TRAN

-- Create Master Key

CREATE MASTER KEY ENCRYPTION BY
    PASSWORD ='man@123'


-- Create Certificate

CREATE CERTIFICATE TEST_Certificate
    WITH SUBJECT='Maneesh'


-- Create Symmetric Key

CREATE SYMMETRIC KEY TEST_SYMKEY
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE TEST_Certificate


-- Inserting records into the table

    OPEN SYMMETRIC KEY TEST_SYMKEY
       DECRYPTION BY CERTIFICATE TEST_Certificate;


    INSERT INTO TestTable
    ([NAME], PASSWORD)
    VALUES
    ('amit', EncryptByKey(Key_GUID('TEST_SYMKEY'), 'pass@123'))


    CLOSE SYMMETRIC KEY TEST_SYMKEY


-- Drop or Close Master Key

    CLOSE MASTER KEY -- FOR CLOSE
    DROP MASTER KEY -- FOR DROP

-- Getting the output again

    OPEN SYMMETRIC KEY TEST_SYMKEY DECRYPTION
    BY CERTIFICATE TEST_Certificate

    SELECT CONVERT(VARCHAR(50),DecryptByKey(Password)) AS DecryptSecondCol
    FROM TestTable




Reference: Govind Badkur(http://sqlserver20.blogspot.com)