(SQL Server) Ibanity HTTP Signature for XS2A, Isabel Connect, Ponto Connect

See more Ibanity Examples

Demonstrates how to add a Signature header for Ibanity HTTP requests.

For more information, see https://documentation.ibanity.com/http-signature

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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    -- In order to sign your HTTP requests, you have to add 2 headers to the HTTP request: Digest: the digest of the request payload and Signature: the actual signature of the request. 

    -- POST /xs2a/customer-access-tokens HTTP/1.1
    -- Host: api.ibanity.com
    -- Content-Type: application/json
    -- Digest: SHA-512=z4PhNX7vuL3xVChQ1m2AB9Yg5AULVxXcg/SpIdNs6c5H0NE8XYXysP+DGNKHfuwvY7kxvUdBeoGlODJ6+SfaPg==
    -- Ibanity-Idempotency-Key: 61f02718-eeee-46e1-b5eb-e8fd6e799c2d
    -- Signature: keyId="62f02718-eeee-46e1-b5eb-e8fd6e799c2e",created=1599659223,algorithm="hs2019",headers="(request-target) host digest (created) ibanity-idempotency-key",signature="SjWJWbWN7i0...zsbM="
    -- 
    -- {"data":{"type":"customerAccessToken", "attributes":{"applicationCustomerReference":"15874569"}}}

    -- The payload (body) of the above HTTP request is the JSON.
    -- Build the JSON above.
    -- Use this online tool to generate code from sample JSON: 
    -- Generate Code to Create JSON
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'data.type', 'customerAccessToken'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'data.attributes.applicationCustomerReference', '15874569'

    DECLARE @payload nvarchar(4000)
    EXEC sp_OAMethod @json, 'Emit', @payload OUT

    PRINT 'payload = ' + @payload

    -- Step 1: Build the (created) virtual header

    DECLARE @dtNow int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dtNow OUT

    EXEC sp_OAMethod @dtNow, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @created nvarchar(4000)
    EXEC sp_OAMethod @dtNow, 'GetAsUnixTimeStr', @created OUT, 0

    PRINT 'created = ' + @created

    -- Step 2: Build the Digest header
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha512'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'

    DECLARE @sbDigestHdrValue int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbDigestHdrValue OUT

    EXEC sp_OAMethod @sbDigestHdrValue, 'Append', @success OUT, 'SHA-512='
    EXEC sp_OAMethod @json, 'Emit', @sTmp1 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @sTmp0 OUT, @sTmp1
    EXEC sp_OAMethod @sbDigestHdrValue, 'Append', @success OUT, @sTmp0

    EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Step 3: Build the (request target) virtual header

    -- In order to build the signature you will need a virtual header named (request-target) (the parentheses are important). 
    -- The (request-target) is the string concatenation of the HTTP method (in lowercase) with the path and query parameters.
    DECLARE @request_target nvarchar(4000)
    SELECT @request_target = 'post /xs2a/customer-access-tokens'

    -- Step 4: Build the signing string

    -- The signing string is the concatenation of the signed header names (in lowercase) and values separated by a LF.

    -- You must always sign the following headers: (request-target), host, (created), digest. 
    -- If used, you must also sign the authorization header and any ibanity-* headers, such as ibanity-idempotency-key. 

    DECLARE @sbSigningString int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSigningString OUT

    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, '(request-target): '
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @request_target, 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'host: '
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, 'api.ibanity.com', 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'digest: '
    EXEC sp_OAMethod @sbDigestHdrValue, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @sTmp0, 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, '(created): '
    EXEC sp_OAMethod @sbSigningString, 'AppendLine', @success OUT, @created, 0
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, 'ibanity-idempotency-key: '
    DECLARE @idempotencyKey nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'GenerateUuid', @idempotencyKey OUT
    EXEC sp_OAMethod @sbSigningString, 'Append', @success OUT, @idempotencyKey

    -- Step 5: Build the signed headers list

    -- To allow Ibanity to check the signed headers, you must provide a list of the header names. They should be lowercase and in the same order used to create the signing string. 
    DECLARE @signed_headers_list nvarchar(4000)
    SELECT @signed_headers_list = '(request-target) host digest (created) ibanity-idempotency-key'

    -- Step 6: Build the Signature header

    -- This is where the real signing happens. The signature header is a combination of several sub-headers -
    -- 
    --     keyId: the identifier for the application's signature certificate, obtained from the Developer Portal
    --     algorithm: the digital signature algorithm used to generate the signature (must be hs2019)
    --     headers: The list of HTTP headers created in step 5
    --     signature: the Base64-encoded digital signature of the signing string created in step 4.

    DECLARE @privKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadEncryptedPemFile', @success OUT, 'my_ibanity_signature_private_key.pem', 'pem_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT

    EXEC sp_OASetProperty @rsa, 'PssSaltLen', 32
    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
    -- Use the RSASSA-PSS signature algorithm
    EXEC sp_OASetProperty @rsa, 'OaepPadding', 1

    EXEC sp_OAMethod @rsa, 'ImportPrivateKeyObj', @success OUT, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Sign the signing string.
    DECLARE @sigBase64 nvarchar(4000)
    EXEC sp_OAMethod @sbSigningString, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @rsa, 'SignStringENC', @sigBase64 OUT, @sTmp0, 'sha-256'
    EXEC sp_OAGetProperty @rsa, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @dtNow
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbDigestHdrValue
        EXEC @hr = sp_OADestroy @sbSigningString
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- Build the signature header value.
    DECLARE @sbSigHeaderValue int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSigHeaderValue OUT

    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, 'keyId="'
    -- Use your identifier for the application's signature certificate, obtained from the Developer Portal
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '62f02718-eeee-46e1-b5eb-e8fd6e799c2e'
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '",created='
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @created
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, ',algorithm="hs2019",headers="'
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @signed_headers_list
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '",signature="'
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, @sigBase64
    EXEC sp_OAMethod @sbSigHeaderValue, 'Append', @success OUT, '"'

    EXEC sp_OAMethod @sbSigHeaderValue, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @dtNow
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbDigestHdrValue
    EXEC @hr = sp_OADestroy @sbSigningString
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @sbSigHeaderValue


END
GO