(SQL Server) Create XML Signature using Java KeyStore (.jks)

Demonstrates how to create an XML digital signature using a certificate and private key from a Java KeyStore (.jks)

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
    DECLARE @iTmp0 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.

    -- The SOAP XML to be signed in this example contains the following:

    -- <?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>
    -- 

    -- Build the XML to sign.
    -- Use this online tool to generate the code from sample XML: 
    -- Generate Code to Create XML

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

    EXEC sp_OASetProperty @xml, 'Tag', 'SOAP-ENV:Envelope'
    DECLARE @success int
    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'

    -- Load a JavaKeyStore file containing the certificate + private key.
    DECLARE @jks int
    -- Use "Chilkat_9_5_0.JavaKeyStore" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JavaKeyStore', @jks OUT

    DECLARE @password nvarchar(4000)
    SELECT @password = 'secret'
    DECLARE @success int
    EXEC sp_OAMethod @jks, 'LoadFile', @success OUT, @password, 'qa_data/jks/test_secret.jks'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @jks, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @jks
        RETURN
      END

    -- Make sure we have a private key.
    EXEC sp_OAGetProperty @jks, 'NumPrivateKeys', @iTmp0 OUT
    IF @iTmp0 < 1
      BEGIN

        PRINT 'No private key available.'
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @jks
        RETURN
      END

    -- -------------------------------------------------------------------------
    -- Get the certificate chain associated with the 1st (and probably only) private key in the JKS.
    DECLARE @chain int
    EXEC sp_OAMethod @jks, 'GetCertChain', @chain OUT, 0
    EXEC sp_OAGetProperty @jks, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @jks, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @jks
        RETURN
      END

    DECLARE @cert int
    EXEC sp_OAMethod @chain, 'GetCert', @cert OUT, 0
    EXEC sp_OAGetProperty @chain, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @chain, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chain

        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @jks
        RETURN
      END
    EXEC @hr = sp_OADestroy @chain

    -- Verify again that this cert has a private key.
    EXEC sp_OAMethod @cert, 'HasPrivateKey', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Certificate has no associated private key.'
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @jks
        RETURN
      END

    -- Prepare for signing...
    -- Use this online tool to generate the following code from an already-signed XML sample: 
    -- Generate Code to Create an XML Signature
    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.
    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'

    -- Note: Because our certificate was loaded from a JKS which also contained the private key,
    -- Chilkat automatically knows and has the private key associated with the certificate.
    -- We set bUsePrivateKey to tell the SetX509Cert method to automatically use the private key
    -- associated with the certificate for signing.
    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 @xml
        EXEC @hr = sp_OADestroy @jks
        EXEC @hr = sp_OADestroy @gen
        RETURN
      END
    EXEC @hr = sp_OADestroy @cert

    -- 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_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 @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @jks
        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

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @jks
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @sbXml


END
GO