(SQL Server) Plaza API (bol.com) HMAC-SHA256 Authentication

Demonstrates how to compute the Authorization header for bol.com using HMAC-SHA256.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
//
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @crypt, 'MacAlgorithm', 'hmac'

    DECLARE @publicKey nvarchar(4000)
    SELECT @publicKey = 'oRNWbHFXtAECmhnZmEndcjLIaSKbRMVE'
    DECLARE @privateKey nvarchar(4000)
    SELECT @privateKey = 'MaQHPOnmYkPZNgeRziPnQyyOJYytUbcFBVJBvbMKoDdpPqaZbaOiLUTWzPAkpPsZFZbJHrcoltdgpZolyNcgvvBaKcmkqFjucFzXhDONTsPAtHHyccQlLUZpkOuywMiOycDWcCySFsgpDiyGnCWCZJkNTtVdPxbSUTWVIFQiUxaPDYDXRQAVVTbSVZArAZkaLDLOoOvPzxSdhnkkJWzlQDkqsXNKfAIgAldrmyfROSyCGMCfvzdQdUQEaYZTPEoA'

    -- The string to sign is this:
    -- http_verb +'\n\n'+ content_type +'\n'+ x_bol_date +'\n'+ 'x-bol-date:'+ x_bol_date +'\n'+ uri

    DECLARE @http_verb nvarchar(4000)
    SELECT @http_verb = 'GET'
    DECLARE @content_type nvarchar(4000)
    SELECT @content_type = 'application/xml'
    DECLARE @x_bol_date nvarchar(4000)
    SELECT @x_bol_date = 'Wed, 17 Feb 2016 00:00:00 GMT'
    DECLARE @uri nvarchar(4000)
    SELECT @uri = '/services/rest/orders/v2'

    -- IMPORTANT: Notice the use of underscore and hyphen (dash) chars in x-bol-date vs. x_bol_date.
    -- In one place hypens are used.  In two places, underscore chars are used.
    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sb OUT

    DECLARE @success int
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @http_verb
    EXEC sp_OAMethod @sb, 'Append', @success OUT, CHAR(10) + CHAR(10)
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @content_type
    EXEC sp_OAMethod @sb, 'Append', @success OUT, CHAR(10)
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @x_bol_date
    EXEC sp_OAMethod @sb, 'Append', @success OUT, CHAR(10) + 'x-bol-date:'
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @x_bol_date
    EXEC sp_OAMethod @sb, 'Append', @success OUT, CHAR(10)
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @uri

    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT

    PRINT '[' + @sTmp0 + ']'

    -- Set the HMAC key:
    EXEC sp_OAMethod @crypt, 'SetMacKeyEncoded', @success OUT, @privateKey, 'ascii'
    DECLARE @mac nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'MacStringENC', @mac OUT, @sTmp0

    -- The answer should be: nqzLWvXI1eBhBXrRx5NF23V5hS8Q1xWCloJzPi/RAts=

    PRINT @mac

    -- The last step is to append the public key with the signature
    DECLARE @sbHeader int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbHeader OUT

    EXEC sp_OAMethod @sbHeader, 'Append', @success OUT, @publicKey
    EXEC sp_OAMethod @sbHeader, 'Append', @success OUT, ':'
    EXEC sp_OAMethod @sbHeader, 'Append', @success OUT, @mac

    DECLARE @hdrValue nvarchar(4000)
    EXEC sp_OAMethod @sbHeader, 'GetAsString', @hdrValue OUT

    PRINT @hdrValue

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @sbHeader


END
GO