Quickly encrypt and decrypt data using EncryptByPassPhrase and DecryptByPassPhrase

February 17, 2023 Derek

So sometimes you just want to quickly encrypt some data in a column on one of your tables. There are many very secure ways to do this but they usually involve certificates and sometimes you just don’t have the time.

Now ideally no one would be able to get to the data because your security setup wouldn’t allow them to read it, but mistakes happen.

So you can quickly encrypt and decrypt some data with the SOL commands

EncryptByPassPhrase (password, Text)

and

DecryptByPassPhrase(password, varbinary)

Examples:

DECLARE @S VARCHAR(MAX) = 'www.slinn.co.uk'
DECLARE @encrypt VARBINARY(MAX)

--Excrypt the data
SELECT
    @encrypt = EncryptByPassPhrase('Derek', @S)
SELECT
    @encrypt

--Decrypt the data
SELECT CONVERT(VARCHAR(MAX), DecryptByPassPhrase('Derek', @encrypt))

Will produce the following output

So this example works fine as we’re using the @encrypt variable but if you put data into a table using a VARBINARY column after the decrypt you also need to convert it back to an NVARCHAR see the example below

--create a working table to store a list of tables from the WorldWideImporters database and the encrypted value of the table name
declare @tables table
(
    num int identity(1, 1)
  , tablename varchar(200)
  , encrypted_data varbinary(max)
)

--insert the table names and the encrypted value
insert @tables
(
    tablename
  , encrypted_data
)
select TABLE_NAME
     , encryptbypassphrase('Derek', TABLE_NAME)
from INFORMATION_SCHEMA.TABLES

--return the top 10 tables and decrypt the data as well, notice the CONVERT statement 
select top 10 
	   num
            , tablename
            , encrypted_data
     , convert(nvarchar, decryptbypassphrase('Derek', encrypted_data)) decrypted_data
from @tables