POP3 Read S/MIME Encrypted Email

Read S/MIME encrypted email.

Download Chilkat Email ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    --  The mailman object is used for receiving (POP3)
    --  and sending (SMTP) email.
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan2', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Any string argument automatically begins the 30-day trial.
    DECLARE @success int

    EXEC sp_OAMethod @mailman, 'UnlockComponent', @success OUT, '30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  Set the POP3 server's hostname
    EXEC sp_OASetProperty @mailman, 'MailHost', 'mail.chilkatsoft.com'

    --  Set the POP3 login/password.
    EXEC sp_OASetProperty @mailman, 'PopUsername', 'myLogin'
    EXEC sp_OASetProperty @mailman, 'PopPassword', 'myPassword'

    --  If running on Microsoft Windows, the Chilkat mailman
    --  will automatically search the registry-based certificate stores
    --  for matching certificates and private keys required for
    --  decryption.  However, on Linux, MAX OS X, and other
    --  operating systems, no such thing exists.  Therefore, you'll
    --  need to specify one or more PFX (.p12 / .pfx) files as
    --  sources for locating the certs required for decyrption.
    EXEC sp_OAMethod @mailman, 'AddPfxSourceFile', @success OUT, '/pfxFiles/certs_and_keys_1.pfx', 'pfxPassword1'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END
    EXEC sp_OAMethod @mailman, 'AddPfxSourceFile', @success OUT, '/pfxFiles/certs_and_keys_2.pfx', 'pfxPassword2'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END
    --  ...
    --  Note: On MS Windows, it is not required to provide PFX sources
    --  if the needed certs and private keys are already installed
    --  on the system (in the registry-based certificate stores).

    DECLARE @bundle int

    --  Copy the all email from the user's POP3 mailbox
    --  into a bundle object.  The email remains on the server.
    EXEC sp_OAMethod @mailman, 'CopyMail', @bundle OUT
    IF @bundle Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  S/MIME security envelopes are automatically "unwrapped"
    --  when a message is retrieved from the server. Signed emails are automatically verified, and
    --  encrypted emails are automatically decrypted, restoring the email to the original state before
    --  signing and/or encrypting. Information about the signing and encrypting certificates can be
    --  retrieved from the Email object (methods: GetSignedByCert, GetEncryptedByCert;
    --  properties: SignedBy, EncryptedBy, SignaturesValid, Decrypted, ReceivedSigned,
    --  ReceivedEncrypted).

    --  Loop over the bundle,
    DECLARE @i int

    EXEC sp_OAGetProperty @bundle, 'MessageCount', @iTmp0 OUT
    SELECT @i = 0
    WHILE @i <= @iTmp0 - 1
      BEGIN
        DECLARE @email int

        EXEC sp_OAMethod @bundle, 'GetEmail', @email OUT, @i

        EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT

        PRINT @sTmp0
        EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT

        PRINT @sTmp0

        --  At this point, if the email was signed and/or encrypted, it is already "unwrapped", i.e.
        --  the email is already decrypted and in a state as if it were never signed or encrypted.
        --  You may check to see if the email was received encrypted or signed, and if so,
        --  whether it was successfully unwrapped and who signed or encrypted it:
        EXEC sp_OAGetProperty @email, 'ReceivedEncrypted', @sTmp0 OUT
        IF @sTmp0 = 1
          BEGIN


            PRINT 'This email was encrypted when received.'
            EXEC sp_OAGetProperty @email, 'Decrypted', @sTmp0 OUT
            IF @sTmp0 = 1
              BEGIN

                PRINT 'This email was successfully decrypted.  It was encrypted by:'
                EXEC sp_OAGetProperty @email, 'EncryptedBy', @sTmp0 OUT

                PRINT @sTmp0
              END
            ELSE
              BEGIN

                PRINT 'This email was not decrypted.'
              END
          END
        EXEC sp_OAGetProperty @email, 'ReceivedSigned', @sTmp0 OUT
        IF @sTmp0 = 1
          BEGIN


            PRINT 'This email was signed when received.'
            EXEC sp_OAGetProperty @email, 'SignaturesValid', @sTmp0 OUT
            IF @sTmp0 = 1
              BEGIN

                PRINT 'The signature was verified.  It was signed by:'
                EXEC sp_OAGetProperty @email, 'SignedBy', @sTmp0 OUT

                PRINT @sTmp0
              END
            ELSE
              BEGIN

                PRINT 'The signature verification failed.'
              END
          END

        --  The email's body, HTML body, attachments, etc.
        --  are decrypted and available just like any non-encrypted email.


        PRINT '--'

        SELECT @i = @i + 1
      END


END
GO