SQL Server
SQL Server
CADES-BES Signature using ePass2003 Token
See more Egypt ITIDA Examples
Demonstrates using a certificate and private key located on an ePass2003 USB token to create a CADES-BES signature.(Demonstrates how to create a .p7s signature that fits Egypt's ITIDA requirements where Chilkat automatically does the ITIDA JSON canonicalization.)
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 requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @scmd int
EXEC @hr = sp_OACreate 'Chilkat.ScMinidriver', @scmd OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Reader names (smart card readers or USB tokens) can be discovered
-- via List Readers or Find Smart Cards
DECLARE @readerName nvarchar(4000)
SELECT @readerName = 'FS USB Token 0'
EXEC sp_OAMethod @scmd, 'AcquireContext', @success OUT, @readerName
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @scmd, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @scmd
RETURN
END
-- If successful, the name of the currently inserted smart card is available:
EXEC sp_OAGetProperty @scmd, 'CardName', @sTmp0 OUT
PRINT 'Card name: ' + @sTmp0
-- If desired, perform regular PIN authentication with the smartcard.
-- For more details about smart card PIN authentication, see the Smart Card PIN Authentication Example
DECLARE @retval int
EXEC sp_OAMethod @scmd, 'PinAuthenticate', @retval OUT, 'user', '12345678'
IF @retval <> 0
BEGIN
PRINT 'PIN Authentication failed.'
END
-- You can find a cerficate using any of the following certificate parts:
-- "subjectDN" -- The full distinguished name of the cert.
-- "subjectDN_withTags" -- Same as above, but in a format that includes the subject part tags, such as the "CN=" in "CN=something"
-- "subjectCN" -- The common name part (CN) of the certificate's subject.
-- "serial" -- The certificate serial number.
-- "serial:issuerCN" -- The certificate serial number + the issuer's common name, delimited with a colon char.
-- These are the same certificate parts that can be retrieved by listing certificates on the smart card (or USB token).
-- See List Certificates on Smart Card Example
DECLARE @certPart nvarchar(4000)
SELECT @certPart = 'subjectCN'
DECLARE @partValue nvarchar(4000)
SELECT @partValue = 'Matt'
-- If the certificate is found, it is loaded into the cert object.
-- Note: We imported this certificate from a .p12/.pfx using code such as this Example to Import a .pfx/.p12 onto a Smart Card
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @scmd, 'FindCert', @success OUT, @certPart, @partValue, @cert
IF @success = 0
BEGIN
PRINT 'Failed to find the certificate.'
EXEC sp_OAMethod @scmd, 'DeleteContext', @success OUT
EXEC @hr = sp_OADestroy @scmd
EXEC @hr = sp_OADestroy @cert
RETURN
END
PRINT 'Successfully loaded the cert object from the smart card / USB token.'
-- Note: When successful, the cert object is internally linked to the ScMinidriver object's authenticated session.
-- The cert object can now be used to sign or do other cryptographic operations that occur on the smart card / USB token.
-- If your application calls PinDeauthenticate or DeleteContext, the cert will no longer be able to sign on the smart card
-- because the smart card ScMinidriver session will no longer be authenticated or deleted.
-- ------------------------------------------------------------------------------------------------------------
-- Here we have to code to create the CADES-BES signature using Chilkat Crypt2..
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
-- Tell the crypt class to use the cert on the ePass2003 token.
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 @scmd
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
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 @scmd
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
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 @scmd
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
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
-- ------------------------------------------------------------------------------------------------------------
-- Cleanup our ScMinidriver session...
-- When finished with operations that required authentication, you may if you wish, deauthenticate the session.
EXEC sp_OAMethod @scmd, 'PinDeauthenticate', @success OUT, 'user'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @scmd, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
-- Delete the context when finished with the card.
EXEC sp_OAMethod @scmd, 'DeleteContext', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @scmd, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @scmd
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @cmsOptions
EXEC @hr = sp_OADestroy @jsonSigningAttrs
EXEC @hr = sp_OADestroy @json
END
GO