Encrypt Decrypt Password String In SQL Server Using EncryptByPassPhrase And DecryptByPassphrase

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.