(SQL Server) PKCS11 Find Specific Certificate on Smart Card or USB Token

See more PKCS11 Examples

This example code shows how to find a particular certificate on a smart card or USB token.

Note: This example requires Chilkat v9.5.0.88 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
    DECLARE @iTmp0 int
    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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Pkcs11', @pkcs11 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @pkcs11, 'SharedLibPath', 'aetpkss1.dll'

    DECLARE @success int
    EXEC sp_OAMethod @pkcs11, 'Initialize', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        RETURN
      END

    -- We need to call Discover to get the slot ID.
    -- (A slot is a smart card reader or USB token.)
    DECLARE @onlyTokensPresent int
    SELECT @onlyTokensPresent = 1
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAMethod @pkcs11, 'Discover', @success OUT, @onlyTokensPresent, @json
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Make sure we have at least one slot.
    EXEC sp_OAMethod @json, 'SizeOfArray', @iTmp0 OUT, 'slot'
    IF @iTmp0 <= 0
      BEGIN

        PRINT 'No occuplied slots.'
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Get the ID of the 1st slot
    DECLARE @slotID int
    EXEC sp_OAMethod @json, 'IntOf', @slotID OUT, 'slot[0].id'

    -- Open a session.
    DECLARE @readWrite int
    SELECT @readWrite = 1
    EXEC sp_OAMethod @pkcs11, 'OpenSession', @success OUT, @slotID, @readWrite
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pkcs11, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pkcs11
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Make it an authenticated session by calling Login.
    -- 
    -- If we don't authenticate, then we won't be able to see the private keys, and thus
    -- we won't know which certificates have an associated private key stored on the smart card.

    -- The smart card PIN is passed to the Login method.
    -- userType 1 indicates a "Normal User".
    DECLARE @userType int
    SELECT @userType = 1
    DECLARE @pin nvarchar(4000)
    SELECT @pin = '0000'
    EXEC sp_OAMethod @pkcs11, 'Login', @success OUT, @userType, @pin
    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 @json
        RETURN
      END

    -- --------------------------------------------------------------------------
    -- The FindCert method can find a particular certificate in a number of different ways.
    -- I'll demonstrate some common ways..

    -- 1) In many cases you'll be working with a smart card that contains one certificate that is to be used for 
    -- signing, and it is the certificate that is associated with the private key also stored on the smart card (or USB token).
    -- There may be other certificates on the card, but these are the issuer certificates in the chain of authentication.
    -- You're just interested in getting the certificate with the private key.
    -- You can do it like this:
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT

    -- Pass the keyword "privateKey" in the 1st argument, and an empty string in the 2nd arg.
    -- This returns the 1st certificate that has a private key.
    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.'
      END

    -- 2) Find a certificate by the Subject Common Name
    EXEC sp_OAMethod @pkcs11, 'FindCert', @success OUT, 'subjectCN', 'Chil application account (8c9be8e0-5544-4cd0-9062-290fbff353a7)', @cert
    IF @success = 1
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
        PRINT 'Found: ' + @sTmp0
      END
    ELSE
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
        PRINT 'Not found: ' + @sTmp0
      END

    -- 3) Find a certificate by hex serial number
    EXEC sp_OAMethod @pkcs11, 'FindCert', @success OUT, 'serial', '5087bf1feda006af54a02f23a851104948acc26f', @cert
    IF @success = 1
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
        PRINT 'Found: ' + @sTmp0
      END
    ELSE
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
        PRINT 'Not found: ' + @sTmp0
      END

    -- 4) Find a certificate by hex serial number and Issuer Common Name
    EXEC sp_OAMethod @pkcs11, 'FindCert', @success OUT, 'serial:issuerCN', '5087bf1feda006af54a02f23a851104948acc26f:Ibanity Production Third Party Application CA', @cert
    IF @success = 1
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
        PRINT 'Found: ' + @sTmp0
      END
    ELSE
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
        PRINT 'Not found: ' + @sTmp0
      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 @json
        EXEC @hr = sp_OADestroy @cert
        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 @json
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    PRINT 'Success.'

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


END
GO