A one way encryption approach to storing passwords in SQL Server

Storing passwords in the database in clear text isn’t really a great idea for a number of reasons. Remember a password is used to establish identity. It’s not a hoop to jump through before you can access data, it says "I know the password for this account thus authenticating that I am the account’s owner." There are plenty of two encryption techniques (combination of public and private key) to encrypt and decrypt passwords on the fly. However, I am more much for of a fan of the one way approach.

In short a one way encryption algorithm is designed to encrypt data in such a way that it cannot be decrypted. So it’s not a good solution for data you need to retrieve.

So, a couple of caveats to this approach. If a user cannot remember their password, it cannot be retrieved from the database. The better approach in my humble opinion is to generate a new random password and send that to the user. Then once they have logged in successfully, allow them a facility to change it to something more memorable (and inherently more guessable .. but that’s another story .. grin).

OK enough waffle, onto the approach:

There are plenty of one way algorithms in existence but the one I’m going to use is SHA1. Yes there are stronger algorithms but the principal is the same in all cases so it’s fine for our example.

SHA1 (and many other one way algorithms) take a variable length string and convert it to a fixed length cipher. The cipher that SHA1 produces is 160bit (or 20 bytes) in length, regardless of the input string. So to generate and encrypt a password we do the following.

  1. Generate a random password of any length.
  2. Generate a cipher with SHA1.
  3. Store that byte array in a SQL Server field of type varbinary(20)

Let’s look at some code:

// GetRandomPassword() just creates a random password
// Code not included in this article
string username = "Fred";
string password = GetRandomPassword();
SHA1CryptoServiceProvider prov = new SHA1CryptoServiceProvider();
byte[] passwordCipher = prov.ComputeHash(Encoding.UTF8.GetBytes(password));

// And lets use LINQ to store the password
// targeting a table called SiteUser with a username and password field
myDataContext db = new myDataContext();
SiteUser user = new SiteUser();
user.Username = username;
user.Password = passwordCipher;

Ok, so that’s pretty straight forward. We’re just creating a new SHA1CryptoServiceProvider and using it’s ComputeHash function to generate a cipher. Finally we store it away.

So how do we check when a user logs in that they have entered the correct password. We can’t retrieve the password from the database to compare it as that is the very nature of a one way encryption strategy .. it’s one way!

What we need to do is encrypt the password that the user provides and then compare the ciphers. To make life a bit easier, we won’t compare the byte arrays. We will convert each byte to a character and then compare the resulting 20 character string.

Onto the code. Please note I am omitting code like checking to see if the username exists before even bothering to compare the password etc .. it’s just a demo.

string suppliedUsername = "Fred";
string suppliedPassword = "password":

// first we encrypt the supplied password in exactly the 
// same way as we did before, but we take an extra 
// step of converting the byte array to a string
SHA1CryptoServiceProvider prov = new SHA1CryptoServiceProvider();
byte[] suppliedPasswordCypher = 
string suppliedPasswordHash = Convert.ToBase64String(passwordPasswordCypher);

// next we retrieve the data from SQL Server
myDataContext db = new myDataContext();
SiteUser user = db.Users.Single(u => u.Username == suppliedUsername);

// and then convert that retrieved 160bit cipher to a 20 character string
string storedPasswordHash = Convert.ToBase64String(user.Password.ToArray());

// and finally compare
if (storedPasswordHash == suppliedPasswordHash) {
    // user is who they say they are
} else {
    // user is politely told to sod off

There you go. Not that hard and of course this could be wrapped up into helper methods to encapsulate this approach. Enjoy.


One thought on “A one way encryption approach to storing passwords in SQL Server

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