SQL Server

Open Smartcard Certificate Store (or from USB Token)

See more Certificates Examples

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. SQL Server

-- 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 @success int
    SELECT @success = 0

    DECLARE @certStore int
    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.
    -- ---------------------------------------------------------------------------------------------------------

    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 @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @i int
    SELECT @i = 0
    DECLARE @numCerts int
    EXEC sp_OAGetProperty @certStore, 'NumCertificates', @numCerts OUT

    PRINT 'numCerts = ' + @numCerts

    WHILE (@i < @numCerts)
      BEGIN
        EXEC sp_OAMethod @certStore, 'GetCert', @success OUT, @i, @cert
        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 '----'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @certStore
    EXEC @hr = sp_OADestroy @cert


END
GO