Encrypt and Decrypt Column Value In SQL Server Table

In this blog 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', '[email protected]', EncryptByPassPhrase('RS','india'),'Developer','Noida','Uttar Pradesh','India',5)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country,Experience)
VALUES('Shambhu Sharma', '[email protected]', EncryptByPassPhrase('RS','Delhi'),'Manager','Delhi','Delhi','India',10)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Manu Khanna', '[email protected]', EncryptByPassPhrase('RS','Rohini'),'Consultant','Delhi','Delhi','India',12)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country,Experience)
VALUES('Abhishek Nigam', '[email protected]', EncryptByPassPhrase('RS','Lucknow'),'Technical Consultant','Hawaii','Honolulu','USA',9)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Shraddha Gaur', '[email protected]', EncryptByPassPhrase('RS','DELHI'),'Lead Tester','Noida','Uttar Pradesh','India',4)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Shweta Kashyap', '[email protected]', EncryptByPassPhrase('RS','Dehradun'),'Developer','Dehradun','Uttarakhand','India',5)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Yogesh Gupta', '[email protected]', EncryptByPassPhrase('RS','Khanpur'),'Consultant','Atlanta','Georgia','USA',10)

INSERT INTO EMPLOYEE (Name,Email,[Password],Designation,City,[State],Country, Experience)
VALUES('Rakesh Dixit', '[email protected]', 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.