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.
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:
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:
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:
With that, the result is readable again:
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.
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.