Simple String Encryption and Decryption

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.

Related: Decrypt SQL Server Objects

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.

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.

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.

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

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.

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 passPhraseEncodersSET @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 passPhraseEncodersSET @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 "Download the Code" hotlink at the top of the page.