Sunday, August 6, 2017

Encrypting email password in SQL Server

There is a simple but not most secure way of encrypting a password in SQL Server. It uses the T-SQL function EncryptByPassPhrase().

This function takes two arguments: PassPhrase and ClearText. You provide both the PassPhrase and the ClearText(this is your email password that needs to be encrypted using this function). The PassPhrase generates a TripleDes algorithm with a 128 Key bit length.

ClearText can be a variable of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar


PassPhrase can be of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar


The following code uses the following variables:
PassPhrase -> "Happy birthday to you"
ClearText->   "$#MyPassword#$"
---------
CREATE TABLE ENCRPTPSWD (EncryptedText varbinary(200))
go

declare @pswrd nvarchar(15)
set @pswrd = '$#MyPassword#$'--ClearText
INSERT INTO ENCRPTPSWD (EncryptedText)
values (EncryptByPassPhrase('Happy birthday to you', @pswrd))
go


The column ENCRPTPSWD contains the result of encrypting and is therefore of type varbinary. The phrase to de-encrypt is the phrase 'Happy birth day to you'.

Now if we access that column we see the encrypted value as shown.