October 23, 2012 at 11:37 am
Has any one encrypted data using HMAC-SHA1 or HMAC-SHA256 encryption method. I need this done for the current project that I am working and am unable to find any code or logic that I could use.
Any help is appreciated.
Thanks
Sreejith
October 23, 2012 at 11:47 am
Sreejith Sreedharan (10/23/2012)
Has any one encrypted data using HMAC-SHA1 or HMAC-SHA256 encryption method. I need this done for the current project that I am working and am unable to find any code or logic that I could use.Any help is appreciated.
Thanks
Sreejith
both SHA1 and SHA256 are hash routines, used to determine whether something has been tampered with/different when compared to the hash of the original.
it is not an encryption routine in that converts some string into an encrypted varbinary string.
it would be fine for comparing passwords, or confirm that an email has not been fiddled with.
SHA1 is available in 2008 and above, but to use SHA256 or SHA512 in SQL 2008/R2, you'll need to add a CLR which implements the routine, but SQL2012 supports it natively as part of the hashbytes function now.
DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HASHBYTES('SHA1', @HashThis);
GO
Lowell
October 23, 2012 at 12:27 pm
The problem with that solution is that you are not using the key. I need a function that accepts the data to be hashed and Key to use(I need HMAC-SHA not just SHA).
Thanks
Sreejith
October 23, 2012 at 12:36 pm
Looks like you'll have to use a CLR routine.
i grabbed this function in vb.net from Google, you'd have to convert it to a CLR function from there.
source:http://stackoverflow.com/questions/7515164/visual-basic-2010-hmac-sha1
edit: here's the converted version as well.
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function HashHMACSHA1(ByVal StringToHash As SqlString, ByVal HashKey As SqlString) As SqlString
Dim _sResults As SqlString
_sResults = New SqlString(HashString(StringToHash.ToString, HashKey.ToString))
Return _sResults
End Function
Friend Shared Function HashString(ByVal StringToHash As String, ByVal HachKey As String) As String
Dim myEncoder As New System.Text.UTF8Encoding
Dim Key() As Byte = myEncoder.GetBytes(HachKey)
Dim Text() As Byte = myEncoder.GetBytes(StringToHash)
Dim myHMACSHA1 As New System.Security.Cryptography.HMACSHA1(Key)
Dim HashCode As Byte() = myHMACSHA1.ComputeHash(Text)
Dim hash As String = Replace(BitConverter.ToString(HashCode), "-", "")
Return hash.ToLower
End Function
Lowell
October 23, 2012 at 12:41 pm
Thanks for the email. Based on my searches thats what I came across too. Do you know of any one who has created this as CLR function? May be I will write my first CLR function 🙂
Thanks
Sreejith
October 23, 2012 at 12:43 pm
Sreejith Sreedharan (10/23/2012)
Thanks for the email. Based on my searches thats what I came across too. Do you know of any one who has created this as CLR function? May be I will write my first CLR function 🙂Thanks
Sreejith
i edited my post, and included one version above; it seemed to work for me just fine, but i cannot say that it matches hases that you may have already existing in your data; you might need to play with it a bit.
declare @val nvarchar(4000);
SELECT @val =dbo.HashHMACSHA1('Apples And Oranges','SekretKey');
SELECT
CASE
WHEN @val = 'ecd76785ff2bd3519a7c522d1cc55780c0481a6e'
THEN 'True'
ELSE 'FALSE'
END
Lowell
October 30, 2013 at 10:27 am
My apologies for the necromancy on a 2012 thread, but in looking for exactly this, I found both this thread and code to do so that belongs here.
Credit to Ryan Malayter, who wrote a simple, pure T-SQL implementation, and one that's SQL 2005 compliant as well. Note that this code should work for any HASHBYTES algorithms, including ones that weren't available when the code was written (SHA2_256 and SHA2_512 have a BLOCKSIZE of 128 bytes) as long as the output VARBINARY(X) is raised to a sufficient size from the current maximum.
https://gist.github.com/rmalayter/3130462
WARNING: Hashbytes maxes out at an 8000 byte input of VARCHAR, NVARCHAR, or VARBINARY, so HMAC will have a shorter limit due to concatenation.
Below are two Scalar UDF's (SLOW!) based on the standards, including RFC test vectors from the relevant RFC's, which can be run to verify that these functions (and any changes anyone makes to them) continue to match the standard.
WARNING: Don't use these directly for PBKDF2 (which is what you'll need for password storage), instead, you'll need to extract the internals and optimize.
HMAC-SHA-1, from SQL2005 through SQL 2012+
USE tempdb; -- in case YourDB does not exist
USE YourDB;
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Yourfn_CRYPT_HMAC_SHA1]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Yourfn_CRYPT_HMAC_SHA1]
GO
CREATE FUNCTION [dbo].[Yourfn_CRYPT_HMAC_SHA1]
(
@key VARBINARY(8000)
,@Data VARBINARY(8000)
)
RETURNS BINARY(20) -- 160 bits maximum return value, which is the SHA-1 digest size
AS
BEGIN
-- See http://tools.ietf.org/html/rfc2104 and http://tools.ietf.org/html/rfc4634 and http://tools.ietf.org/html/rfc4868
-- This is a dedicated HMAC-SHA-1 version, with a moderate amount of performance tuning.
/*
-- test vectors
SET NOCOUNT ON
DECLARE @Result VARBINARY(64)
DECLARE @start DATETIME2(7)
SET @start = SYSDATETIME()
-- RFC2202 test vectors as a continuation
PRINT 'RFC2202 Test 1'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b,CONVERT(VARBINARY(4000),'Hi There'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xb617318655057264e28bc0b6fb378c8ef146be00 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC2202 Test 2'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(CONVERT(VARBINARY(4000),'Jefe'),CONVERT(VARBINARY(4000),'what do ya want for nothing?'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xeffcdf6ae5eb2fa2d27416d5f184df9c259a7c79 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC2202 Test 3'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,0xdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd)
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x125d7342b9ac11cd91a39af48aa17b4f63f175d3 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC2202 Test 4'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0x0102030405060708090a0b0c0d0e0f10111213141516171819,0xcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd)
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x4c9007f4026250c6bc8414f9bf50c86c2d7235da THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC2202 Test 5'
SET @Result = CONVERT(BINARY(12),YourDB.dbo.Yourfn_CRYPT_HMAC('MD5',0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c,CONVERT(VARBINARY(4000),'Test With Truncation')))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x56461ef2342edc00f9bab995 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
SET @Result = CONVERT(BINARY(12),YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c,CONVERT(VARBINARY(4000),'Test With Truncation')))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x4c1a03424b55e07fe7f27be1 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC2202 Test 6 binary data'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,0x54657374205573696e67204c6172676572205468616e20426c6f636b2d53697a65204b6579202d2048617368204b6579204669727374)--,CONVERT(VARBINARY(4000),'Test Using Larger Than Block-Size Key - Hash Key First'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xaa4ae5e15272d00e95705637ce8a3b55ed402112 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
-- REPEAT test 6 but using character type data, not binary
PRINT 'RFC2202 Test 6 character data'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,CONVERT(VARBINARY(4000),'Test Using Larger Than Block-Size Key - Hash Key First'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xaa4ae5e15272d00e95705637ce8a3b55ed402112 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC2202 Test 7'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA1(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,CONVERT(VARBINARY(4000),'Test Using Larger Than Block-Size Key and Larger Than One Block-Size Data'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xe8e99d0f45237d786d6bbaa7965c7808bbff1a91 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'Duration (ms): ' + CONVERT(VARCHAR(23),DATEDIFF(ms,@start,SYSDATETIME()))
*/
DECLARE @ipadRFC2104 BIGINT
DECLARE @opadRFC2104 BIGINT
DECLARE @k_ipadRFC2104 BINARY(64) -- BLOCKSIZE in bytes per HMAC definition
DECLARE @k_opadRFC2104 BINARY(64) -- BLOCKSIZE in bytes per HMAC definition
--SQL 2005 fails to allow binary operations on two binary data types!!! We use bigint and interate 8 times for 512 bits = 64 bytes
SET @ipadRFC2104 = CAST(0x3636363636363636 AS BIGINT)
SET @opadRFC2104 = CAST(0x5C5C5C5C5C5C5C5C AS BIGINT)
-- B = BLOCKSIZE (64 bytes for MD5, SHA1, SHA-256, and 128 bytes for SHA-384 and SHA-512, per RFC2104 and RFC4868)
IF LEN(@Key) > 64 -- Applications that use keys longer than B bytes will first hash the key using H and then use the resultant L byte string as the actual key to HMAC
SET @key = CAST(HASHBYTES('SHA1', @key) AS BINARY (64))
ELSE
SET @key = CAST(@Key AS BINARY (64)) -- append zeros to the end of K to create a B byte string
-- Loop unrolled for definite performance benefit
-- Must XOR BLOCKSIZE bytes
SET @k_ipadRFC2104 = CONVERT(BINARY(8),(SUBSTRING(@Key, 1, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 9, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 17, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 25, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 33, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 41, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 49, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 57, 8) ^ @ipadRFC2104))
-- Loop unrolled for definite performance benefit
-- Must XOR BLOCKSIZE bytes
SET @k_opadRFC2104 = CONVERT(BINARY(8),(SUBSTRING(@Key, 1, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 9, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 17, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 25, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 33, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 41, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 49, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 57, 8) ^ @opadRFC2104))
RETURN HASHBYTES('SHA1', @k_opadRFC2104 + HASHBYTES('SHA1', @k_ipadRFC2104 + @data))
END
GO
HMAC-SHA-512, for SQL2012+
USE tempdb; -- in case YourDB does not exist
USE YourDB;
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Yourfn_CRYPT_HMAC_SHA512]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[Yourfn_CRYPT_HMAC_SHA512]
GO
CREATE FUNCTION [dbo].[Yourfn_CRYPT_HMAC_SHA512]
(
@key VARBINARY(8000)
,@Data VARBINARY(8000)
)
RETURNS BINARY(64) -- 512 bits maximum return value, which is the SHA-512 digest size
AS
BEGIN
-- See http://tools.ietf.org/html/rfc2104 and http://tools.ietf.org/html/rfc4634 and http://tools.ietf.org/html/rfc4868
-- This is a dedicated HMAC-SHA-512 version, with a moderate amount of performance tuning.
/*
--RFC4231 test vectors
SET NOCOUNT ON
DECLARE @Result VARBINARY(64)
DECLARE @start DATETIME2(7)
SET @start = SYSDATETIME()
PRINT 'RFC4321 Test 1'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b,CONVERT(VARBINARY(4000),'Hi There'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x87aa7cdea5ef619d4ff0b4241a1d6cb02379f4e2ce4ec2787ad0b30545e17cdedaa833b7d6b8a702038b274eaea3f4e4be9d914eeb61f1702e696c203a126854 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 2'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(CONVERT(VARBINARY(4),'Jefe'),CONVERT(VARBINARY(4000),'what do ya want for nothing?'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x164b7a7bfcf819e2e395fbe73b56e0a387bd64222e831fd610270cd7ea2505549758bf75c05a994a6d034f65f8f0e6fdcaeab1a34d4a6b4b636e070a38bce737 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 3'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,0xdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd)
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xfa73b0089d56a284efb0f0756c890be9b1b5dbdd8ee81a3655f83e33b2279d39bf3e848279a722c806b485a47e67c807b946a337bee8942674278859e13292fb THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 4'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0x0102030405060708090a0b0c0d0e0f10111213141516171819,0xcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd)
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xb0ba465637458c6990e5a8c5f61d4af7e576d97ff94b872de76f8050361ee3dba91ca5c11aa25eb4d679275cc5788063a5f19741120c4f2de2adebeb10a298dd THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 5'
SET @Result = CONVERT(BINARY(16),YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c,CONVERT(VARBINARY(4000),'Test With Truncation')))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x415fad6271580a531d4179bc891d87a6 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 6 binary data'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,0x54657374205573696e67204c6172676572205468616e20426c6f636b2d53697a65204b6579202d2048617368204b6579204669727374)--,CONVERT(VARBINARY(4000),'Test Using Larger Than Block-Size Key - Hash Key First'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x80b24263c7c1a3ebb71493c1dd7be8b49b46d1f41b4aeec1121b013783f8f3526b56d037e05f2598bd0fd2215d6a1e5295e64f73f63f0aec8b915a985d786598 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 6 character data'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,CONVERT(VARBINARY(4000),'Test Using Larger Than Block-Size Key - Hash Key First'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0x80b24263c7c1a3ebb71493c1dd7be8b49b46d1f41b4aeec1121b013783f8f3526b56d037e05f2598bd0fd2215d6a1e5295e64f73f63f0aec8b915a985d786598 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'RFC4321 Test 7'
SET @Result = YourDB.dbo.Yourfn_CRYPT_HMAC_SHA512(0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,CONVERT(VARBINARY(4000),'This is a test using a larger than block-size key and a larger than block-size data. The key needs to be hashed before being used by the HMAC algorithm.'))
SELECT @Result
PRINT CASE WHEN @Result IS NULL THEN 'FAIL NULL - PROBABLY INVALID HASH ALGO FOR YOUR SQL SERVER VERSION' WHEN @Result = 0xe37b6a775dc87dbaa4dfa9f96e5e3ffddebd71f8867289865df5a32d20cdc944b6022cac3c4982b10d5eeb55c3e4de15134676fb6de0446065c97440fa8c6a58 THEN 'PASS' ELSE 'FAIL INVALID RESULT' END
PRINT 'Duration (ms): ' + CONVERT(VARCHAR(23),DATEDIFF(ms,@start,SYSDATETIME()))
*/
DECLARE @ipadRFC2104 BIGINT
DECLARE @opadRFC2104 BIGINT
DECLARE @k_ipadRFC2104 BINARY(128) -- BLOCKSIZE in bytes per HMAC definition
DECLARE @k_opadRFC2104 BINARY(128) -- BLOCKSIZE in bytes per HMAC definition
--SQL 2005 fails to allow binary operations on two binary data types!!! We use bigint and interate 16 times for 1024 bits = 128 bytes
SET @ipadRFC2104 = CAST(0x3636363636363636 AS BIGINT)
SET @opadRFC2104 = CAST(0x5C5C5C5C5C5C5C5C AS BIGINT)
-- B = BLOCKSIZE (64 bytes for MD5, SHA1, SHA-256, and 128 bytes for SHA-384 and SHA-512, per RFC2104 and RFC4868)
IF LEN(@Key) > 128 -- Applications that use keys longer than B bytes will first hash the key using H and then use the resultant L byte string as the actual key to HMAC
SET @key = CAST(HASHBYTES('SHA2_512', @key) AS BINARY (128))
ELSE
SET @key = CAST(@Key AS BINARY (128)) -- append zeros to the end of K to create a B byte string
-- Loop unrolled for definite performance benefit
-- Must XOR BLOCKSIZE bytes
SET @k_ipadRFC2104 = CONVERT(BINARY(8),(SUBSTRING(@Key, 1, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 9, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 17, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 25, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 33, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 41, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 49, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 57, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 65, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 73, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 81, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 89, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 97, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 105, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 113, 8) ^ @ipadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 121, 8) ^ @ipadRFC2104))
-- Loop unrolled for definite performance benefit
-- Must XOR BLOCKSIZE bytes
SET @k_opadRFC2104 = CONVERT(BINARY(8),(SUBSTRING(@Key, 1, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 9, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 17, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 25, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 33, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 41, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 49, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 57, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 65, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 73, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 81, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 89, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 97, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 105, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 113, 8) ^ @opadRFC2104))
+ CONVERT(BINARY(8),(SUBSTRING(@Key, 121, 8) ^ @opadRFC2104))
RETURN HASHBYTES('SHA2_512', @k_opadRFC2104 + HASHBYTES('SHA2_512', @k_ipadRFC2104 + @data))
END
GO
EDITED 20131110 to provide standards based, more optimized solutions (still with scalar UDF overhead).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply