(SQL Server) Sign XML (XAdES) using AWS CloudHSM

See more PKCS11 Examples

Demonstrates how to sign XML using AWS CloudHSM. The signing of the hash happens on a hardware token in AWS CloudHSM. Everything else involving the updating the XML to add the signature happens locally within Chilkat.

Note: This example requires Chilkat v9.5.0.96 or later.

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

    -- Note: Chilkat's PKCS11 implementation runs on Windows, Linux, Mac OS X, and other supported operating systems.

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

    -- Provide the path to the AWS CloudHSM PKCS11 driver.
    -- This example runs on Windows, so we'll provide the CloudHSM DLL.
    -- If your code runs on Linux, the CloudHSM driver might be at /opt/cloudhsm/lib/libcloudhsm_pkcs11.so
    EXEC sp_OASetProperty @pkcs11, 'SharedLibPath', 'C:\Program Files\Amazon\CloudHSM\lib\cloudhsm_pkcs11.dll'

    -- Your PIN should be a string containing your crypto user's login and password, with a colon char delimiting.
    -- See https://docs.aws.amazon.com/cloudhsm/latest/userguide/pkcs11-pin.html
    DECLARE @pin nvarchar(4000)
    SELECT @pin = 'user:password'
    DECLARE @userType int
    SELECT @userType = 1

    DECLARE @success int
    EXEC sp_OAMethod @pkcs11, 'QuickSession', @success OUT, @userType, @pin
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        RETURN
      END

    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_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/myCert.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Tell the certificate to link with the PKCS11 session.
    -- The cert's private key should be installed on the CloudHSM.
    -- If there are multiple private keys on the CloudHSM, then Chilkat will automatically
    -- locate and use the private key corresponding to the certificate.
    EXEC sp_OAMethod @cert, 'LinkPkcs11', @success OUT, @pkcs11
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- --------------------------------------------------------------------------
    -- At this point, we have the cert to be used for signing.
    -- Our XML signing code is the same as for a cert obtained from any other source..

    -- First generate the following XML to be signed:
    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    -- <?xml version="1.0" encoding="UTF-8" standalone="no" ?>
    -- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    --     <SOAP-ENV:Header>
    --         <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" SOAP-ENV:mustUnderstand="1"></wsse:Security>
    --     </SOAP-ENV:Header>
    --     <SOAP-ENV:Body xmlns:SOAP-SEC="http://schemas.xmlsoap.org/soap/security/2000-12" SOAP-SEC:id="Body">
    --         <z:FooBar xmlns:z="http://example.com" />
    --     </SOAP-ENV:Body>
    -- </SOAP-ENV:Envelope>

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

    EXEC sp_OASetProperty @xml, 'Tag', 'SOAP-ENV:Envelope'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:SOAP-ENV', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Header|wsse:Security', 1, 'xmlns:wsse', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Header|wsse:Security', 1, 'SOAP-ENV:mustUnderstand', '1'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body', 1, 'xmlns:SOAP-SEC', 'http://schemas.xmlsoap.org/soap/security/2000-12'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body', 1, 'SOAP-SEC:id', 'Body'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|z:FooBar', 1, 'xmlns:z', 'http://example.com'

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

    -- Indicate where the Signature will be inserted.
    EXEC sp_OASetProperty @gen, 'SigLocation', 'SOAP-ENV:Envelope|SOAP-ENV:Header|wsse:Security'

    -- Add a reference to the fragment of the XML to be signed.

    -- Note: "Body" refers to the XML element having an "id" equal to "Body", where "id" is case insensitive
    -- and where any namespace might qualify the attribute.  In this case, the SOAP-ENV:Body fragment is signed
    -- NOT because the tag = "Body", but because it has SOAP-SEC:id="Body"
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'Body', 'sha1', 'EXCL_C14N', '', ''

    -- (You can read about the SignedInfoPrefixList in the online reference documentation.  It's optional..)
    EXEC sp_OASetProperty @gen, 'SignedInfoPrefixList', 'wsse SOAP-ENV'

    -- Provide the private key for signing via the certificate, and indicate that
    -- we want the base64 of the certificate embedded in the KeyInfo.
    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
    EXEC sp_OASetProperty @gen, 'X509Type', 'Certificate'

    DECLARE @bUsePrivateKey int
    SELECT @bUsePrivateKey = 1
    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, @bUsePrivateKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAMethod @pkcs11, 'CloseSession', @success OUT
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @gen
        RETURN
      END

    -- Everything's specified.  Now create and insert the Signature
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    EXEC sp_OASetProperty @xml, 'EmitCompact', 1
    EXEC sp_OAMethod @xml, 'GetXmlSb', @success OUT, @sbXml

    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAMethod @pkcs11, 'CloseSession', @success OUT
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- Examine the XML with the digital signature inserted
    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- --------------------------------------------------------------------------

    -- Revert to an unauthenticated session by calling Logout.
    EXEC sp_OAMethod @pkcs11, 'Logout', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAMethod @pkcs11, 'CloseSession', @success OUT
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    -- When finished, close the session.
    -- It is important to close the session (memory leaks will occur if the session is not properly closed).
    EXEC sp_OAMethod @pkcs11, 'CloseSession', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END


    PRINT 'Success.'

    -- --------------------------------------------------------------------------------
    -- Also see Chilkat's Online WSDL Code Generator
    -- to generate code and SOAP Request and Response XML for each operation in a WSDL.
    -- --------------------------------------------------------------------------------

    EXEC @hr = sp_OADestroy @pkcs11
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @sbXml


END
GO