Saturday, August 12, 2017

Encrypted email passwords in 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

No comments:

DMCA.com Protection Status