(SQL Server) Iterate over Certificates on Connected Smart Cards and USB Tokens

See more Cert Store Examples

Demonstrates how to iterate over the certificates on connected smart cards and USB tokens.

Note: Requires Chilkat v10.1.2 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)
    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

    -- Detects connected smart cards and USB tokens
    -- and loads the certificate store object with the certificates found.
    -- Works on Windows, MacOS, iOS, and Linux.
    -- Note: If something is not detected, contact info@chilkatsoft.com
    -- for help or for workarounds.
    DECLARE @argNotUsed nvarchar(4000)
    SELECT @argNotUsed = ''
    DECLARE @success int
    EXEC sp_OAMethod @certStore, 'OpenSmartcard', @success OUT, @argNotUsed
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @certStore, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @certStore
        RETURN
      END

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

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

    WHILE @i < @numCerts
      BEGIN
        -- Load the cert object with the Nth certificate found in the connected HSMs
        EXEC sp_OAMethod @certStore, 'GetCert', @success OUT, @i, @cert

        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
        PRINT @i + ': ' + @sTmp0
        SELECT @i = @i + 1
      END

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


END
GO