MS SQLServer 2005 (Encryption with Passphrase)

This is a quick tip for using Encryption with Passphrase function "encryptbypassphrase" in MS SQLServer database

Introduction:
encryptbypassphrase function help to encrypt sensitive information in a table.


Example:
1) create a table:
create table employees
(ssn varbinary(8000),
[name] varchar(50),
dob datetime)
GO

2) Insert data into employees table using the encryptbypassphrase function:
insert into employees (ssn, name, dob)
values (encryptbypassphrase('passphrase', '999999999'), 'Jon', '01/01/1900')

3) Testing the encrypted Data:
When you run select * from employees table, it will return the inserted row as binary something similar to the following:
select *
from
employees;
Resultset ==>
0x10100000000c0, Jon, 01/01/1900

However, to view the encrypted information use the DecryptByPassPhrase function to dycrypt the values stored in the field. Do not forget to use the convert function to convert the binary to a character base.

select convert(varchar(9), DecryptByPassPhrase('passphrase', SSN), DOB
from
employees;
Resultset ==>
999999999, Jon, 01/01/1900


In case you provided the wrong passphrase, the select statement will return null for the encrypted field
select convert(varchar(9), DecryptByPassPhrase('Wrongpassphrase', SSN), DOB
from
employees;
Resultset ==>
Null, Jon, 01/01/1900