SQL Server‎ > ‎

Using the EncryptByPassPhrase Function in SQL Server

Step 1 - Encrypt a field
Select ENCRYPTBYPASSPHRASE('mickey mouse', cast(YourField as nvarchar(100))) as YourFieldEncrypted  from table
NOTE1:    If the field you are encrypting is a number field, you must cast it as a nvarchar as I did here.
NOTE2:    If you case your field as a nvarchar, make sure you specify field size - "nvarchar(100)" not just nvarchar.
This will output your field as a varbinary field with a length of 8000.
Step 2 - To decrypt your field
Select convert(nvarchar(100),DECRYPTBYPASSPHRASE('mickey mouse',YourFieldEncrypted)) as YourFieldDecrypted from table
NOTE3:   the "convert(nvarchar(100)" is necessary to convert your field from varbinary(8000) back to its original form.
Step 4 -  If you need to convert your encrypted field into a text field rather than a varbinaryfield:
Select convert(varchar(max), YourFieldEncrypted,1) as YourFieldEncryptedConvertedToVarchar from table
Step 5 -  If you need to read your text field created in step 4 and decrypt it:
Select convert(nvarchar(100),DECRYPTBYPASSPHRASE('mickey mouse',convert(varbinary(max),YourFieldEncryptedConvertedToVarchar,1)))