Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Thursday, 22 June 2017

Encrypt Decrypt Password String In SQL Server Using EncryptByPassPhrase And DecryptByPassphrase

 SQL     No comments   


Introduction
In this article we will learn How to encrypt or encode a string using EncryptByPassPhrase function and how to decrypt or decode string using DecryptByPassphrase sql function. 
EncryptByPassPhrase and DecryptByPassphrase  function in Sql server with example.

Previous Updates

In previous articles we have learnt  Base64 Encoding And Decoding In SQL Server. Transform multiple rows into one comma separated string column. .AngularJS basics, What is Cursor in sql and use of cursor. 

EncryptByPassPhrase 

EncryptByPassPhrase uses DES algorithm to convert the data. This sql function encrypts the string or data into varbinary format .



EncryptByPassPhrase('Passphrase', 'text')

In this EncryptByPassPhrase function syntax two mandatory arguments passphrase and text. Passphrase is used as a Key for encryption and  same key will be used at the time of decryption. Text should be the value which needs to be encrypted.

DecryptByPassphrase  

This sql function decrypts the varbinary formatted string to our actual string value. 


DecryptByPassphrase('Passphrase', 'text')


In this DecryptByPassPhrase function syntax two mandatory arguments passphrase and text. Passphrase is  a Key for decryption which is the same as encryption key. Text should be the value which needs to be decrypted.

Example 

In this example i used a temp table to display the use of both function.

DECLARE @tempDetails table(ID INT, Name VARCHAR(50),[Password] VARBINARY(100))

INSERT INTO @tempDetails(ID,Name, [Password]) values(1,'Mike',EncryptByPassPhrase('MyKey','ABC_Test'))

INSERT INTO @tempDetails(ID,Name, [Password]) values(1,'Nicks',EncryptByPassPhrase('MyKey','Nicks_Test'))

INSERT INTO @tempDetails(ID,Name, [Password]) values(1,'Mark',EncryptByPassPhrase('MyKey','Mark_Test'))


select * from @tempDetails

-- Decrypting varbinary column password using DECRYPTBYPASSPHRASE

SELECT ID,Name,
CONVERT(VARCHAR(50),DecryptByPassphrase ('MyKey',[Password]))as DecryptedPassword
FROM @tempDetails
Here my Passphrase  value or key value same for encryption and decryption which is 'MyKey'. If you will not use the same key or Passphrase for decryption which is used in encryption then your decryption code will not work. 

Here is the Result Window Image how it looks like.




  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write