Encrypt and Decrypt text in SQL server 2008
In this article I describe how to Encrypt and Decrypt text in SQL Server.
Introduction
In this article I describe how to Encrypt and Decrypt text in SQL Server. Encryption and decryption string is much easier in SQL Server 2008. There is a way to encrypt a password and then store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted using the DECRYPTBYPASSPHRASE function.
EncryptByPassPhrase:
This function works on the Triple DES algorithm to encrypt the text. EncryptByPassPhrase
Syntax
ENCRYPTBYPASSPHRASE ('PASSPHRASE','text')
EncryptByPassPhrase has two mandatory arguments: PASSPHRASE (specifies the data string to be used to derive an encryption key) and text (specifies text to be encrypted). Text should be of VarBinary type.
Example
Creation of Table
create table login_details
(
uid int,
username varchar(10),
password varbinary(100)
)
Insert data in table
insert into login_details
select 1,'smith',EncryptByPassPhrase('8','ABC') union all
select 2,'jean',EncryptByPassPhrase('8','DEF') union all
select 3,'michael',EncryptByPassPhrase('8','GHI')
Now select table data, password will be in encrypted form.
select * from login_details
Output:
DECRYPTBYPASSPHRASE:
DECRYPTBYPASSPHRASE is used to decrypt the encrypted column. Similarly DECRYPTBYPASSPHRASE takes two two mandatory arguments: PASSPHRASE (generates the decryption key) and text (specifies text to be decrypted).
Example
To decrypt the password field, type following code:
SELECT uid,username,
DECRYPTBYPASSPHRASE ('8',password)as DecryptedPassword
FROM login_details
Output
But this output is in varbinary format. So use convert function to convert the varbinary in varchar format. Now replace the preceding code by:
SELECT uid,username,
CONVERT(varchar(50),DECRYPTBYPASSPHRASE ('8',password)) as Password
FROM login_details
Output:
