(SQL Server) ITIDA Debug Signed JSON and HTTP POST

See more Egypt ITIDA Examples

Explains how to debug when problems are encountered with ITIDA canonicalization, signing, and posting to api.preprod.invoicing.eta.gov.eg.

The most common error response from the api.preprod.invoicing.eta.gov.eg server is:

ITIDA Signature Invalid Signature * 4043 4043:message-digest attribute value does not match the calculated value[message-digest attribute value does not match the calculated value]

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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @crypt, 'VerboseLogging', 1

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

    EXEC sp_OASetProperty @cert, 'VerboseLogging', 1

    -- Set the smart card PIN, which will be needed for signing.
    EXEC sp_OASetProperty @cert, 'SmartCardPin', '12345678'

    -- There are many ways to load the certificate.  
    -- This example was created for a customer using an ePass2003 USB token.
    -- Assuming the USB token is the only source of a hardware-based private key..
    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadFromSmartcard', @success OUT, ''
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Tell the crypt class to use this cert.
    EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

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

    -- Setting "DigestData" causes OID 1.2.840.113549.1.7.5 (digestData) to be used.
    EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'DigestData', 1
    EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'OmitAlgorithmIdNull', 1

    -- Indicate that we are passing normal JSON and we want Chilkat do automatically
    -- do the ITIDA JSON canonicalization:
    EXEC sp_OAMethod @cmsOptions, 'UpdateBool', @success OUT, 'CanonicalizeITIDA', 1

    EXEC sp_OAMethod @cmsOptions, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @crypt, 'CmsOptions', @sTmp0

    -- The CadesEnabled property applies to all methods that create CMS/PKCS7 signatures. 
    -- To create a CAdES-BES signature, set this property equal to true. 
    EXEC sp_OASetProperty @crypt, 'CadesEnabled', 1

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'

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

    EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'contentType', 1
    EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'signingTime', 1
    EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'messageDigest', 1
    EXEC sp_OAMethod @jsonSigningAttrs, 'UpdateInt', @success OUT, 'signingCertificateV2', 1
    EXEC sp_OAMethod @jsonSigningAttrs, 'Emit', @sTmp0 OUT
    EXEC sp_OASetProperty @crypt, 'SigningAttributes', @sTmp0

    -- By default, all the certs in the chain of authentication are included in the signature.
    -- If desired, we can choose to only include the signing certificate:
    EXEC sp_OASetProperty @crypt, 'IncludeCertChain', 0

    -- First, load the JSON file that is to be canonicalized and signed.
    -- The JSON file should look like this:
    -- NOTE: The JSON should not begin with "{ "documents" : [ ..."
    -- We want to send a single signed document like this:

    --       {
    --          "issuer":{
    --             "address":{
    --                "branchID":"0",
    --                "country":"EG",
    --                "regionCity":"Cairo",
    --                "postalCode":"",
    --                "buildingNumber":"0",
    --                "street":"123rd Street",
    --                "governate":"GOVERNATE"
    --             },
    --             "type":"B",
    --             "id":"209999899",
    --             "name":"Xyz SAE"
    --          },
    --          "receiver":{
    --             "address":{
    --                "country":"EG",
    --                "regionCity":"CAIRO",
    --                "postalCode":"11435",
    --                "buildingNumber":"0",
    --                "street":"Autostrad Road Abc",
    --                "governate":"GOVERNATE"
    --             },
    --             "type":"B",
    --             "id":"999999999",
    --             "name":"XYZ EGYPT FOR TRADE"
    --          },
    --          "documentType":"I",
    --          "documentTypeVersion":"1.0",
    --          "dateTimeIssued":"2020-11-15T11:04:53Z",
    --          "taxpayerActivityCode":"1073",
    --          "internalID":"ZZZZ999",
    --          "purchaseOrderReference":"2009199918",
    --          "salesOrderReference":"",
    --          "payment":{
    --             "bankName":"",
    --             "bankAddress":"",
    --             "bankAccountNo":"",
    --             "bankAccountIBAN":"",
    --             "swiftCode":"",
    --             "terms":""
    --          },
    --          "delivery":{
    --             "approach":"",
    --             "packaging":"",
    --             "dateValidity":"",
    --             "exportPort":"",
    --             "countryOfOrigin":"EG",
    --             "grossWeight":0,
    --             "netWeight":0,
    --             "terms":""
    --          },
    --          "invoiceLines":[
    --             {
    --                "description":"CDM Widget 48GX99X12BA",
    --                "itemType":"GS1",
    --                "itemCode":"7622213335056",
    --                "unitType":"CS",
    --                "quantity":1.00,
    --                "unitValue":{
    --                   "currencySold":"EGP",
    --                   "amountEGP":588.67,
    --                   "amountSold":0,
    --                   "currencyExchangeRate":0
    --                },
    --                "salesTotal":588.67,
    --                "total":603.97,
    --                "valueDifference":0,
    --                "totalTaxableFees":0,
    --                "netTotal":529.8,
    --                "itemsDiscount":0,
    --                "discount":{
    --                   "rate":10.00,
    --                   "amount":58.87
    --                },
    --                "taxableItems":[
    --                   {
    --                      "taxType":"T1",
    --                      "amount":74.17,
    --                      "subType":"No sub",
    --                      "rate":14.00
    --                   }
    --                ],
    --                "internalCode":"9099994"
    --             }
    --          ],
    --          "totalSales":588.67,
    --          "totalSalesAmount":588.67,
    --          "totalDiscountAmount":58.87,
    --          "netAmount":529.80,
    --          "taxTotals":[
    --             {
    --                "taxType":"T1",
    --                "amount":74.17
    --             }
    --          ],
    --          "extraDiscountAmount":0,
    --          "totalItemsDiscountAmount":0,
    --          "totalAmount":603.97,
    --       }
    -- 

    -- Please include this JSON file as an attachment in your support email to Chilkat.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'c:/someDir/jsonToSignAndSend.json'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load input JSON file.'
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @cmsOptions
        EXEC @hr = sp_OADestroy @jsonSigningAttrs
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OASetProperty @json, 'EmitCompact', 1
    DECLARE @jsonToSign nvarchar(4000)
    EXEC sp_OAMethod @json, 'Emit', @jsonToSign OUT

    -- Create the CAdES-BES signature.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'

    -- Make sure we sign the utf-8 byte representation of the JSON string
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'

    -- Turn on verbose logging.
    EXEC sp_OASetProperty @crypt, 'VerboseLogging', 1

    DECLARE @sigBase64 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'SignStringENC', @sigBase64 OUT, @jsonToSign
    EXEC sp_OAGetProperty @crypt, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @cmsOptions
        EXEC @hr = sp_OADestroy @jsonSigningAttrs
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- The LastErrorText property still contains information when the method succeeds.
    -- Send the contents of this LastErrorText as an attachment in your support email to Chilkat.
    EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
    PRINT @sTmp0

    -- Insert the base64 signature into the JSON to be sent
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].signatureType', 'I'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].value', @sigBase64

    -- Wrap the JSON in  {"documents":[ ... ]}
    DECLARE @sbToSend int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbToSend OUT

    EXEC sp_OAMethod @sbToSend, 'Append', @success OUT, '{"documents":['
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    EXEC sp_OAMethod @sbToSend, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbToSend, 'Append', @success OUT, ']}'

    -- ------------------------------------------------------------------------
    -- Submit the signed JSON to Chilkat's URL for simply echoing back the contents of the HTTP POST it receives:
    -- 
    -- We just want to see what actually gets sent.  To do this, we can send to https://www.chilkatsoft.com/echoPostBody.asp
    -- which will echo in the response that data and headers it received.

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

    -- Also, set the SessionLogFilename property so we can see the exact bytes of the HTTP request sent from your end.
    -- Please include this session log file as an attachment in your support email to Chilkat.
    EXEC sp_OASetProperty @http, 'SessionLogFilename', 'c:/someDir/sessionLog.txt'

    DECLARE @resp int
    EXEC sp_OAMethod @sbToSend, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostJson2', @resp OUT, 'https://www.chilkatsoft.com/echoPostBody.asp', 'application/json; charset=utf-8', @sTmp0
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @cmsOptions
        EXEC @hr = sp_OADestroy @jsonSigningAttrs
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbToSend
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- Save the exact contents of Chilkat's response to a file.
    -- The server at https://www.chilkatsoft.com/echoPostBody.asp is simply echoing back the headers and POST body it received.
    -- Please include the responseBody.txt as an attachment in your support email to Chilkat.
    EXEC sp_OAMethod @resp, 'SaveBodyBinary', @success OUT, 'c:/someDir/responseBody.txt'


    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response status code: ' + @iTmp0

    EXEC @hr = sp_OADestroy @resp

    -- -------------------------------------------------
    -- In summary, please include the following in your support email to Chilkat
    -- 1. The original JSON file before signing and before canonicalization (i.e. c:/someDir/jsonToSignAndSend.json)
    -- 2. The contents of the LastErrorText for the successful call to SignStringENC.
    -- 3. The HTTP session log file (i.e. c:/someDir/sessionLog.txt)
    -- 4. The responseBody.txt file.

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @cmsOptions
    EXEC @hr = sp_OADestroy @jsonSigningAttrs
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbToSend
    EXEC @hr = sp_OADestroy @http


END
GO