How to Encrypt and Decrypt a Password


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



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


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

Similar Articles