A hash function maps a data string of variable length to a data string of a fixed length. The fixed-length data string is called a hash value. A hash function is one-way, meaning that you cannot use a hash function to recreate the original data string from its hash value. A secure hashing algorithm will produce a unique value for any given data string. A collision occurs when a hash function produces the same hash value for two different data strings. Collisions raise the possiblity that an attacker could compromise data and escape detection. A broken hash function is one where a method for producing collisions is known to exist.

Hash values can be used as checksums to confirm that data has not been corrupted, as indexes in hash tables to accelerate table-lookup or data comparison tasks, and in cryptography to confirm that a message has not been tampered with. All of these uses depend upon the ability to independenty generate a matching hash value from the original data string using the same algorithm that was used to generate the original hash value.

The Secure Hash Algorithm (SHA) is a family of hash functions published by the National Institute of Standards and Technology (NIST). The SHA-2 family of NIST hash algorithms includes SHA-256 and SHA-512. There are also truncated versions of each algorithm known as SHA-224 and SHA-384. The SHA-2 hash functions were designed by the U.S. National Security Agency (NSA) and published in 2005 when security flaws were identified in the SHA-1 hash functions.

Overview

This article will demonstrate how to:

1. Compile a strongly-named CLR assembly called SHA-256Hash using Microsoft Visual C# 2010 Express
2. Deploy the SHA-256Hash assembly to SQL Server
3. Create a SQL Server function named sha256Hash that references the public method sha256HashProc in the SHA256Hash CLR assembly
4. Use the sha256Hash SQL Server function to generate a hash value for a data string stored in a table.

SHA-256 CLR Assembly Tutorial

The following C# code compiles to a console application that accepts a string and returns a 64-character SHA-256 hash of that string.

using System;
using System.Security.Cryptography;
using System.Text;
public class SHA256HashCode
{
    sealed class SHA256HashCodeConsole
    {
        private static void Main()
        {
            Console.Write("Enter String to be Hashed: ");
            string testString = Console.ReadLine();
            
            Console.WriteLine("");
            
            string hashString = sha256HashProc(testString);
            Console.WriteLine("Hash String: " + hashString);
            
            Console.ReadLine();
        }
    }
    public static string sha256HashProc(string inputString)
    {
        SHA256Managed crypt = new SHA256Managed();
        string hashString = String.Empty;
        byte[] crypto = crypt.ComputeHash(Encoding.UTF8.GetBytes(inputString), 0, Encoding.UTF8.GetByteCount(inputString));
        foreach (byte bit in crypto)
        {
            hashString += bit.ToString("x2");
        }
        return hashString;
    }
}

NOTE: This same code can be used to generate SHA-384 and SHA-512 hashes by using the "search and replace" edit function to replace the string "256" with the string "384" or the string "512."

To compile and execute the code, create a new project in Microsoft Visual C# 2010 Express (downloadable at http://go.microsoft.com/?linkid=9709939).

Choose the Console Application template, enter the name SHA256Hash, then click the OK button.

A default Program.cs file is created.

Copy and past the C# code at the beginning of this article over the default code.

Click the "Start Debugging" button to execute the project. Upon execution, the application will present a command console.

Enter a string of characters at the prompt and press the Enter key.

The application prints the SHA-256 hash for the input string.

