Encrypting and decrypting strings in T-SQL code is complicated in SQL Server 2000 and earlier. You have to use the undocumented PWDEncrypt and PWDCompare functions or use symmetric (i.e., secret) or asymmetric (i.e., public) keys. Fortunately, encrypting and decrypting strings in T-SQL code is much easier in SQL Server 2008 and SQL Server 2005. One of the easiest ways is to use the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions.
To demonstrate the ease of using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE, I created two simple functions: dbo.Encrypt and dbo.Decrypt. The dbo.Encrypt function encrypts the plaintext that you pass to it, whereas the dbo.Decrypt function decrypts the ciphertext you pass in.
As Listing 1 shows, the dbo.Encrypt function gets a nvarchar string and encrypts it to a varbinary result using the ENCRYPTBYPASSPHRASE function.
Listing 1: The dbo.Encrypt Function |
 |
ENCRYPTBYPASSPHRASE has two mandatory arguments: a passphrase (which will be used to generate the encryption key) and the plaintext to be encrypted. In this case, the passphrase is 'SQL SERVER 2008' and the @str variable contains the plaintext to be encrypted. As the latter demonstrates, you have the option of storing either of these arguments in variables.
Listing 2 shows the dbo.Decrypt function.
Listing 2: The dbo.Decrypt Function |
 |
It gets an encrypted varbinary string and decrypts it into a nvarchar string using the DECRYPTBYPASSPHRASE function. Like the ENCRYPTBYPASSPHRASE function, the DECRYPTBYPASSPHRASE function has two mandatory arguments: a passphrase (which will be used to generate the decryption key) and the ciphertext to decrypt. In this case, the passphrase is 'SQL SERVER 2008' and the @encryp variable contains the ciphertext to be decrypted. (The passphrase that you use to decrypt the ciphertext needs to be the same as the passphrase you used to encrypt it.)
To call the dbo.Encrypt function, you use the syntax
dbo.Encrypt (ItemToEncrypt)
where ItemToEncrypt is the plaintext you want to encrypt or a variable that contains the plaintext.
To call the dbo.Encrypt function, you use the syntax
dbo.Encrypt (ItemToDecrypt)
where ItemToDecrypt is the ciphertext you want to decrypt or a variable that contains the ciphertext.