SQL Server

Get Certificates from .p12 / .pfx

See more PFX/P12 Examples

A PKCS12 (.p12 / .pfx) is a container for holding a certificate, its private key, and the certs in the chain of authentication up to and possibly including the root CA cert. A .p12 is not required to contain certain things. It will contain whatever the creator of the .p12 decided to include. It's possible to contain just a private key, just a cert, many certs without private keys, or many certs with many private keys. Usually, a .p12 contains one certificate, its associated private key, and certificates in the chain of authentication. 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 @pfx int
    EXEC @hr = sp_OACreate 'Chilkat.Pfx', @pfx OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/pfx/test.pfx', 'pfx_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Iterate over the certs contained in the PFX
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @numCerts int
    EXEC sp_OAGetProperty @pfx, 'NumCerts', @numCerts OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numCerts
      BEGIN

        EXEC sp_OAMethod @pfx, 'CertAt', @success OUT, @i, @cert



        PRINT '--- ' + @i + ' ---'
        EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
        PRINT @sTmp0
        -- Is this a root cert, or self-signed?

        EXEC sp_OAGetProperty @cert, 'IsRoot', @iTmp0 OUT
        PRINT 'Root: ' + @iTmp0

        EXEC sp_OAGetProperty @cert, 'SelfSigned', @iTmp0 OUT
        PRINT 'Self-Signed: ' + @iTmp0

        -- If this certificate is not the root (self-signed), then get the issuer.
        -- If the issuing certificate is contained in the PFX, then it will be found here..
        EXEC sp_OAGetProperty @cert, 'SelfSigned', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN
            DECLARE @issuer int
            EXEC sp_OAMethod @cert, 'FindIssuer', @issuer OUT
            EXEC sp_OAGetProperty @cert, 'LastMethodSuccess', @iTmp0 OUT
            IF @iTmp0 = 0
              BEGIN

                PRINT 'Issuer not found.'
              END
            ELSE
              BEGIN

                EXEC sp_OAGetProperty @issuer, 'SubjectDN', @sTmp0 OUT
                PRINT 'Issuer: ' + @sTmp0
                EXEC @hr = sp_OADestroy @issuer

              END
          END
        SELECT @i = @i + 1
      END

    -- Usually, the user certificate is at index 0, its issuer is at index 1, etc. until we get to the root certificate.

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


END
GO