How to Encrypt Sensitive Text in SQL Server with ENCRYPTBYPASSPHRASE

Storing sensitive information in a database, like passwords or social security numbers, is common practice. However, storing them securely is less common. Unfortunately, one of the most typical approaches is to store sensitive information in a table as clear text. That means that anyone with access to that table can see all of that sensitive data.

Just to be clear, storing sensitive information as a clear text string is a really, really, really bad idea.

Not encrypting information in a database can cause serious problems. As just one example, if the database is compromised, all user passwords could be exposed. Data breaches are becoming more and more common. If the authorities come knocking on your door, you need to be able to show them that you at least made a concerned effort to protect that data.

Encrypting text that will need to be decrypted

In some cases, you may be able to store your sensitive data as strongly encrypted text that will never need to be decrypted. For example, hashing a password used for your application login and then just comparing the hashed password for the login instead of the actual password. But, in most cases, being able to decrypt the sensitive data is going to be necessary.

In these cases,  ENCRYPTBYPASSPHRASE (available in SQL Server 2008 and up) offers one of the simplest ways for you to encrypt sensitive information in a way that can also be decrypted (by using DECRYPTBYPASSPHRASE). At its very basic, ENCRYPTBYPASSPHRASE requires two mandatory arguments: a passphrase used to generate the encryption key and the text to be encrypted.  Notice that it specifies a passphrase, not password. There is an important difference between these two.

A passphrase vs. a password

As described in the ENCRYPTBYPASSPHRASE documentation: 

A passphrase is a password that includes spaces. The advantage of using a passphrase is that it is easier to remember a meaningful phrase or sentence than to remember a comparably long string of characters.

Many people don’t realize that you can use a space as a legitimate special character in most passwords. By doing this, you can generate a much more secure password sentence (or phrase) instead of a single word. An example of a passphrase may be something like “I forgot my password!”

Just to be clear, a space is not required in your passphrase for ENCRYPTBYPASSPHRASE. If you wanted to use a GUID for your passphrase or a random string such as “Zgt9$Ex%*unZO8Z},” that is perfectly acceptable.

Using ENCRYPTBYPASSPHRASE

For the examples in this post, I am going to use the encryption passphrase “This is my Passphrase!”, and the text to be encrypted is “ABC123”.

The basic syntax is:
ENCRYPTBYPASSPHRASE(‘encryption passphrase’, ‘text to encrypt’)

There are other arguments that can be used with ENCRYPTBYPASSPHRASE (see MSDN Doc), but for this simple example we are just using the two mandatory arguments.

To view the encrypted value of the text “ABC123”, you would use this script:

SELECT ENCRYPTBYPASSPHRASE(N'This is my Passphrase!', N'ABC123');

That SELECT statement will return a VARBINARY value such as: 0x0100000093EEC20B790EF208B1FB631F0AB3028E3A8C196643C4BD578528A0DFAE7AB45B

It is important to note that the VARBINARY value returned from ENCRYPTBYPASSPHRASE is nondeterministic, meaning that even with the same input it will not generate the same output every time.  So you can run the exact same SELECT statement multiple times and get a different result each time.

Thankfully, this output has no bearing on using the DECRYPTBYPASSPHRASE function. As long as you have the correct passphrase, DECRYPTBYPASSPHRASE will successfully decrypt any of those VARBINARY results to their original value.

Storing an encrypted value in a table

Now that we know how to encrypt a sensitive text string, let’s take a look at how to store that encrypted value in a table.  Since the value returned from ENCRYPTBYPASSPHRASE is a VARBINARY data type, that is how we want to store it since this is also the data type required by DECRYPTBYPASSPHRASE.

The first thing we need to do is determine the size of our encrypted column in our table. The VARBINARY values returned by ENCRYPTBYPASSPHRASE can vary in size, with maximum size of 8,000 bytes. The size of the returned value is going to depend on the size of the actual text being encrypted. You can use the DATALENGTH function to help figure that out. If you have a way to control the maximum allowed length of the sensitive text value you want to encrypt, use that size for your table column, but try not to use VARBINARY(8000) if you don’t have to.

Here is a simple example of storing our encrypted text in the [Password] column of a table:

CREATE TABLE dbo.Users ([UserName] VARCHAR(50), [Password] VARBINARY(50))
 
INSERT INTO dbo.Users ([UserName], [Password])
VALUES ('Charlie Brown', ENCRYPTBYPASSPHRASE(N'This is my Passphrase!', N'ABC123'))
 
SELECT [UserName], [Password]
FROM dbo.Users

Using DECRYPTBYPASSPHRASE

Now that we have our sensitive text encrypted, we need to be able to decrypt it as well.  This is easily done by using the DECRYPTBYPASSPHRASE function with the same passphrase we encrypted our text string with. However, DECRYPTBYPASSPHRASE also returns a VARBINARY value, which we will have to convert to a string.  This can be done by adding a CONVERT function to our SELECT statement.

SELECT [UserName], CONVERT(NVARCHAR, DECRYPTBYPASSPHRASE(N'This is my Passphrase!', [Password]))
FROM dbo.Users

Now you should see your decrypted value returned correctly in clear text. ENCRYPTBYPASSPHRASE offers a quick and easy way for you to encrypt text in SQL Server and can be useful for encrypting sensitive information if you need to be able to decrypt it later. 

Want to work with The SERO Group?

Want to learn more about how The SERO Group helps organizations take the guesswork out of managing their SQL Servers? Schedule a no-obligation discovery call with us to get started.