Encrypting Passwords Using EncryptByPassPhrase

While storing passwords in a database may be a common practice, storing them properly usually isn’t so common.  This is part of a storing passwords blog series where we will examine some of the options available for storing passwords in a SQL Server database.

To recap the introduction to this series, when you store a password in a database you basically have 3 choices as to how you are going to do it.  You can save the password as:

• Unencrypted clear text that can be viewed by anyone with read access to the table
• Encrypted text that has a need to be decrypted
• Strongly encrypted text that will never need to be decrypted

In the introduction, we established that storing passwords as an unencrypted clear text string is a really, really, really bad idea.  In this post of our series, we’re going to address one of the ways you can accomplish the second choice:

Encrypting text that has a need to be decrypted

Now, some will argue that you should always store your passwords as strongly encrypted text that will never need to be decrypted, and in most cases I would agree with that.  However, I have run across situations where being able to decrypt the password is necessary.  For example, I once worked on an application that connected to dozens of vendor FTP sites to upload/download files nightly.  These FTP passwords were stored encrypted in our database, but would have to be retrieved and decrypted and used for authentication to the vendor’s FTP site.

In cases like that,  ENCRYPTBYPASSPHRASE (available in SQL Server 2008 and up) offers one of the simplest ways for you to encrypt your passwords in a way that can also be decrypted (by using DECRYPTBYPASSPHRASE).

At the 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.  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.  So, an example of a passphrase may be something like “My dog has fleas”, or “I would love some ice cream”, or for us forgetful guys out there “My wedding anniversary is June 31st”.  (although, as explained in this XKCD comic, your passphrase really needs to be something a little more random). 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 “RZgt9$ExYunZO8Zf{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}s8hxKV@lHPxz85CJ”, you could.

For the examples in this post, I’m going to use a passphrase given to me by my six year old. When I asked him what his password was, he replied “I am not going to tell you what my password is!”.  That is the passphrase we will use.

Using ENCRYPTBYPASSPHRASE

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 password “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 3 times, and get 3 different results.  Try it:

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 its orignal value.  I’m not exactly sure why/how this works, it’s just part of the secret sauce and it is what it is.

Storing An Encrypted Value In A Table

Now that we know how to encrypt a password 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 password column.  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 password being encrypted.  For example, if we check the DATALENGTH of our above SELECT statement, we will see that the password of  ‘ABC123’ has a length of 36.  However, if we change the password to ‘ABC123456’ the size of our returned value is 44.  And, with a password of ‘supercalifragilisticexpialidocious’ (it could happen!) we get a size of 92.

Since we already know that our test value has a length of 36 this is the value I am going to use, because I really don’t want to use VARBINARY(8000) if I don’t have to.  If you know what the maximum allowed length of your users passwords are (and you should!), then you can use the answer in this DBA StackExchange post to help calculate the size of your  ENCRYPTBYPASSPHRASE output, and set your password table column size accordingly.

Here is a simple example of storing our encrypted password:

Using DECRYPTBYPASSPHRASE

Now that we have our password 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 password with.

And you will see that you get a returned password value of something along the lines of ‘0x4100420043003100320033000x410042004300310032003300’

Wait..what?  That’s not our password!
Just like its ENCRYPTBYPASSPHRASE counterpart, DECRYPTBYPASSPHRASE returns a VARBINARY value, which we will have to convert to a usable string.  This can be done easily by adding a CONVERT function to our SELECT statement.

Now you should see your decrypted password returned correctly in clear text.

So, at this point, you’re probably asking the following question: If using ENCRYPTBYPASSPHRASE for password encryption,  should I use the same passphrase for all of the passwords in my database?  Probably not. (That really means “NO!”) Every encrypted password should have its own unique passphrase, which will need to be stored with (or somehow associated to) that record.  This is where being able to use a UNIQUEIDENTIFIER comes in handy, because it is much easier to automatically generate a GUID for each password than generating a phrase.

The Bottom Line:
ENCRYPTBYPASSPHRASE offers a quick and easy way for you to encrypt text in SQL Server, and can be useful for encrypting passwords if you need to be able to decrypt the passwords later.  However, if you do not need to decrypt the passwords, you will be much better off using a hash, which we will discuss in a later post in this series.  So, while using ENCRYPTBYPASSPHRASE to encrypt your passwords may not be the best option, it is still better than doing nothing!