Encrypt and Decrypt Column Value In SQL Server Table

In this article I am going to show how we can save our data in SQL Server Table in Encrypted form and how we can Decrypt our column value.

Below is my SQL Server Table in design mode:

Image 1.

Select Records:

Image 2.

Now we will insert some records in this table. Here you will notice in Password column I will insert value in Encrypted form:

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Rahul Saxena', 'rahulsaxena@live.com', EncryptByPassPhrase('RS','india'),'Developer','Noida','Uttar Pradesh','India',5)
 

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country,Experience)
VALUES('Shambhu Sharma', 'shambhu@shambhu.com', EncryptByPassPhrase('RS','Delhi'),'Manager','Delhi','Delhi','India',10)
 
INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Manu Khanna', 'manu@manu.com', EncryptByPassPhrase('RS','Rohini'),'Consultant','Delhi','Delhi','India',12)
 
INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country,Experience)
VALUES('Abhishek Nigam', 'abhishek@abhishek.com', EncryptByPassPhrase('RS','Lucknow'),'Technical Consultant','Hawaii','Honolulu','USA',9)
 
INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Shraddha Gaur', 'shraddha@shraddha.com', EncryptByPassPhrase('RS','DELHI'),'Lead Tester','Noida','Uttar Pradesh','India',4)
 
INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Shweta Kashyap', 'shweta@shweta.com', EncryptByPassPhrase('RS','Dehradun'),'Developer','Dehradun','Uttarakhand','India',5)
 
INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Yogesh Gupta', 'yogesh@yogesh.com', EncryptByPassPhrase('RS','Khanpur'),'Consultant','Atlanta','Georgia','USA',10)
 
INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Rakesh Dixit', 'rakesh@rakesh.com', EncryptByPassPhrase('RS','Jaipur'),'Consultant','Jaipur','Rajasthan','India',10)

Image 3.

Now see your records in your table:

Image 4.

Now How we can Decrypt Our Column Value while fetching:

SELECT EMP_ID,NAME,EMAIL,CONVERT(VARCHAR(50),DECRYPTBYPASSPHRASE ('RS',PASSWORD))AS DECRYPTEDPASSWORD,
DESIGNATION,CITY,STATE,COUNTRY,EXPERIENCE FROM EMPLOYEE

Image 5.