(SQL Server) PKCS11 Sign PDF using Certificate and Private Key on Smart Card / USB Token

See more PKCS11 Examples

Sample code showing how to use PKCS11 to sign a PDF 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 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.

    -- Note: Chilkat's PKCS11 implementation runs on Windows, Linux, Mac OS X, and other supported operating systems.

    DECLARE @pkcs11 int
    -- Use "Chilkat_9_5_0.Pkcs11" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pkcs11', @pkcs11 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @pkcs11, 'SharedLibPath', 'C:/Program Files (x86)/Gemalto/IDGo 800 PKCS#11/IDPrimePKCS1164.dll'
    DECLARE @pin nvarchar(4000)
    SELECT @pin = '0000'
    DECLARE @userType int
    SELECT @userType = 1

    -- Establish a PKCS11 logged-on session using the driver (.so, .dylib, or .dll) as specified in the SharedLibPath above.
    DECLARE @success int
    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.
    -- There are other ways to locate a certificate on the HSM.
    -- This example assumes there is a single certificate w/ private key.
    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 @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 PDF signing code is the same as for a cert obtained from any other source..

    DECLARE @pdf int
    -- Use "Chilkat_9_5_0.Pdf" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT

    -- Load a PDF to be signed.
    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/hello.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, '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 @pdf
        RETURN
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'page', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.y', 'top'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.x', 'left'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.fontScale', '10.0'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'signingAlgorithm', 'pss'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'hashAlgorithm', 'sha256'

    DECLARE @i int
    SELECT @i = 0
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[i]', 'Digitaly signed by: Xyz Widgets, Inc.'
    SELECT @i = @i + 1
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[i]', 'current_dt'
    SELECT @i = @i + 1
    EXEC sp_OASetProperty @json, 'I', @i
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appearance.text[i]', 'blah blah blah'

    -- The certificate is internally linked to the Pkcs11 object, which is currently in an authenticated session.
    EXEC sp_OAMethod @pdf, 'SetSigningCert', @success OUT, @cert

    EXEC sp_OAMethod @pdf, 'SignPdf', @success OUT, @json, 'qa_output/out.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, '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 @pdf
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- --------------------------------------------------------------------------

    -- 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 @pdf
        EXEC @hr = sp_OADestroy @json
        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 @pdf
        EXEC @hr = sp_OADestroy @json
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @pkcs11
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json


END
GO