(SQL Server) Sign XML (XAdES) in the Cloud using AWS KMS

See more Signing in the Cloud Examples

Demonstrates how to sign XML using AWS KMS. The signing of the hash happens in AWK KMS. 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 greater.

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.

    -- Create the XML to be signed.
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    DECLARE @success int
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '<?xml version="1.0" encoding="UTF-8" standalone="no" ?>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    <SOAP-ENV:Header>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '        <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>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    </SOAP-ENV:Header>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    <SOAP-ENV:Body xmlns:SOAP-SEC="http://schemas.xmlsoap.org/soap/security/2000-12" SOAP-SEC:id="Body">', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '        <z:FooBar xmlns:z="http://example.com" />', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '    </SOAP-ENV:Body>', @bCrlf
    EXEC sp_OAMethod @sbXml, 'AppendLine', @success OUT, '</SOAP-ENV:Envelope>', @bCrlf

    -- Load the certificate used for signing.  The certificate's private key is stored in AWS KMS
    -- However, we still need the certificate locally (without private key).
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @success int
    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 @sbXml
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Here's a screenshot of our private key AWS KMS:
    

    -- To sign using AWS KMS, 
    -- add the following lines of code to specify your AWS authentication credentials,
    -- and the ID of the KMS private key.
    DECLARE @jsonAwsKms int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonAwsKms OUT

    -- Set the "service" equal to "aws_kms" to tell Chilkat to use AWS KMS for signing.
    EXEC sp_OAMethod @jsonAwsKms, 'UpdateString', @success OUT, 'service', 'aws_kms'
    EXEC sp_OAMethod @jsonAwsKms, 'UpdateString', @success OUT, 'access_key', 'ACCESS_KEY'
    EXEC sp_OAMethod @jsonAwsKms, 'UpdateString', @success OUT, 'secret_key', 'SECRET_KEY'
    -- Make sure to specify the correct region for your case.
    EXEC sp_OAMethod @jsonAwsKms, 'UpdateString', @success OUT, 'region', 'us-west-2'
    -- In the above screenshot, our key ID is "187012e8-008f-4fc7-b100-5efe6146dff2".  You will use your key ID.
    EXEC sp_OAMethod @jsonAwsKms, 'UpdateString', @success OUT, 'key_id', '187012e8-008f-4fc7-b100-5efe6146dff2'

    EXEC sp_OAMethod @cert, 'SetCloudSigner', @success OUT, @jsonAwsKms

    -- Prepare for signing...
    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'

    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
    EXEC sp_OASetProperty @gen, 'X509Type', 'IssuerSerial'

    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 @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonAwsKms
        EXEC @hr = sp_OADestroy @gen
        RETURN
      END
    -- Everything's specified.  Now create and insert the Signature
    -- (The actual signing of the hash will happen on the AWS KMS server.)
    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @jsonAwsKms
        EXEC @hr = sp_OADestroy @gen
        RETURN
      END

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

    -- --------------------------------------------------------------------------------
    -- 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 @sbXml
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @jsonAwsKms
    EXEC @hr = sp_OADestroy @gen


END
GO