Hash Code Encryption with HASHBYTES in SQL Server

In SQL Server, for simple hash code encryption like password encryption, we can use the HASHBYTES function to encrypt the string. This is a built-in cryptographic function with hashing algorithms like MD-2, MD-4, MD-5, SHA-1, SHA-2 (256 and 512). In these algorithm, SHA-2 (256 and 512) are introduced in SQL Server 2008. The other MD and SHA1 algorithms are available in SQL Server 2005 onwards. Starting from the current version (SQL Server 2016) only SHA-2 (256 and 512) are recommended. All the earlier versions of MD and SHA-1 are depreciated.

HASHBYTES function takes in hashing algorithm type and a string to be hashed and returns the generated hash code. Let’s see how to use the HASHBYTES function and generate the hash code of various algorithms.

Example Of Hash Code Encryption

DECLARE @input nvarchar(50);
SET @input = 'www.MyTecBits.com' 

SELECT HASHBYTES('MD2', @input) as 'MD2 Output';
SELECT HASHBYTES('MD4', @input) as 'MD4 Output';
SELECT HASHBYTES('MD5', @input) as 'MD5 Output';
SELECT HASHBYTES('SHA1', @input) as 'SHA1 Output';
SELECT HASHBYTES('SHA2_256', @input) as 'SHA-256 Output';
SELECT HASHBYTES('SHA2_512', @input) as 'SHA-512 Output';

Storing and checking password with encryption

To store a password in hash code, make sure the column which you want to store the hash code is of data type varbinary. Then. use the HASHBYTES function in the insert statement to generate the hash for the password and store it in the column. Below is an example to store a password in hash code with SHA2 512 algorithm and comparing the hash coded password in a select statement.

Example

CREATE TABLE [dbo].[Users](
	[UserID] [int] NOT NULL,
	[UserName] [nvarchar](20) NOT NULL,
	[Password] [varbinary](150) NOT NULL
) ON [PRIMARY]
GO

Insert into Users values (1, 'MyTecBitsUser', HASHBYTES('SHA2_512', 'SomePassword'))
Insert into Users values (2, 'AnotherUser', HASHBYTES('SHA2_512', 'AnotherPassword'))
GO

Select * from Users
GO

Select
	UserID,
	UserName,
	[Password],
	CASE [Password]
		WHEN HASHBYTES('SHA2_512', 'SomePassword') THEN 'Authorized User'
		ELSE 'Not Authorized'
	END As Status
	from Users
GO

Reference

• Cryptographs hash code generators in tools section.
• Details about HASHBYTES built-in cryptographic function in MSDN.