Simple pass phrase based encryption using SQL Server 2005

Here is how you can use a single pass-phrase based encryption using SQL Server built-in functions. (single pass-phrase encryption is also known as symmetric key encryption).

DECLARE @Passphrase nvarchar(50);
DECLARE @textToEncrypt nvarchar(128); 
DECLARE @encryptedText as varbinary(max) 
SET @Passphrase = 'This is my secret code'; 
SET @textToEncrypt = 'The quick brown fox jumped over the fence' 
SET @encryptedText = EncryptByPassPhrase(@Passphrase,@textToEncrypt) 

Select @encryptedText, len(@encryptedText);

-- Decrypting Data by DecryptByPassPhrase 
Select convert(nvarchar(max),DecryptByPassPhrase(@Passphrase,@encryptedText) )

EncryptByPassPhrase - http://technet.microsoft.com/en-us/library/ms190357.aspx

DecryptByPassPhrase - http://technet.microsoft.com/en-us/library/ms188910.aspx