SQL Server
SQL Server
EuroCert Cloud Sign XML
See more Signing in the Cloud Examples
Demonstrates how to sign XML using the EuroCert cloud provider. The signing of the hash happens on a hardware token on the EuroCert server. 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 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
-- 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.
-- Note: Chilkat's PKCS11 implementation runs on Windows, Linux, Mac OS X, and other supported operating systems.
DECLARE @pkcs11 int
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 EuroCert Cloud PKCS11 driver.
-- If your application runs as a 64-bit process, use the path to the 64-bit ECSPKCS11.dll
EXEC sp_OASetProperty @pkcs11, 'SharedLibPath', 'C:\Users\Public\ECSIGNER\PKCS11\32\ECSPKCS11.dll'
-- At this time, we don't know what should be used for the PIN.
-- Perhaps it is documented online by EuroCert?
DECLARE @pin nvarchar(4000)
SELECT @pin = 'user:password'
DECLARE @userType int
SELECT @userType = 1
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
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
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
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
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.'
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