Home » Blog » How to Encrypt a Column with a Passphrase

How to Encrypt a Column with a Passphrase

2014-08-08 - Cryptography, General, Security

Sometimes it is appropriate to go with the simplest solution, even if it is not the most secure. SQL Server provides a sophisticated key management solution. But as with most powerful tools, its use is not necessarily trivial. In addition, just for those cases where a full-fledged key-managed encryption solution seems overkill, SQL Server provides a simple encryption solution that is based on a passphrase (a long password) instead of keys.

The ENCRYPTBYPASSPHRASE Function

The ENCRYPTBYPASSPHRASE function allows us to encrypt any string or varbinary value, up to 7975 bytes in length. It takes two parameters, a @passphrase and the @cleartext and returns a VARBINARY(8000) value that contains the encrypted values.

There are a few problems with this function that you need to be aware of. The passphrase is run through a key derivation function to generate a 128-bit key. Which key derivation function is used is not published, so we cannot tell how secure it actually is. The key is then used to encrypt the provided data using the Triple-DES algorithm. How that is done exactly is also unclear, as the Triple DES algorithm natively can only handle either 112-bit or 168-bit keys. Finally, the NIST ran a competition to create a new standard encryption algorithm because DES and its derivations including Triple DES were considered not secure enough anymore. That competition resulted in AES being announced and recommended as the new encryption standard. That would not be too bad, if it happened yesterday. However, the new AES algorithm was announced at the end of 2001. Since then computing power has increased by a >factor of 128 and that does not even calculate in the abilities that cloud computing now provides for little money.

All that does not mean that ENCRYPTBYPASSPHRASE is not useful; it is still based on a fairly strong encryption method. However, I would not rely on it to encrypt my customer's credit card numbers or PHI anymore.

So, let us look at an example. I have the following table:

[sql] CREATE TABLE dbo.encrypted
(
id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
data VARBINARY(8000)
);
[/sql]

In real life, you probably would have additional columns, but for simplicity, I just went with two, the primary key and the data column. The id column will stay unencrypted. (There is never a reason to encrypt a surrogate key column and if you do, the result will most likely be disastrous.) The data column is of type VARBINARY(8000), which matches the output of the ENCRYPTBYPASSPHRASE function.

To insert data into this column you can directly call the ENCRYPTBYPASSPHRASE function in the INSERT statement:

[sql] INSERT INTO dbo.encrypted(data)
VALUES(ENCRYPTBYPASSPHRASE('**********','Hello World!'));
[/sql]

In case that is not immediately obvious, the first parameter is the passphrase and the second provides the clear text value.

The result of the encryption can be seen with a straight forward SELECT:

The twelve characters of clear text were transformed into 36 bytes of gobbledygook (that is a technical term).

ENCRYPTBYPASSPHRASE only takes CHAR and BINARY data types as input (including their N- and VAR- variants). Any other data type like a DATE, for example to encrypt a birthdate, you have to explicitly convert to a binary value first.

Summary

ENCRYPTBYPASSPHRASE provides a quick and simple way to encrypt data columns in SQL Server. However, its use of the Triple DES algorithm as well as a few documentation gaps make it inadequate for important data like credit card information or social security numbers.