SQL Server
SQL Server
Create XML Digital Signature using Certificate on Smart Card
See more ScMinidriver Examples
Demonstrates how to load a certificate located on a smart card or USB token and then use it for creating an XML digital signature such that the cryptographic signing happens on the smart card.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.
DECLARE @scmd int
EXEC @hr = sp_OACreate 'Chilkat.ScMinidriver', @scmd OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Reader names (smart card readers or USB tokens) can be discovered
-- via List Readers or Find Smart Cards
DECLARE @readerName nvarchar(4000)
SELECT @readerName = 'SCM Microsystems Inc. SCR33x USB Smart Card Reader 0'
EXEC sp_OAMethod @scmd, 'AcquireContext', @success OUT, @readerName
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @scmd, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @scmd
RETURN
END
-- If successful, the name of the currently inserted smart card is available:
EXEC sp_OAGetProperty @scmd, 'CardName', @sTmp0 OUT
PRINT 'Card name: ' + @sTmp0
-- If desired, perform regular PIN authentication with the smartcard.
-- For more details about smart card PIN authentication, see the Smart Card PIN Authentication Example
DECLARE @retval int
EXEC sp_OAMethod @scmd, 'PinAuthenticate', @retval OUT, 'user', '000000'
IF @retval <> 0
BEGIN
PRINT 'PIN Authentication failed.'
END
-- You can find a cerficate using any of the following certificate parts:
-- "subjectDN" -- The full distinguished name of the cert.
-- "subjectDN_withTags" -- Same as above, but in a format that includes the subject part tags, such as the "CN=" in "CN=something"
-- "subjectCN" -- The common name part (CN) of the certificate's subject.
-- "serial" -- The certificate serial number.
-- "serial:issuerCN" -- The certificate serial number + the issuer's common name, delimited with a colon char.
-- These are the same certificate parts that can be retrieved by listing certificates on the smart card (or USB token).
-- See List Certificates on Smart Card Example
DECLARE @certPart nvarchar(4000)
SELECT @certPart = 'subjectCN'
DECLARE @partValue nvarchar(4000)
SELECT @partValue = 'PIVKey E42D60A759D39147A26E0AF2FB1183C5'
-- If the certificate is found, it is loaded into the cert object.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @scmd, 'FindCert', @success OUT, @certPart, @partValue, @cert
IF @success = 0
BEGIN
PRINT 'Failed to find the certificate.'
EXEC sp_OAMethod @scmd, 'DeleteContext', @success OUT
EXEC @hr = sp_OADestroy @scmd
EXEC @hr = sp_OADestroy @cert
RETURN
END
PRINT 'Successfully loaded the cert object from the smart card / USB token.'
-- Note: When successful, the cert object is internally linked to the ScMinidriver object's authenticated session.
-- The cert object can now be used to sign or do other cryptographic operations that occur on the smart card / USB token.
-- If your application calls PinDeauthenticate or DeleteContext, the cert will no longer be able to sign on the smart card
-- because the smart card ScMinidriver session will no longer be authenticated or deleted.
-- ------------------------------------------------------------------------------------------------------------
-- We have the certificate. Now let's do the XML digital signature..
-- Create the following XML to be signed:
-- <?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'
-- Prepare for signing...
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'
-- Note: Our certificate was loaded from the smartcard and is internally linked to the authenticated ScMinidriver session.
-- The signing will occur on the smart card using the chosen 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 @hr = sp_OADestroy @scmd
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_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 @scmd
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
-- ------------------------------------------------------------------------------------------------------------
-- Cleanup our ScMinidriver session...
-- When finished with operations that required authentication, you may if you wish, deauthenticate the session.
EXEC sp_OAMethod @scmd, 'PinDeauthenticate', @success OUT, 'user'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @scmd, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
-- Delete the context when finished with the card.
EXEC sp_OAMethod @scmd, 'DeleteContext', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @scmd, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @scmd
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @sbXml
END
GO