Encrypt and Decrypt a Password using EncryptByPassPhrase and DecryptByPassPhrase

Posted by in Sql Server category on for Beginner level | Views : 155939 red flag
Rating: 4.6 out of 5  
 5 vote(s)

This Article explains you how to Encrypt and Decrypt a text


This Article explains you how to Encrypt and Decrypt a text.you can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase functionEncrypted column can be decrypted by using DECRYPTBYPASSPHRASE function.



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 'text' data type shoulb be VarBinary.


Creating a 'login_details' 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





Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE.


DECRYPTBYPASSPHRASE function takes two arguments one is 'PASSPHRASE'and text or column_name.


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


 In the above result the password still in VarBinary.So we have to Convert the VarBianry in Varchar by using Convert function as follows.

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

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)