SQL Server: How to retrieve actual value of password encrypted using HASHBYTES

Question

insert into Customer(AccountNo,Name,EmailId,MobileNo,[Password],Balance,
                     CustomerKey,OTPPin,CreatedBy,CreatedOn)
values(@AccountNumber,@Name,@EmailId,
       EncryptByPassPhrase(@PassPhrase, CONVERT(nvarchar,@MobileNo)),
       HASHBYTES('SHA1',@Password),@TotalBalance,@CustomerKey,@OTPPin,0,GETDATE())

I am getting inserted value in this form

Now I want the password's actual value. How can I get it?

Answer 1

Since you've used HASHBYTES('SHA1' on your password - you can't straightforward get back its original value.

SHA1 is one-way hash function.

In fact, you don't need that original value in most cases. Typical usage of hased passwords is not to somehow get original value from hash and compare it with password entered by user, but instead apply hash function to the password entered by user and then compare two hash values.

Answer 2

DO NOT USE A SINGLE ROUND OF HASHING TO STORE PASSWORDS

---

DO NOT USE UNSALTED HASHES TO STORE PASSWORDS

---

DO NOT USE SHA1 TO STORE PASSWORDS

---

Go read Thomas Pornin's canonical answer to How to securely hash passwords, right now.

Now, choose PBKDF2, BCrypt, or SCrypt.

---

Hashing Rules

• Do your hashing where you control it - on the Web, that's your server side application (whether it's PHP, ASP.NET, etc.).

  • NOT in SQL, if at all possible - your app server will be much faster, tends to be much more scalable, and you need a high iteration count that SHOULD be so high as to use up most of your application server's CPU under your peak number of logins per second - remember, 8 cores can run 8 hashes in the same time 8 cores can run 1 hash.

• Securely encrypt the connection between the application server and SQL server.

  • You can see many of the basics in MSDN article Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)

  • NOTE - after importing the certificate, you need to right-click on it, go to All Tasks, manage the private keys, and then add READ (and only read) permission for the SQL Server Service Account and the SQL Server Agent Service Account

• If you absolutely must hash in SQL Server, I have T-SQL PBKDF2 code in my Github repository with a public domain license, including a few optimizations and a set of test vectors.

  • And get on your application developers to fix the app.

Here is some information on how to store PBKDF2 (or BCrypt, or SCrypt) in a database, if that would help as well.

---

To compare a password against a hashed password in general

Once you correct your password storage, then the correct way to validate a user's password is

1. Retrieve the hashed password, salt and iteration count/work factor from the database
2. Hash whatever the user entered as a possible password using the salt and iteration count/work factor just retrieved
3. If the fresh hash is the same as the previous has, the password was the same.

---

To get the passwords actual value from the hash:

If you really want to get the password's actual value, which you SHOULD NOT except for weak password auditing, use oclHashcat or, if you can't use oclHashcat, try John the Ripper.

This will do both Markov and Mask attacks (advanced brute force style attacks), as well as statistical attacks and rules-based dictionary attacks to make billions of password guesses per second against a single round of SHA1, or with 8 modern GPU's as of early 2016

• That's 9.7E16 (2^56) single iteration SHA1 guesses every 30 days

  • and against an unsalted hash, it can do that many against every password in the database simultaneously.