How do you decrypt encrypted cypher?

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

How do I keep my email addresses and passwords encrypted using SQL Server?

You could keep your email addresses and passwords in SQL Server database table. You could get a free copy of your SQL Server from a Microsoft download site.

While the email addresses can be kept un-encrypted, you may want to keep your passwords in an encrypted column of a table. If want to keep everything encrypted you can do it also.

With this table set up correctly you may need to remember just one phrase which can be of your choosing.

Here are steps to create such a table:

1. Create a table in one of your existing databases. I am creating this table in a database called 'Aloha' which I use for teaching.

Here is the statement to create the table:

USE Aloha
Go
Create Table Encrpt(
emailID int Primary Key not null,
emailAdd nvarchar(25) null,
emailPswd varbinary(250) null
)

This creates the table ENCRPT.

The email password (emailPswd) is stored in a special column(varbinary data type) as shown above.

Now keep a list of emails and the passwords ready to populate the table.

Populate the table you created by inserting values using the following code:

INSERT INTO ENCRPT(emailID, EmailAdd, emailPswd)
values (1, 'hodentek@live.com', EncryptByPassPhrase('Happy birthday to you','xyzpo')),
(2, 'mysorian@gmail.com',EncryptByPassPhrase('Happy birthday to you ','ZZZ12$' )),
(3, 'htek@mysorian.com',EncryptByPassPhrase('Happy birthday to you','$$$11X' ))

I have inserted only three values but you could add more. While the email addresses shown are my real addresses, the passwords are not.

Now when you run a SELECT statement to display all the columns, this is what you see.

Here is a screen shot of all code:

I have used the same PassPhrase for all data, you could have different on, the onus of remembering phrases is on you.

The fourth row was filled using a different PassPhrase ('Happy Anniversary).

Read more here:

https://hodentekmsss.blogspot.com/2017/08/encrypting-email-password-in-sql-server.html