Encrypt and Decrypt text in SQL server 2008

In this article I describe how to Encrypt and Decrypt text in SQL Server.
  • 3942

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:

encryptpassword-in-sql.jpg

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

decryptedPassword-in-varbinary-sql.jpg

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:

decryptedPassword-in-varchar-sql.jpg

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.