SQL Server
SQL Server
Create Egypt ITIDA CAdES-BES Signature with Automatic JSON Canonicalization
See more Egypt ITIDA Examples
Demonstrates how to create a .p7s signature that fits Egypt's ITIDA requirements where Chilkat automatically does the ITIDA JSON canonicalization.Note: This example requires Chilkat v9.5.0.75 or greater.
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
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 <> 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
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
-- Pass a JSON document such as the following. Chilkat will do the ITIDA canonicalization.
-- (It is the canonicalized JSON that gets signed.)
-- {
-- "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,
-- }
--
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/itida/sdk.invoicing.eta.gov.eg/files/one-doc.json'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @json, '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
EXEC sp_OASetProperty @json, 'EmitCompact', 0
-- 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'
DECLARE @sigBase64 nvarchar(4000)
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'SignStringENC', @sigBase64 OUT, @sTmp0
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
PRINT 'Base64 signature:'
PRINT @sigBase64
-- Add the signature to the JSON.
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].signatureType', 'I'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signatures[0].value', @sigBase64
PRINT 'JSON with signature added:'
EXEC sp_OAMethod @json, 'Emit', @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
END
GO