(SQL Server) Import a PFX/P12 into the Windows Certificate Stores

Demonstrates how to import the certificates contained in a .pfx/.p12 to the Windows certificate stores.

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 @primaryCert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @primaryCert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load a PFX file into a certificate object.
    -- The cert object will contain the certificate from the PFX that has a private key.
    -- The certs in the chain of authentication (if contained in the PFX) are also loaded,
    -- and can be accessed by getting the certificate chain (see below).
    -- If the PFX did not include the issuer certs in the chain of authentication, then Chilkat will
    -- automatically try to construct the issuer chain from the CA and intermedicate CA certs
    -- already installed on the Windows system.
    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'myPfxPassword'
    DECLARE @success int
    EXEC sp_OAMethod @primaryCert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/somePfx.p12', @pfxPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @primaryCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @primaryCert
        RETURN
      END

    DECLARE @certChain int
    EXEC sp_OAMethod @primaryCert, 'GetCertChain', @certChain OUT
    EXEC sp_OAGetProperty @primaryCert, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @primaryCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @primaryCert
        RETURN
      END

    -- If the certificate chain reaches the root CA cert, then the last cert in the chain
    -- is the root CA cert.
    DECLARE @chainReachesRoot int
    EXEC sp_OAGetProperty @certChain, 'ReachesRoot', @chainReachesRoot OUT
    IF @chainReachesRoot = 1
      BEGIN

        PRINT 'The certificate chain reaches the root CA cert.'
      END

    DECLARE @cert int

    DECLARE @i int
    SELECT @i = 0
    DECLARE @numCerts int
    EXEC sp_OAGetProperty @certChain, 'NumCerts', @numCerts OUT
    WHILE @i < @numCerts
      BEGIN
        EXEC sp_OAMethod @certChain, 'GetCert', @cert OUT, @i


        EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
        PRINT 'SubjectDN ' + @i + ': ' + @sTmp0


        EXEC sp_OAGetProperty @cert, 'IssuerDN', @sTmp0 OUT
        PRINT 'IssuerDN ' + @i + ': ' + @sTmp0

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

        SELECT @i = @i + 1
      END

    -- The primary cert having the private key will be imported into the Current User "My" certificate store.
    -- Any intermediate root certificates will be imported into certificate store for intermediate certificate authorities.
    -- The root CA cert will be imported into the Root CA cert store.

    -- Let's open each of these 3 certificate stores..
    DECLARE @certStoreCU int
    -- Use "Chilkat_9_5_0.CertStore" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CertStore', @certStoreCU OUT

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

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

    DECLARE @readOnlyFlag int
    SELECT @readOnlyFlag = 0

    -- "CurrentUser" and "My" are the exact keywords to select your user account's certificate store.
    EXEC sp_OAMethod @certStoreCU, 'OpenWindowsStore', @success OUT, 'CurrentUser', 'My', @readOnlyFlag
    IF @success = 0
      BEGIN

        PRINT 'Failed to open the CurrentUser/My certificate store for read/write.'
        EXEC @hr = sp_OADestroy @certChain

        EXEC @hr = sp_OADestroy @primaryCert
        EXEC @hr = sp_OADestroy @certStoreCU
        EXEC @hr = sp_OADestroy @certStoreCA
        EXEC @hr = sp_OADestroy @certStoreRootCA
        RETURN
      END

    -- Certificate store for intermediate certification authorities (CAs). 
    EXEC sp_OAMethod @certStoreCA, 'OpenWindowsStore', @success OUT, 'CurrentUser', 'CertificationAuthority', @readOnlyFlag
    IF @success = 0
      BEGIN

        PRINT 'Failed to open the CurrentUser/CertificationAuthority certificate store for read/write.'
        EXEC @hr = sp_OADestroy @certChain

        EXEC @hr = sp_OADestroy @primaryCert
        EXEC @hr = sp_OADestroy @certStoreCU
        EXEC @hr = sp_OADestroy @certStoreCA
        EXEC @hr = sp_OADestroy @certStoreRootCA
        RETURN
      END

    -- Certificate store for trusted root certification authorities (CAs). 
    EXEC sp_OAMethod @certStoreRootCA, 'OpenWindowsStore', @success OUT, 'CurrentUser', 'Root', @readOnlyFlag
    IF @success = 0
      BEGIN

        PRINT 'Failed to open the CurrentUser/Root certificate store for read/write.'
        EXEC @hr = sp_OADestroy @certChain

        EXEC @hr = sp_OADestroy @primaryCert
        EXEC @hr = sp_OADestroy @certStoreCU
        EXEC @hr = sp_OADestroy @certStoreCA
        EXEC @hr = sp_OADestroy @certStoreRootCA
        RETURN
      END

    -- Iterate over the certs in the chain and import each into the desired certificate store.
    DECLARE @allSuccess int
    SELECT @allSuccess = 1
    SELECT @i = 0
    WHILE @i < @numCerts
      BEGIN
        EXEC sp_OAMethod @certChain, 'GetCert', @cert OUT, @i
        IF @i = 0
          BEGIN
            -- Import the primary certificate into the CurrentUser/My certificate store.
            EXEC sp_OAMethod @certStoreCU, 'AddCertificate', @success OUT, @cert
            IF @success = 0
              BEGIN
                EXEC sp_OAGetProperty @certStoreCU, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                SELECT @allSuccess = 0
              END
          END
        ELSE
          BEGIN
            IF (@i = (@numCerts - 1)) and (@chainReachesRoot = 1)
              BEGIN
                -- Add the root CA certificate to the CurrentUser/Root certificate store.
                -- (Your application can obviously choose whether this should be done or not.  Perhaps you prompt the user.)

                -- Note: If the root CA cert is already present in the Windows certificate store, Windows will display
                -- a dialog to ask if it should be deleted.  Chilkat does not explicitly display dialogs.
                EXEC sp_OAMethod @certStoreRootCA, 'AddCertificate', @success OUT, @cert
                IF @success = 0
                  BEGIN
                    EXEC sp_OAGetProperty @certStoreRootCA, 'LastErrorText', @sTmp0 OUT
                    PRINT @sTmp0
                    SELECT @allSuccess = 0
                  END
              END
            ELSE
              BEGIN
                -- This is an intermediate CA certificate.
                EXEC sp_OAMethod @certStoreCA, 'AddCertificate', @success OUT, @cert
                IF @success = 0
                  BEGIN
                    EXEC sp_OAGetProperty @certStoreCA, 'LastErrorText', @sTmp0 OUT
                    PRINT @sTmp0
                    SELECT @allSuccess = 0
                  END
              END
          END

        IF @success = 0
          BEGIN

            PRINT 'Failed to import certificate.'
          END

        EXEC @hr = sp_OADestroy @cert

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @certChain


    PRINT 'allSuccess = ' + @allSuccess

    EXEC @hr = sp_OADestroy @primaryCert
    EXEC @hr = sp_OADestroy @certStoreCU
    EXEC @hr = sp_OADestroy @certStoreCA
    EXEC @hr = sp_OADestroy @certStoreRootCA


END
GO