Encryption with TSQL

SQL Server has a bunch of encryption functionality at its disposal. The EncryptByPassphrase allows us to quickly encrypt data using a password. This function uses the Triple DES algorithm to protect data from prying eyes. To encrypt a section of text we supply a password and the text to the function;

SELECT ENCRYPTBYPASSPHRASE('secret', 'My very secret text');

This returns an encrypted version of our text.

0x01000000409190B7ABDB55FE3724888C854ADBF33DA0BDF6F8AD0DCB0DC76746A2122D515B96DA4DCEF14FFA

Of course there is also a DecryptByPassphrase we can use to decrypt the data.

SELECT DECRYPTBYPASSPHRASE('secret', 0x010000006CF61B39AAF0030FDCED9942BEEEE946CB4EDB0AF2C0CDBCFB0B0882A7B32B4975974D20C8A3C82D);

0x4D792076657279207365637265742074657874

What? That’s not our original text. What’s going on here? This is our original text it’s just in VARBINARY format. Cast it to a char data type to make it human readable;

SELECT CONVERT(VARCHAR(100), DECRYPTBYPASSPHRASE('secret', 0x010000006CF61B39AAF0030FDCED9942BEEEE946CB4EDB0AF2C0CDBCFB0B0882A7B32B4975974D20C8A3C82D));