In order to confirm the validity of the hash we just generated, we will use an online SHA-256 hash calculator (https://quickhash.com/) to hash the same input string.

The hash generated by the console application matches the hash generated by the online hash calculator. Feel free to test the console application with more strings and compare the resulting hashes to the output of the online hash calculator.

To deploy our SHA-256 code to SQL Server 2005/2008 as a CLR assembly we need to change the output type from Console Application to Class Library, change the target .NET framework to .NET 3.5 (.NET 4 is supported by SQL Server 2012, but not by earlier versions of SQL Server), and sign the compiled assembly with a strong name.

In order to sign the assembly with a strong name, we need to first generate a strong name key file. To do so we need to open a DOS command line console. We can add this as a tool to the development environment. Click on the Tools menu item from the top menu, then click on the External Tools menu item.

The External Tools dialog box is brought up.

Enter the title Command Prompt, the command cmd.exe and the initial directory c:\, then click the Add button to add the Command Prompt to the Tools menu.

Click the OK button to close the External Tools dialog box.

When you click on the Tools menu you can see that the Command Prompt menu item has been added. Select it to bring up the command console.

The command console opens up in the C:\ root directory.

Create a directory called C:\myKeyFiles\ and move to that directory. Enter the command "sn -k sha256KeyFile.snk" and press Enter.

The Visual Studio Strong Name Utility creates a strong name key file named sha256KeyFile.snk.

You can browse to c:\myKeyFiles\ in Windows Explorer to confirm the existence of the key file.

Before we can compile to an assembly we must save the project by clicking File - Save All.

Chose the name and location to save the project and click the Save button.

Right-click the Properties node and select the Open menu item, or simply double-click the Properties node to bring up the project's Properties dialog box.

Change the output type from Console Application to Class Library in the Application tab of the project properties dialog box.

Click on the target framework drop-down list of the project properties dialog box and select .NET Framework 3.5

The following popup window appears. Click the Yes button.

As the previous popup promised, the SHA256Hash project was closed and reopened,

Right-click the Properties node and select the Open menu item, or simply double-click the Properties node to bring up the project's Properties dialog box once again.

Now click on the Signing tab (highlighted in yellow) in the column of tabs just to the right of the Solution Explorer window.

Click on the Sign the assembly checkbox.

Select the <Browse...> menu item from the Choose a strong name key file drop-down menu.

Browse to the C:\myKeyFiles\ directory and select the sha256KeyFile.snk file that we created earlier.

We now have the project fully configured for building a strongly-named CLR assembly.

Click on the Build top-menu and select the Rebuild Solution menu item.

Now browse to the SHA256 project and drill down to the ..\x86\Debug\ folder where the newly created CLR assembly SHA256Hash.dll resides.

Create a folder named C:\myDLLs\ and copy the SHA256Hash.dll file to it. This will make it easier to deploy the assembly to SQL Server.

Now open SQL Server Managment Studio and create a new database...

...name it TestDB and click the OK button.

Click on the TestDB database to give it the focus, then click on the New Query toolbar item...

...to create a query editor window.

Paste the following code...

USE [TestDB]
GO
Sp_configure 'clr enable',1
GO
RECONFIGURE
GO
CREATE ASSEMBLY SHA256Hash from 'C:\MyDLLs\SHA256Hash.dll' WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[sha256Hash](@STRING [nvarchar](250))
RETURNS [nvarchar](100) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SHA256Hash].[SHA256HashCode].[sha256HashProc]
GO
CREATE TABLE TestHash
(
    StringValue VARCHAR(50)
)
INSERT INTO TestHash (StringValue) VALUES ('TestString')
SELECT StringValue, dbo.sha256Hash(StringValue) AS HashValue FROM TestHash

...into the query editor window.

Execute the query to:

1. Enable CLR support on the SQL Server instance
2. Deploy the SHA256Hash assembly to the SQL Server Programmability-Assemblies node
3. Create sha256Hash as a scalar-valued function
4. Create the TestHash table with the single field StringValue
5. Insert the value 'TestString' into the TestHash table
6. Execute a SELECT statement that uses the sha256Hash function to generate a hash value

The SELECT statement has returned the value 'TestString' and its hash value, which matches the value we obtained with the console version of the application, and the online hash generator.

Conclusion

This article has shown how to compile a strongly-named CLR assembly called SHA256Hash using Microsoft Visual C# 2010 Express, how to deploy the SHA256Hash assembly to SQL Server, how to create a SQL Server function named sha256Hash that references the public method sha256HashProc in the SHA256Hash CLR assembly, and how to use the sha256Hash SQL Server function to generate a hash value for a data string stored in a table.