SQL Server 2008 Encryption Without Keys

In addition to using certificates, asymmetric keys, and symmetric keys, you can encrypt your data using passphrases. A passphrase is a string or binary value from which SQL Server can derive a symmetric key to encrypt your data. The EncryptByPassPhrase and DecryptByPassPhrase functions allow you to use this type of encryption, as in the following example:

DECLARE @plain_text nvarchar(1000),
@enc_text varbinary(2000);
SET @plain_text = N'Ask not what your country can do for you...';
SET @enc_text = EncryptByPassPhrase(N'E Pluribus Unum', @plain_text);
SELECT 'Original plain text = ', @plain_text;
SELECT 'Encrypted text = ', @enc_text;
SELECT 'Decrypted plain text = ',
CAST(DecryptByPassPhrase(N'E Pluribus Unum', @enc_text) AS nvarchar(1000));

EncryptByPassPhrase accepts the plain text that you want to encrypt. DecryptByPassPhrase, on the other hand, accepts the previously encrypted cipher text that will be decrypted. For both functions, you can add an authenticator value to further obfuscate your encrypted text, as follows:

SET @enc_text = EncryptByPassPhrase(N'E Pluribus Unum', @plain_text,
1, N'Authentic');

Both functions return a varbinary(8000) value. After you use DecryptByPassPhrase, you may need to cast your result back to another datatype, such as varchar or nvarchar.

EncryptByPassPhrase and DecryptByPassPhrase use the Triple DES algorithm to encrypt and decrypt data. You cannot choose another algorithm to encrypt and decrypt with these functions.

Source of Information : Apress Accelerated SQL Server 2008