SHA-2 Hashing in SQL Server

One of our online cryptography tools is about Generating SHA-2 Hash. In that tool I have used .NET’s cryptography abstract class to perform SHA-2 hashing. I have also explained with examples about SHA-2 hashing in Java and PHP. Here in this article we will see the various types of SHA-2 hash algorithms and the algorithms supported by SQL Server with examples.

SHA-2 is the 2nd version of the SHA hash generator algorithm. It is otherwise called Secure Hash Algorithm 2. SHA-2 is a set of 6 hashing algorithms (SHA-256, SHA-512, SHA-224, SHA-384, SHA-512/224, SHA-512/256). SQL Server from version 2016 to 2019 supports SHA2 256 and SHA2 512. I haven’t mentioned other hashing algorithms like MD2, MD4, MD5, SHA and SHA1 as they are depreciated in SQL Server 2016 and later.

SHA2 256

SHA-256 is a 256 bit (32 bytes) hashing algorithm which can calculate hash code for an input up to 264-1 bits. It undergoes 64 rounds of hashing. The calculated hash code will be a 64 digit hexadecimal number. For example, the SHA-256 hash code for www.mytecbits.com is 575f62a15889fa8ca55514a10754d2f98e30c57c4538f0f3e39dc53114533857.

SHA2 256 Hashing in SQL Server

Hashing is one of the best ways to store passwords in a database. Here is a simple example to generate a SHA2 256 hash code for a password and store it in a table. The best datatype to store the hash code is binary and for SHA2 256 hashing you need 32 bytes. So, binary(32) will be the best option to store SHA2 256 hash codes.

/* Create Table */
CREATE TABLE [dbo].[MTB_TEST_SHA](
        [ID] int NOT NULL PRIMARY KEY,
        [Password] binary(32) NULL
    ) ON [PRIMARY]
GO

/* Generate hash and insert it */
INSERT [dbo].[MTB_TEST_SHA]
    VALUES (
        1,
        Hashbytes('SHA2_256', 'SomePassword')
    );

/* See the hash code */
SELECT *
    FROM   [dbo].[MTB_TEST_SHA];
GO

/* Result */
0xCA74E5FE75654735D3B8D04A7BDF5DCDD06F1C6C2A215171A2
4E5A9DCB28E7A2
SHA-2 Hashing In SQL Server

To check if the password is matching with the one stored in the table:

/* Check with the original value */
SELECT 
    IIF(Hashbytes('SHA2_256', 'SomePassword') = Password, 'Match', 'Not A Match') 
    FROM [dbo].[MTB_TEST_SHA]
    WHERE [ID] = 1;  
GO

/* Check with a different value */
SELECT 
    IIF(Hashbytes('SHA2_256', 'SomeOtherPassword') = Password, 'Match', 'Not A Match') 
    FROM [dbo].[MTB_TEST_SHA]
    WHERE [ID] = 1;  
GO
SHA2 256 hash code matching

SHA2 512

SHA-512, as the name suggests, is a 512 bit (64 bytes) hashing algorithm which can calculate hash code for an input up to 2128-1 bits. It undergoes 80 rounds of hashing. So, SHA-512 is stronger hashing than SHA-256. The calculated hash code will be a 124 digit hexadecimal number. For example, the SHA-512 hash code for www.mytecbits.com is d91359957c1a7f11931bc0cfb1082b0061d5bb5c7bd76d789f0e2ff2b3769edde9d53993946c23677404a8acf0f4a70134bfd2f8f182103a84ef789054241516.

SHA2 512 Hashing in SQL Server

SHA2 512 hash codes need 64 bytes of binary data type. So, binary(64) will be the best option to store SHA2 512 hash codes.

/* Create Table */
CREATE TABLE [dbo].[MTB_TEST_SHA](
        [ID] int NOT NULL PRIMARY KEY,
        [Password] binary(32) NULL
    ) ON [PRIMARY]
GO

/* Generate hash and insert it */
INSERT [dbo].[MTB_TEST_SHA]
    VALUES (
        1,
        Hashbytes('SHA2_256', 'SomePassword')
    );

/* See the hash code */
SELECT *
    FROM   [dbo].[MTB_TEST_SHA];
GO

/* Result */
0x255B593DDF734DDCB33515F01222FFB944E40D3C6F771696F6
1E14D38BF7D11952AC253FA23E91588F70E62E8224DBD934CB92
08E9D34892AB7E2BCFCE2FD261
SHA-2 512 Hashing In SQL Server

To check if the password is matching with the one stored in the table:

/* Check with the original value */
SELECT 
    IIF(Hashbytes('SHA2_256', 'SomePassword') = Password, 'Match', 'Not A Match') 
    FROM [dbo].[MTB_TEST_SHA]
    WHERE [ID] = 1;  
GO

/* Check with a different value */
SELECT 
    IIF(Hashbytes('SHA2_256', 'SomeOtherPassword') = Password, 'Match', 'Not A Match') 
    FROM [dbo].[MTB_TEST_SHA]
    WHERE [ID] = 1;  
GO
SHA2 512 hash code matching

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.