(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 ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @scmd int
    -- Use "Chilkat_9_5_0.ScMinidriver" for versions of Chilkat < 10.0.0
    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'
    DECLARE @success int
    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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.XmlDSigGen" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    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