Step one in producing Shared Access Signatures for SQL Server Data Files in Azure - Generating a SHA2 256 HMAC Hash using Native T-SQL

One of the projects I have been working on as time permits is to construct a way to create a shared access signature using native T-SQL. Shared access signatures are needed when working with SQL Server data files in Azure.

Mostly, DBAs would use a programmatic utility such as Azure Storage Explorer to generate these signatures.

It is also straightforward to do so with SQL CLR based code but this is not enabled in all environments.

So I set about trying to create the required signatures using native T-SQL. I’m not concerned about the slow cryptographic performance as this won’t be a regular operation.

I found some excellent code from Ryan Malayter on github: https://gist.github.com/rmalayter/3130462 that does a SHA –1 or SHA – 512 hash. I’ve modified and enhanced it a bit so that it does SHA2-256 and performs the functions I need.

After the function code, there is a set of test code that shows how the function satisfies the HMAC_SHA256 test vectors. Hope that helps someone. I’ll post more as I complete the next step in building a SAS generator.

USE tempdb;

GO

ALTER FUNCTION dbo.HMAC_SHA256

(

    @HashKey varbinary(8000),

    @ValueToHash varbinary(8000)

)

RETURNS binary(32)

AS

BEGIN

    – Based on concept code from Ryan Malayter: https://gist.github.com/rmalayter/3130462

    DECLARE @k_ipad_partial bigint = CAST(0x3636363636363636 AS bigint);

    DECLARE @k_opad_partial bigint = CAST(0x5C5C5C5C5C5C5C5C AS bigint);

    IF LEN(@HashKey) > 64

    BEGIN

        SET @HashKey = CAST(HASHBYTES(‘SHA2_256’, @HashKey) AS binary (64));

    END ELSE BEGIN

        SET @HashKey = CAST(@HashKey AS binary (64));

    END;

    DECLARE @k_ipad binary(64)

      = CAST((SUBSTRING(@HashKey, 1, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 9, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 17, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 25, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 33, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 41, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 49, 8) ^ @k_ipad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 57, 8) ^ @k_ipad_partial) AS binary(8));

    DECLARE @k_opad binary(64)

      = CAST((SUBSTRING(@HashKey, 1, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 9, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 17, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 25, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 33, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 41, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 49, 8) ^ @k_opad_partial) AS binary(8))

      + CAST((SUBSTRING(@HashKey, 57, 8) ^ @k_opad_partial) AS binary(8));

    RETURN HASHBYTES(‘SHA2_256’, @k_opad + HASHBYTES(‘SHA2_256’, @k_ipad + @ValueToHash));

END;

GO

-- Test with RFC4231 test vectors

DECLARE @KeyToUse varbinary(4000);

DECLARE @ValueToHash varbinary(4000);

DECLARE @HashedValue varbinary(32);

DECLARE @ExpectedResult varbinary(32);

-- Test 1

SET @KeyToUse = 0x0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b;

SET @ValueToHash = CAST(‘Hi There’ AS varbinary(1000));

SET @ExpectedResult = 0xb0344c61d8db38535ca8afceaf0bf12b881dc200c9833da726e9376c2e32cff7;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 1 passed’;

END ELSE BEGIN

    PRINT ‘»Test 1 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

-- Test 2

SET @KeyToUse = CAST(‘Jefe’ AS varbinary(4));

SET @ValueToHash = CAST(‘what do ya want for nothing?’ AS varbinary(1000));

SET @ExpectedResult = 0x5bdcc146bf60754e6a042426089575c75a003f089d2739839dec58b964ec3843;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 2 passed’;

END ELSE BEGIN

    PRINT ‘»Test 2 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

-- Test 3

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = 0xdddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd;

SET @ExpectedResult = 0x773ea91e36800e46854db8ebd09181a72959098b3ef8c122d9635514ced565fe;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 3 passed’;

END ELSE BEGIN

    PRINT ‘»Test 3 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

-- Test 4

SET @KeyToUse = 0x0102030405060708090a0b0c0d0e0f10111213141516171819;

SET @ValueToHash = 0xcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcdcd;

SET @ExpectedResult = 0x82558a389a443c0ea4cc819899f2083a85f0faa3e578f8077a2e3ff46729665b;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 4 passed’;

END ELSE BEGIN

    PRINT ‘»Test 4 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

-- Test 5

SET @KeyToUse = 0x0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c0c;

SET @ValueToHash = CAST(‘Test With Truncation’ AS varbinary(4000));

SET @ExpectedResult = 0xa3b6167473100ee06e0c796c2955552b;

SET @HashedValue = CONVERT(varbinary(16),dbo.HMAC_SHA256(@KeyToUse, @ValueToHash));

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 5 passed’;

END ELSE BEGIN

    PRINT ‘»Test 5 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

-- Test 6

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = CAST(‘Test Using Larger Than Block-Size Key - Hash Key First’ AS varbinary(4000));

SET @ExpectedResult = 0x60e431591ee0b67f0d8a26aacbf5b77f8e0bc6213728c5140546040f0ee37f54;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 6 passed’;

END ELSE BEGIN

    PRINT ‘»Test 6 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

-- Test 7

SET @KeyToUse = 0xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

SET @ValueToHash = CAST(‘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.’ AS varbinary(4000));

SET @ExpectedResult = 0x9b09ffa71b942fcb27635fbcd5b0e944bfdc63644f0713938a7f51535c3a35e2;

SET @HashedValue = dbo.HMAC_SHA256(@KeyToUse, @ValueToHash);

IF @HashedValue = @ExpectedResult

BEGIN

    PRINT ‘»Test 7 passed’;

END ELSE BEGIN

    PRINT ‘»Test 7 failed’;

    PRINT ‘Expected 0x’ + CONVERT(varchar(1000), @ExpectedResult, 2);

    PRINT ‘Received ’ + COALESCE(‘0x’ + CONVERT(varchar(1000), @HashedValue, 2),‘NULL’);

    PRINT ’ ‘;

END;

2014-07-09