Decrypting cypherText encrypted using EncryptByPassPhrase funtion

We saw in the previous post how to encrypt using the EncryptByPassPhrase() function.

We will use the same encrypted text and decrypt it using the same PassPhrase used to create it.

We use the reverse function DecryptByPassPhrase() which takes two arguments, the first is the PassPhrase and the second is the encrypted value (cyphertext). The PassPhrase generates the key for decryption.

MSSS2017\ENCRPTPSWD_04.pn

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.