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.

OPTIONAL:

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)))