SQL Server
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 SQL Server Downloads
-- 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 @success int
SELECT @success = 0
-- 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.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @crypt, 'VerboseLogging', 1
DECLARE @cert int
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..
EXEC sp_OAMethod @cert, 'LoadFromSmartcard', @success OUT, ''
IF @success = 0
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 = 0
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
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
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
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
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
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 @jsonStr nvarchar(4000)
EXEC sp_OAMethod @sbToSend, 'GetAsString', @jsonStr OUT
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', 'https://www.chilkatsoft.com/echoPostBody.asp', @jsonStr, 'utf-8', 'application/json; charset=utf-8', @resp
IF @success = 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
EXEC @hr = sp_OADestroy @resp
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
-- -------------------------------------------------
-- 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
EXEC @hr = sp_OADestroy @resp
END
GO