SQL Server Requires Chilkat v11.0.0+
SQL Server
Sign SOAP XML using a wsse:SecurityTokenReference
See more XML Digital Signatures Examples
This example signs SOAP XML such that the KeyInfo in the XML Signature is a wsse:SecurityTokenReference to the X.509 certificate embedded elsewhere in the SOAP XML.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 @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.
-- To begin, we'll need a PFX containing a certificate and private key, and the SOAP XML to be signed.
-- Chilkat provides sample data at chilkatsoft.com and chilkatdownload.com, and our first step is to download.
-- -------------------------------------------------------------------------
-- Step 1: Get the SOAP XML template to be signed.
--
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/exampleData/wssSoapTemplate.xml', @sbXml
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
RETURN
END
-- The SOAP XML template contains this:
-- <?xml version="1.0" encoding="UTF8"?>
-- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
-- <SOAP-ENV:Header>
-- <wsse:Security xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
-- xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
-- xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"
-- xmlns:xenc="http://www.w3.org/2001/04/xmlenc#" SOAP-ENV:mustUnderstand="1">
-- <wsse:BinarySecurityToken
-- EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary"
-- ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509"
-- wsu:Id="x509cert00">BASE64_CERT</wsse:BinarySecurityToken>
-- </wsse:Security>
-- </SOAP-ENV:Header>
-- <SOAP-ENV:Body xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="TheBody">
-- <getVersion xmlns="http://msgsec.wssecfvt.ws.ibm.com"/>
-- </SOAP-ENV:Body>
-- </SOAP-ENV:Envelope>
--
-- -------------------------------------------------------------------------
-- Step 2: Get the test certificate and private key stored in a .pfx
--
DECLARE @pfxData int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @pfxData OUT
EXEC sp_OAMethod @http, 'QuickGetBd', @success OUT, 'http://chilkatdownload.com/example_data/testcertificate.pfx', @pfxData
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @pfxData
RETURN
END
DECLARE @pfx int
EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT
DECLARE @password nvarchar(4000)
SELECT @password = 'test'
EXEC sp_OAMethod @pfxData, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @pfx, 'LoadPfxEncoded', @success OUT, @sTmp0, 'base64', @password
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @pfxData
EXEC @hr = sp_OADestroy @pfx
RETURN
END
-- -------------------------------------------------------------------------
-- Step 3: Get the certificate from the PFX.
--
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @pfx, 'CertAt', @success OUT, 0, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @pfxData
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @cert
RETURN
END
-- -------------------------------------------------------------------------
-- Step 4: Replace "BASE64_CERT" with the actual base64 encoded certificate.
--
DECLARE @bdCert int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdCert OUT
EXEC sp_OAMethod @cert, 'ExportCertDerBd', @success OUT, @bdCert
DECLARE @numReplaced int
EXEC sp_OAMethod @bdCert, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @sbXml, 'Replace', @numReplaced OUT, 'BASE64_CERT', @sTmp0
-- -------------------------------------------------------------------------
-- Step 5: Build the wsse:SecurityTokenReference XML.
-- This will be the CustomKeyInfoXml (see below).
--
DECLARE @refXml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @refXml OUT
EXEC sp_OASetProperty @refXml, 'Tag', 'wsse:SecurityTokenReference'
EXEC sp_OAMethod @refXml, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'URI', '#x509cert00'
EXEC sp_OAMethod @refXml, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'ValueType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509'
-- The above lines of code builds the following XML:
-- <wsse:SecurityTokenReference>
-- <wsse:Reference URI="#x509cert00" ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509" />
-- </wsse:SecurityTokenReference>
--
EXEC sp_OASetProperty @refXml, 'EmitXmlDecl', 0
EXEC sp_OAMethod @refXml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- -------------------------------------------------------------------------
-- Step 6: Setup the XML Digital Signature Generator and add the XML Signature.
--
DECLARE @gen int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT
EXEC sp_OASetProperty @gen, 'SigLocation', 'SOAP-ENV:Envelope|SOAP-ENV:Header|wsse:Security'
EXEC sp_OASetProperty @gen, 'SignedInfoPrefixList', 'wsse SOAP-ENV'
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'TheBody', 'sha1', 'EXCL_C14N', '', ''
EXEC sp_OASetProperty @gen, 'KeyInfoType', 'Custom'
EXEC sp_OASetProperty @refXml, 'EmitCompact', 1
EXEC sp_OAMethod @refXml, 'GetXml', @sTmp0 OUT
EXEC sp_OASetProperty @gen, 'CustomKeyInfoXml', @sTmp0
EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1
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 @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @pfxData
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @bdCert
EXEC @hr = sp_OADestroy @refXml
EXEC @hr = sp_OADestroy @gen
RETURN
END
-- Examine the signed XML
EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Pretty-printed, the XML signature looks as shown here:
-- (The exact XML signature is shown below. Pretty-printing invalidates the XML signature.)
-- <?xml version="1.0" encoding="UTF8" ?>
-- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
-- <SOAP-ENV:Header>
-- <wsse:Security xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#" SOAP-ENV:mustUnderstand="1">
-- <wsse:BinarySecurityToken EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary" ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509" wsu:Id="x509cert00">MIID...</wsse:BinarySecurityToken>
-- <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
-- <ds:SignedInfo>
-- <ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
-- <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="wsse SOAP-ENV" />
-- </ds:CanonicalizationMethod>
-- <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256" />
-- <ds:Reference URI="#TheBody">
-- <ds:Transforms>
-- <ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#" />
-- </ds:Transforms>
-- <ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" />
-- <ds:DigestValue>VhsSnaEAFsY0OYegKQh99v9csXg=</ds:DigestValue>
-- </ds:Reference>
-- </ds:SignedInfo>
-- <ds:SignatureValue>Ynp3H4rtzpXIh4TaVxkpEkS1bMCCu672aeCzUOzheNNfnpmLsCZz3+zQjMBbchPggCayC5ihpEdhRe3XvPXjPXXAgxDP4mic091QPmjHlmUcu8yqRKfxnPtD35nqaxDtCYw+jGIzj+ch094vA4RPCfY8JQnb1mpy1ZjjsMW8741CIh1epbsd/0bZt6tfINUQ37seg07yvLbCJZ/Zf+h8FlFryQk6lHTTeZl/GfQ9NlDBcShby3x8Hc1KwW++zFqEA7G783R9AYPYn3fWTOBhYk5gkgFc+HaPRLR/L0Bp7ZPbmOR/iZQ+HK4W672tTdN/R2GdN7/deV7QTp2DYK1Z8w==</ds:SignatureValue>
-- <ds:KeyInfo>
-- <wsse:SecurityTokenReference>
-- <wsse:Reference URI="#x509cert00" ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509" />
-- </wsse:SecurityTokenReference>
-- </ds:KeyInfo>
-- </ds:Signature>
-- </wsse:Security>
-- </SOAP-ENV:Header>
-- <SOAP-ENV:Body xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="TheBody">
-- <getVersion xmlns="http://msgsec.wssecfvt.ws.ibm.com" />
-- </SOAP-ENV:Body>
-- </SOAP-ENV:Envelope>
--
-- --------------------------------------------------------------------------------------------
-- This is the XML signature, which is also available at https://www.chilkatsoft.com/exampleData/signedSoapBinarySecurityToken.xml
--
-- <?xml version="1.0" encoding="UTF8"?>
-- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
-- <SOAP-ENV:Header>
-- <wsse:Security xmlns:ds="http://www.w3.org/2000/09/xmldsig#"
-- xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"
-- xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"
-- xmlns:xenc="http://www.w3.org/2001/04/xmlenc#" SOAP-ENV:mustUnderstand="1">
-- <wsse:BinarySecurityToken
-- EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary"
-- ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509"
-- wsu:Id="x509cert00">MIIDg...</wsse:BinarySecurityToken>
-- <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"><InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="wsse SOAP-ENV"/></ds:CanonicalizationMethod><ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/><ds:Reference URI="#TheBody"><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>VhsSnaEAFsY0OYegKQh99v9csXg=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>Ynp3H4rtzpXIh4TaVxkpEkS1bMCCu672aeCzUOzheNNfnpmLsCZz3+zQjMBbchPggCayC5ihpEdhRe3XvPXjPXXAgxDP4mic091QPmjHlmUcu8yqRKfxnPtD35nqaxDtCYw+jGIzj+ch094vA4RPCfY8JQnb1mpy1ZjjsMW8741CIh1epbsd/0bZt6tfINUQ37seg07yvLbCJZ/Zf+h8FlFryQk6lHTTeZl/GfQ9NlDBcShby3x8Hc1KwW++zFqEA7G783R9AYPYn3fWTOBhYk5gkgFc+HaPRLR/L0Bp7ZPbmOR/iZQ+HK4W672tTdN/R2GdN7/deV7QTp2DYK1Z8w==</ds:SignatureValue><ds:KeyInfo><wsse:SecurityTokenReference><wsse:Reference URI="#x509cert00" ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509" /></wsse:SecurityTokenReference></ds:KeyInfo></ds:Signature></wsse:Security>
-- </SOAP-ENV:Header>
-- <SOAP-ENV:Body xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="TheBody">
-- <getVersion xmlns="http://msgsec.wssecfvt.ws.ibm.com"/>
-- </SOAP-ENV:Body>
-- </SOAP-ENV:Envelope>
--
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @pfxData
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @bdCert
EXEC @hr = sp_OADestroy @refXml
EXEC @hr = sp_OADestroy @gen
END
GO