To encrypt and decrypt a string we have a predefined functions in Sql Server 2008 that are easy to use. Suppose we want to save some data that doesn't need to be exposed as a plain text then in such case we can use these functions, in real scenario as we are storing password in a database.

These functions are ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE. The result of the encrypted string is in VARBINARY data type format. To encrypt the text this function uses Triple DES algorithm.

DECLARE @EncryptedString as VARBINARY(MAX);

SET @EncryptedString = (SELECT ENCRYPTBYPASSPHRASE ('Key@12','StringToEncrypt'));

SELECT @EncryptedString AS 'Encrypted String '

SELECT CONVERT(VARCHAR(150), DECRYPTBYPASSPHRASE ('Key@12',@EncryptedString)) AS 'Decrypted String'

Here is a sample of practical use to save passwords for each user in encrypted form and decrypt their password in case we want to authenticate the user.

CREATE TABLE tblUser
(
    ID INT,
    Username VARCHAR(250),
    EncryptedPassword VARBINARY(MAX)
)

INSERT INTO tblUser VALUES(1, 'User1', ENCRYPTBYPASSPHRASE('Key@12!','Password1'))
INSERT INTO tblUser VALUES(1, 'User2', ENCRYPTBYPASSPHRASE('Key@12!','Password2'))

SELECT * FROM tblUser

SELECT ID, Username, EncryptedPassword, CONVERT(VARCHAR(150), DECRYPTBYPASSPHRASE ('Key@12!', EncryptedPassword)) AS 'Plain Password'
FROM tblUser

The result of the above script is shown below:

Note: For more security we can save unique key like guid as password salt for each user.