Encrypt Password in SQL

Hello folks,

Today we will see how we can encrypt password before saving into database table.

There are many ways to implement this feature like defining Asymmetric keys and using that into table/sp or use some hash algo to encrypt password etc but we will add some complexity into encryption stuff.

Objective:  Our goal is to generate very complex and encrypted password which can’t be hacked (not even by database guys using sql injection etc).

Solution:

Step 1: Create a table that will hold username, password and other details. See below sample.

employee-table

Employee table

Step 2:  To add complexity into encryption we will generate a salt key (which will be unique in time and space), then this salt key will be added with user password to add complexity and then finally we will encrypt this encrypted key which will be very much secure and hackers can’t even guess your password.

Step 3: Create a stored procedure into your database and add below line of code to generate a strong salt key.

DECLARE @salt UNIQUEIDENTIFIER=NEWID()

SELECT @salt = '8846E7C2-1BAE-45C6-987F-684787F826BD'

This is s 64 bit code and you can see above code is complex enough to guess and regenerate because each time and each person will get different code than earlier one. The code will never ever be repeated.

Step 4:  Now we will use hashbyte algo which is an inbuilt algorithm to  generate & encrypt  something in sql.

We are now combining the user password with unique code which will be unique and then we are applying the encryption. This guarantee that each and every line in table will be unique and hard enough to guess.

HASHBYTES('SHA2_512', @Password+CAST(@salt AS NVARCHAR(36))),

Preview:

You can see the Passwordhash column in below email from table and try to guess what would be the password :).

encryption

Encrypted password (Passwordhash)

Hope this post makes your like easy and you might got to learn something new.

Always looking for your recommendation and feedback..

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s