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
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'
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.