T-SQL Hmac (Rfc2104 SHA2_256) Implementation

Question

I'm trying to implement an Hmac in T-SQL using this question as an guide. Please critique this:

create function dbo.Hmac (
    @key varbinary(8000)
  , @message varbinary(7936)
)   
returns table
with schemabinding as
return (
    select HashBytes('SHA2_256', Pads.o + HashBytes('SHA2_256', Pads.i + @message)) as [Hash]
    from (values(
        Cast( -- keys longer than blocksize are shortened and keys shorter than blocksize are zero-padded
            case
                when DataLength(@key) > 64 then HashBytes('SHA2_256', @key)
                else @key
            end
        as binary(64))
      , Cast(0x3636363636363636 as bigint) -- Rfc2104 inner padding constant
      , Cast(0x5C5C5C5C5C5C5C5C as bigint) -- Rfc2104 outer padding constant
    )) as Vars ([key], innerPadding, outerPadding)
    cross apply (values(
      -- unrolled loop to XOR key and iPad
        Cast((Substring(Vars.[key], 1, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.innerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.innerPadding) as binary(8))
      -- unrolled loop to XOR key and oPad
      , Cast((Substring(Vars.[key], 1, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 9, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 17, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 25, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 33, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 41, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 49, 8) ^ Vars.outerPadding) as binary(8))
      + Cast((Substring(Vars.[key], 57, 8) ^ Vars.outerPadding) as binary(8))
    )) as Pads (i, o)
);

Answer 1

Looks OK to me, without knowing Transact SQL anyway. Some things to consider:

• obviously this implements just HMAC-SHA256, so the function should be named that way;
• the key parameters is 8000 varbinary max value. That seems largish to me (although it might not matter much as the size is variable anyway);
• there is no way to perform a hash over multiple fields in the DB which might be a use case (no incremental update possibility);
• similarly, often it is required to reuse the key for multiple calculations, in that case you can cache the Pads.i and Pads.o values.

I'm also missing some constants being introduced but that might be due to the nature of TSQL.

Don't forget to test against NIST test vectors, just to be sure.