Enumerate Certificates in a PFX

See more PFX/P12 Examples

Demonstrates Pfx.CertAt, which copies the certificate at a zero-based index into a Cert object. The example enumerates all certificates in the PFX.

The file path is relative to the application's current working directory. An absolute path may also be used. Supply the path appropriate to your own environment.

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @pfx int
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  The file path is relative to the application's current working directory.  An absolute path
    --  may also be used.  Supply the path appropriate to your own environment.
    --  The PFX password should come from a secure source rather than being hard-coded.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'myPfxPassword'
    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/identity.pfx', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    --  Enumerate the certificates in the PFX by zero-based index.  Valid indexes are less than NumCerts.
    --  The certificate at index 0 is typically the certificate that has a private key, and the remaining
    --  certificates are the others in its certificate chain.
    DECLARE @numCerts int
    EXEC sp_OAGetProperty @pfx, 'NumCerts', @numCerts OUT
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @numCerts - 1
      BEGIN
        EXEC sp_OAMethod @pfx, 'CertAt', @success OUT, @i, @cert
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @pfx
            EXEC @hr = sp_OADestroy @cert
            RETURN
          END


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

    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @cert


END
GO