SQL Server

Add a PFX Source File for S/MIME Decryption

Demonstrates the Chilkat Imap.AddPfxSourceFile method, which adds a PFX file as a source of certificates and private keys for S/MIME processing. The first argument is the PFX file path and the second is its password. Call it once for each additional source.

Note: The certificate path is relative to the application's current working directory. Supply the path to your own certificate file.

Background: This is the file-based form of AddPfxSourceBd. Registering the private keys needed to decrypt S/MIME mail lets Chilkat decrypt messages transparently during fetch. Add several sources if recipients' keys are spread across multiple PFX files. On Windows and macOS the system stores (and Keychain) are searched automatically in addition to the sources you add.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

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 @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Imap.AddPfxSourceFile method, which adds a PFX file as a source of
    --  certificates and private keys for S/MIME decryption.  The 1st argument is the PFX file path
    --  and the 2nd is its password.

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

    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993

    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'user@example.com', 'myPassword'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  The PFX password is not hard-coded in source.  Insert code here to obtain it from an
    --  interactive prompt, environment variable, or a secrets vault.
    DECLARE @pfxPassword nvarchar(4000)

    --  Add the PFX file as a source for S/MIME certificates and private keys.  Call once per source.
    EXEC sp_OAMethod @imap, 'AddPfxSourceFile', @success OUT, 'qa_data/smime.pfx', @pfxPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  Messages downloaded after this are automatically decrypted when a matching key is found.

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    EXEC @hr = sp_OADestroy @imap


END
GO