EncryptByPassPhrase

EncryptByPassPhrase uses the Triple DES algorithm to encrypt the text passed in.

Syntax

ENCRYPTBYPASSPHRASE ('PASSPHRASE', column_name or 'text')

In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key, and the column_name data type should be VarBinary.

Creating a table

create table login_details(uid integer,username varchar(10),password varbinary(100))
insert into login_details(uid,username,password) values(1,'smith',EncryptByPassPhrase('12','XXX'))
insert into login_details(uid,username,password) values(2,'kennal',EncryptByPassPhrase('12','YYY'))
insert into login_details(uid,username,password) values(3,'staurt',EncryptByPassPhrase('12','ZZZ'))

  select * from login_details

Image of login

DECRYPTBYPASSPHRASE

The encrypted column can be decrypted by using DECRYPTBYPASSPHRASE.

DECRYPTBYPASSPHRASE function takes two arguments one is symmetric key and column_name.

select uid, username, DECRYPTBYPASSPHRASE ('12',password) as Password from login_details

image of decrypt......

select uid, username, convert(varchar(10), DECRYPTBYPASSPHRASE ('12', password)) from login_details

image of Convert using decrypt