(SQL Server) Open Smartcard Certificate Store (or from USB Token)

Demonstrates how to open the certificate store of the smart card currently in the reader (or the USB token). Iterates over the certs found on the smartcard.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @certStore int
    -- Use "Chilkat_9_5_0.CertStore" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CertStore', @certStore OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Access the certificates on the smart card or USB token via the Chilkat certificate store class.
    -- Note: Always pass the empty string to OpenSmartcard.

    -- ---------------------------------------------------------------------------------------------------------
    -- The following is true only for Chilkat v10.1.1 and earlier:
    -- Also, the Chilkat CertStore class can only use MS CNG or CryptoAPI.
    -- Some smartcard/USB token drivers only support PKCS11 or ScMinidriver.
    -- You may get better results using Chilkat.Cert.LoadFromSmartcard because
    -- Cert.LoadFromSmartcard can automatically detect and utilize PKCS11, ScMinidriver, CNG, and CryptoAPI.
    -- ---------------------------------------------------------------------------------------------------------
    -- Starting in Chilkat versions after v10.1.1, OpenSmartcard also works with
    -- Apple Keychain and PKCS11 drivers on Windows, Linux, and MacOS.
    -- ---------------------------------------------------------------------------------------------------------

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

    -- Iterate over certificates on the smartcard.
    DECLARE @i int
    SELECT @i = 0
    DECLARE @numCerts int
    EXEC sp_OAGetProperty @certStore, 'NumCertificates', @numCerts OUT

    PRINT 'numCerts = ' + @numCerts

    WHILE (@i < @numCerts)
      BEGIN
        DECLARE @cert int
        EXEC sp_OAMethod @certStore, 'GetCertificate', @cert OUT, @i
        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @cert, 'SerialNumber', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAMethod @cert, 'IsRsa', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            PRINT 'key type is RSA'
          END
        EXEC sp_OAMethod @cert, 'IsEcdsa', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            PRINT 'key type is ECDSA'
          END

        EXEC sp_OAMethod @cert, 'HasPrivateKey', @iTmp0 OUT
        PRINT 'has private key: ' + @iTmp0

        PRINT '----'
        EXEC @hr = sp_OADestroy @cert

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @certStore


END
GO