SQL Server
SQL Server
Create XML Signature using Java KeyStore (.jks)
See more XML Digital Signatures Examples
Demonstrates how to create an XML digital signature using a certificate and private key from a Java KeyStore (.jks)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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
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.
-- 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
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'
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
EXEC @hr = sp_OACreate 'Chilkat.JavaKeyStore', @jks OUT
DECLARE @password nvarchar(4000)
SELECT @password = 'secret'
EXEC sp_OAMethod @jks, 'LoadFile', @success OUT, @password, 'qa_data/jks/test_secret.jks'
IF @success = 0
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 @hr = sp_OACreate 'Chilkat.CertChain', @chain OUT
EXEC sp_OAMethod @jks, 'CertChainAt', @success OUT, 0, @chain
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @jks, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @jks
EXEC @hr = sp_OADestroy @chain
RETURN
END
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @chain, 'CertAt', @success OUT, 0, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @chain, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @jks
EXEC @hr = sp_OADestroy @chain
EXEC @hr = sp_OADestroy @cert
RETURN
END
-- 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 @xml
EXEC @hr = sp_OADestroy @jks
EXEC @hr = sp_OADestroy @chain
EXEC @hr = sp_OADestroy @cert
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
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 @xml
EXEC @hr = sp_OADestroy @jks
EXEC @hr = sp_OADestroy @chain
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
RETURN
END
-- Everything's specified. Now create and insert the Signature
DECLARE @sbXml int
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 = 0
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @jks
EXEC @hr = sp_OADestroy @chain
EXEC @hr = sp_OADestroy @cert
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 @chain
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @sbXml
END
GO