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.


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

SELECT DECRYPTBYPASSPHRASE('secret', 0x010000006CF61B39AAF0030FDCED9942BEEEE946CB4EDB0AF2C0CDBCFB0B0882A7B32B4975974D20C8A3C82D);

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;


tsql encryption

Leave a Reply