Home » Blog » How to Access Passphrase-Encrypted Data

How to Access Passphrase-Encrypted Data

2014-08-11 - Cryptography, General, Security

On Friday we used the ENCRYPTBYPASSPHRASE function to encrypt the data in a column. Usually if you encrypt data, you need a way to later decrypt it too. That is where the DECRYPTBYPASSPHRASE function comes in.

A DECRYPTBYPASSPHRASE Example

If you followed the example on Friday, you already have the table we are going to work with today. If not, you can create it with the following script:

[sql] CREATE TABLE dbo.encrypted
(
id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
data VARBINARY(8000)
);
GO
INSERT INTO dbo.encrypted(data)
VALUES(ENCRYPTBYPASSPHRASE('**********','Hello World!'));
[/sql]

To use the DECRYPTBYPASSPHRASE function you need to supply the passphrase itself and the encrypted value. Selecting from our table therefore would look like this:

[sql] SELECT id,DECRYPTBYPASSPHRASE('**********',data)
FROM dbo.encrypted;
[/sql]

That does decrypt the value correctly; however, the result is probably not what you expected:

Remember, ENCRYPTBYPASSPHRASE returns the encrypted value as a VARBINARY(8000) data type. That data type, other than for example SQL_VARIANT does not carry any information about the originating data type. Therefore, the DECRYPTBYPASSPHRASE also returns a VARBINARY(8000) value.

To get that value back into the originating data type, you just have to cast it:

[sql] SELECT id,CAST(DECRYPTBYPASSPHRASE('**********',data) AS VARCHAR(8000))
FROM dbo.encrypted;
[/sql]

With that, the result is readable again:

Always Use the Correct Password

The DECRYPTBYPASSPHRASE function is a little finicky. It requires the two values to match each other. If you for example use the wrong password, it just returns NULL.

DECRYPTBYPASSPHRASE shows the same behavior, if the provided cipher text was not created by the ENCRYPTBYPASSPHRASE function. That means you can use the returned NULL as a reliable indicator that something went wrong. You will never just get garbage back.

Summary

The pendant to the ENCRYPTBYPASSPHRASE function is the aptly named DECRYPTBYPASSPHRASE function. It decrypts cipher text that was created with the former. However, you yourself are responsible to cast the result back into the appropriate data type, as no information about the data type itself is stored with the encrypted value.