Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Encryptbypassphrase and Decryptbypassphrase Functions with Example

Apr 20, 2015
Introduction:

Here I will explain what is
 encryptbypassphrase and decryptbypassphrase functions in sql server. By using these functions we can encrypt or decrypt string or text or password column in sql server. EncryptByPassPhrase function in sql server will encrypt the data and store it in varbinary format and DecryptByPassphrase function will decrypt varbinary encrypted string and show the result in decrypted format.
Description:                           
In previous articles I explained SQL injection attacks with example, SQL Server interview question and answers, joins in sql server, function example in sql server 2008, Primary key constraint in sql server, foreign key constraint in sql server, cursor example in sql server and many articles relating to SQL server. Now I will explain use of encryptbypassphrase and decryptbypassphrase functions with example in sql server.

EncryptByPassPhrase:

This function will use DES algorithm to encrypt the data and store it in varbinary format.

Syntax of EncryptByPassPharse declaration


ENCRYPTBYPASSPHRASE ('PASSPHRASE','text')

If you observe above syntax EncryptByPassPhrase has two mandatory arguments: PASSPHRASE (specifies the data string that is used to derive an encryption key) and text (specifies text to be encrypted).

DecryptByPassphrase:

DecryptByPassphrase is used to decrypt the encrypted column.

Syntax of DecryptByPassphrase declaration


DecryptByPassphrase ('PASSPHRASE','text')

If you observe above syntax DecryptByPassphrase has two mandatory arguments: PASSPHRASE (this string is used to derive decryption key this string should be same as encrptbypasspharse 'PASSPHRASE' string) and text (specifies text to be decrypted).

To encrypt and decrypt string with example we need to write the query like as shown below


-- creating temp table and inserting encrypted password using EncryptByPassPhrase
declare @userdetails table(userid int, username varchar(50),password varbinary(100))
insert into @userdetails(userid,username,password) values(1,'suresh',EncryptByPassPhrase('aspdotnetsuresh','dasari'))
insert into @userdetails(userid,username,password) values(2,'rohini',EncryptByPassPhrase('aspdotnetsuresh','alavala'))
insert into @userdetails(userid,username,password) values(3,'madhavsai',EncryptByPassPhrase('aspdotnetsuresh','yemineni'))

select * from @userdetails

-- Converting and Decrypting varbinary column password using DECRYPTBYPASSPHRASE
SELECT userid,username,
CONVERT(varchar(50),DecryptByPassphrase ('aspdotnetsuresh',password))as DecryptedPassword
FROM @userdetails

If you observe above query in EncryptByPassPhrase and DecryptByPassphrase I am using same string name 'aspdotnetsuresh' to generate key for encryption and decryption otherwise it will not convert the string correctly. Now execute this query and check results that will be like as shown below

Output


SQL Server Encryptbypassphrase and Decryptbypassphrase Functions with Example


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

1 comments :

Arkadeep De said...

its an useful one..

Give your Valuable Comments

Other Related Posts

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.