Strong Password Hashing with SQL Server

While SQL Server security features continue to improve, hashing in SQL Server using native functions is simple, but not necessarily up to speed on the latest security specifications. The good news however, is that using extended stored procedures allows database developers to take advantage of the strength of .NET using the System.Security.Cryptography namespace.

I started to evaluate Hashing capabilities within SQL 2005 and 2008 and as it turns out, hashing is extremely simple using the HASHBYTES function. This function takes two parameters: the hashing algorithm (sha1, md5…) and the input parameter.

For example, the following code hashes a password. Pay attention to the Encoding chosen; the type of the variable dictates which encoding will be used (varchar and nvarchar).

DECLARE @password1 varchar(100)-- UTF8 Encoding DECLARE @password2
    nvarchar(100)-- Unicode SET @password1 = 's3cret' SET @password2 =
        's3cret' SELECT HASHBYTES('sha1', @password1) SELECT
    HASHBYTES('sha1', @password2)

Output:

0xFEF341F85D87439E7D91A2D465B9871EF66B5E98 
0xC06DCADF544BC3D6ECE7C64F485D2846E7A93F55

Unfortunately, SQL Server does not support any of the stronger hashing mechanisms, such as SHA256. In addition, storing passwords by simply hashing them is not considered sufficient as they are vulnerable to dictionary attacks; indeed, hashing a given input always yields the same output. To provide stronger hashing, one would need to obtain a cryptographically strong random byte array, known as a vector (or a salt value). To use stronger hashing algorithms and create vectors, we need to use the .NET framework through an extended function.

Using .NET Hashing

The objective of our .NET code is to generate a single output that is never the same (avoiding the dictionary vulnerability) and uses SHA256 as the hashing mechanism. The following shows how to use a .NET extended function that takes a password as an input and returns a byte array to SQL Server that contains both the hash and the vector.

Note that your SQL Server database should allow .NET execution. Here is the SQL statement used to enabled CLR on SQL Server:

sp_configure ‘clr enabled’ , ‘1’
go
reconfigure

The following T-SQL uses the strong .NET hashing functions found later in this post. The output is a byte array that can be stored as binary(48).

DECLARE hash binary(48) SET hash =
    (SELECT dbo.pyn_encryption_hashPassword('s3cret'))SELECT dbo
        .pyn_encryption_verifyPassword('wrongpwd', hash)-- returns
    0 SELECT dbo.pyn_encryption_verifyPassword('s3cret', hash)-- returns 1

The above code makes it simple to offer a strong password hashing function that combines SHA256 and a vector. Since the vector is embedded in the password, there is no need to store the vector in a separate column.

Here is the .NET code that creates and verifies a password using a strong hashing method. The methods below use UTF8 encoding. To use Unicode, replace the method calls GetNonUnicodeBytes() to GetUnicodeBytes().

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {
  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlBytes pyn_encryption_hashPassword(SqlString password) {
    // Create a strong vector
    byte[] vector = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
    System.Security.Cryptography.RandomNumberGenerator.Create().GetNonZeroBytes(
        vector);

    // Get the password bytes
    byte[] pwdBytes = password.GetNonUnicodeBytes();

    // Add the vector bytes to the password bytes and hash them both at the same
    // time
    System.Security.Cryptography.SHA256Managed sha256 =
        new System.Security.Cryptography.SHA256Managed();
    byte[] outputBytes = sha256.ComputeHash(AddBytes(pwdBytes, vector));

    // Return the resulting hash, and append the vector again to it so it can
    // extracted later
    return new SqlBytes(AddBytes(outputBytes, vector));
  }

  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlBoolean pyn_encryption_verifyPassword(SqlString password,
                                                         SqlBytes hash) {
    byte[] vector = new byte[16];
    byte[] pwdAndHash = new byte[32];

    // Split the hash and vector into separate variables
    Array.Copy(hash.Value, 32, vector, 0, 16);
    Array.Copy(hash.Value, 0, pwdAndHash, 0, 32);

    // Get the password bytes that will be tested against the hash
    byte[] pwdBytes = password.GetNonUnicodeBytes();

    // Compute a hash using the password provided, and the vector extracted from
    // the hash
    System.Security.Cryptography.SHA256Managed sha256 =
        new System.Security.Cryptography.SHA256Managed();
    byte[] testHash = sha256.ComputeHash(AddBytes(pwdBytes, vector));

    // Compare hash values to determine if the password provided matches
    return new SqlBoolean(BitConverter.ToString(pwdAndHash) ==
                          BitConverter.ToString(testHash));
  }

  private static byte[] AddBytes(byte[] array1, byte[] array2) {
    // Add two byte arrays
    byte[] array3 = new byte[array1.Length + array2.Length];
    Array.Copy(array1, array3, array1.Length);
    Array.Copy(array2, 0, array3, array1.Length, array2.Length);
    return array3;
  }
};

Conclusion

While SQL Server hashing capabilities offer good support for simple hashing needs, extending SQL Server with .NET can provide a significant security advantage when it comes to encryption. Since the code above does not access any local resources on the database server, it can run in Safe permission mode.

Using SQL Server Hashing

Print | posted @ Saturday, September 19, 2009 11:39 AM