SQL Server
SQL Server
PKCS11 XML Signature using Certificate and Private Key on Smart Card / USB Token
See more PKCS11 Examples
Sample code showing how to use PKCS11 to create an XML digital signature with a certificate and private key stored on a smart card or USB token.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
-- This example is tested on Windows using a Giesecke and Devrient Sm@rtCafe Expert 3.2 smart card.
-- You will use the DLL (or shared lib) provided by your smart card vendor, or a DLL compatible with your smart card.
-- On Windows, if the DLL is located in C:\Windows\System32, specify only the filename.
-- Otherwise provide the full path.
EXEC sp_OASetProperty @pkcs11, 'SharedLibPath', 'aetpkss1.dll'
-- Make it an authenticated session by calling Login.
DECLARE @userType int
SELECT @userType = 1
-- Make sure to use the correct PIN for your smart card..
DECLARE @pin nvarchar(4000)
SELECT @pin = '0000'
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
-- Get the certificate (on the smart card) that has a private key.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @pkcs11, 'FindCert', @success OUT, 'privateKey', '', @cert
IF @success = 1
BEGIN
EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
PRINT 'Cert with private key: ' + @sTmp0
END
ELSE
BEGIN
PRINT 'No certificates having a private key were found.'
EXEC sp_OAMethod @pkcs11, 'CloseSession', @success OUT
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