How to generate a SHA-3(256) hash in SQL SERVER 2016+

Question

How can I generate a SHA-3 (256) hash within SQL Server 2016 ?

HASHBYTES appears to only go up to SHA-2 (256) or SHA-2 (512).

Microsoft BOL isn't giving me a warm and fuzzy, that this is built in anywhere.

-- SHA2-256
DECLARE @HashThis nvarchar(32);  
SET @HashThis = CONVERT(nvarchar(32),'123456');  
SELECT HASHBYTES('SHA2_256', @HashThis);  

-- Output: 0xEC278A38901287B2771A13739520384D43E4B078F78AFFE702DEF108774CCE24

-- SHA3-256  (KECCAK)
DECLARE @HashThis nvarchar(32);  
SET @HashThis = CONVERT(nvarchar(32),'123456');  
SELECT HASHBYTES('SHA3_256', @HashThis);  

-- Output: NULL

user_2344394 · Answer

You can achieve SHA-3 256 Hashing with a SQL Server CLR integration

There is a project on GitHub that has most of the work done for you already and you could easily add SHA3 support to it.

https://github.com/sedenardi/sql-hashing-clr

There is no way of SHA3-256 hashing with pure dotnet core. I recommend you make use of BouncyCastle library (https://www.bouncycastle.org/csharp/index.html) that has a SHA3-256 support. There is a nuget library package that is a wrapper on top of BouncyCastle and could make SHA3-256 hashing relatively easy to achieve. https://www.nuget.org/packages/SHA3.Net/. You'll need to use this package and update HashUtil.cs

Pre Req: Build CLR .dll

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;
-- include NugetPackage to help with SHA-3 hashing

public class HashUtil
{
    [SqlFunction(IsDeterministic = true)]
    public static SqlBinary GetHash(SqlString algorithm, SqlBytes src)
    {
        if (src.IsNull)
            return null;

        switch (algorithm.Value.ToUpperInvariant())
        {
            case "MD5":
                return new SqlBinary(MD5.Create().ComputeHash(src.Stream));
            case "SHA1":
                return new SqlBinary(SHA1.Create().ComputeHash(src.Stream));
            case "SHA2_256":
                    return new SqlBinary(SHA256.Create().ComputeHash(src.Stream));
            case "SHA2_512":
                    return new SqlBinary(SHA512.Create().ComputeHash(src.Stream));
            -- add SHA3-256
            default:
                throw new ArgumentException("HashType", "Unrecognized hashtype: " + algorithm.Value);
        }
    }
}

Deploy the CLR .dll to SQL Server

For reference here is Microsoft's documentation on how to deploy a CLR .dll

https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/deploying-clr-database-objects?view=sql-server-ver15

Deploy Function to SQL Server

CREATE FUNCTION dbo.GetHashHybrid(@algorithm NVARCHAR(4000),@input VARBINARY(MAX))
 RETURNS VARBINARY(8000) WITH SCHEMABINDING
 AS
 BEGIN
 RETURN ( 
     SELECT dbo.GetHash(@algorithm,@input)
 )
 END

Call dbo.GetHash

DECLARE @INPUT VARCHAR(MAX);
SELECT @INPUT = REPLICATE(CAST('test1' AS VARCHAR(MAX)),2000);
SELECT dbo.GetHash('MD5',CONVERT(VARBINARY(MAX),@INPUT));

This example shows MD5 hashing but demonstrates the concept of how you can use CLR to create hashing functionality that is not supported "out of the box" from SQL Server currently.

How to generate a SHA-3(256) hash in SQL SERVER 2016+

1 Answers