Simple String Encryption and Decryption

Listing 3 gives a simple example of how to incorporate the dbo.Encrypt and dbo.Decrypt calls in code.

Listing 3: Sample Code Showing How to Use the Functions

This code uses the dbo.Encrypt function to encrypt the phrase 'Eli Leiba is a SQL guru', stores the result (an encrypted string) in the @code variable, then prints it. Next, the code uses the dbo.Decrypt function to decrypt the string in @code, stores the result (i.e., 'Eli Leiba is A SQL guru') in the @x variable, then prints it.

As you can see, the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions are easy to use when you need to encrypt and decrypt strings. However, there's a caveat—you're responsible for managing and securing the passphrase. By default, no permissions are required to execute the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions and you don't have the ability to select the algorithm they use for encryption and decryption. So, depending on how your SQL Server environment is set up, a few people might be able to see the passphrase and even execute the dbo.Encrypt and dbo.Decrypt functions if they had access to their code.

However, there are ways to help secure passphrases, such as storing them in a table that resides in msdb or a master system database and allowing only DBAs with the sysadmin server role access to that table. For example, the code in Listing 4 creates a table named passPhraseEncoders and inserts the 'SQL SERVER 2008' passphrase into it.

Listing 4: Code That Creates and Loads the passPhraseEncoders Table

After you give this table the necessary permissions, you need to create a synonym named passPhraseEncoders for master.dbo.passPhraseEncoders in the application database.

Then, you need to adapt the dbo.Encrypt and dbo.Decrypt functions accordingly. In the dbo.Encrypt function in Listing 1, you need to replace the line in callout A with the code

DECLARE @passPhrase varchar(80)
SELECT @passPhrase = Pass_Phrase from passPhraseEncoders
SET @res = EncryptByPassPhrase(@passPhrase,@str)

In the dbo.Decrypt function in Listing 2, you need to replace the line in callout A with the code

DECLARE @passPhrase varchar(80)
SELECT @passPhrase = Pass_Phrase from passPhraseEncoders
SET @res = DecryptByPassPhrase(@passPhrase,@encryp)

Although storing the passphrases in a protected table provides an additional layer of security, even this setup has its vulnerabilities. Only reliable DBAs and trusted individuals should create these functions. It's also a good practice to create them with the ENCRYPTION clause. Consequently, you might not want to use the dbo.Encrypt and dbo.Decrypt functions for encrypting text just before exporting it out of your database to an exposed text file, for example.

The dbo.Encrypt and dbo.Decrypt functions work on SQL Server 2008 and SQL Server 2005. (I've ran them on SQL Server 2005 SP1 and SQL Server 2008 Express.) You can download the functions' code by clicking the 102788.zip hotlink at the top of the page.