(SQL Server) IMAP Read Encrypted Email

See more IMAP Examples

Demonstrates how to read encrypted email from an IMAP mailbox.

Reading encrypted email works the same as reading non-encrypted email. If the required certificate and private key are available on the system (e.g., in the macOS Keychain or Windows Certificate Store), Chilkat will automatically locate them, decrypt the email, and handle the process seamlessly.

Information about the original encrypted state of the email is available after it has been downloaded and decrypted.

Note: This example requires Chilkat v11.0.0 or greater.

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 @success int
    SELECT @success = 0

    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.example2.com'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- We'll get the IMAP email account's password from the Apple Keychain or Windows Credentials Manager.
    -- See how we originally saved the email credentials to the Keychain here:
    -- Save Email Credentials in Apple Keychain or Windows Credentials Manager
    DECLARE @secrets int
    EXEC @hr = sp_OACreate 'Chilkat.Secrets', @secrets OUT

    -- On Windows, this is the Windows Credentials Manager
    -- On MacOS/iOS, it is the Apple Keychain
    EXEC sp_OASetProperty @secrets, 'Location', 'local_manager'

    -- Specify the name of the secret.
    -- service and username are required.
    -- appName and domain are optional.
    -- Note: The values are arbitrary and can be anything you want.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'appName', 'MyEmailApp'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'service', 'IMAP'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'domain', 'example2.com'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'username', 'jane@example2.com'

    DECLARE @password nvarchar(4000)
    EXEC sp_OAMethod @secrets, 'GetSecretStr', @password OUT, @json
    EXEC sp_OAGetProperty @secrets, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @secrets, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'jane@example2.com', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- This example: Send Encrypted Email using Certificate in Apple Keychain
    -- sent an email with the subject "This email is encrypted".
    -- Let's download an email with the word "encrypted" in the subject.

    DECLARE @messageSet int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT

    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'SUBJECT encrypted', 1, @messageSet
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @messageSet
        RETURN
      END

    EXEC sp_OAGetProperty @messageSet, 'Count', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No messages found.'
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @messageSet
        RETURN
      END

    -- Reading encrypted email works the same as reading non-encrypted email. 
    -- If the required certificate and private key are available on the system (e.g., in the macOS Keychain or Windows Certificate Store), 
    -- Chilkat will automatically locate them, decrypt the email, and handle the process seamlessly.

    DECLARE @uid int
    EXEC sp_OAMethod @messageSet, 'GetId', @uid OUT, 0

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OAMethod @imap, 'FetchEmail', @success OUT, 0, @uid, 1, @email
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @messageSet
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Here we can show if the email was received encrypted, if it was successfully decrypted, and
    -- which certificate was used to decrypt.

    EXEC sp_OAGetProperty @email, 'ReceivedEncrypted', @iTmp0 OUT
    PRINT 'Email received encrypted: ' + @iTmp0

    -- Was it successfully decrypted?

    EXEC sp_OAGetProperty @email, 'Decrypted', @iTmp0 OUT
    PRINT 'Successfully decrypted: ' + @iTmp0

    -- What cert was used to decrypt?

    EXEC sp_OAGetProperty @email, 'EncryptedBy', @sTmp0 OUT
    PRINT 'Encrypted by: ' + @sTmp0
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @email, 'LastDecryptCert', @success OUT, @cert
    IF @success <> 0
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
        PRINT 'Certificate DN: ' + @sTmp0
      END

    -- Show the decrypted email body.
    EXEC sp_OAGetProperty @email, 'Body', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @secrets
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @messageSet
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @cert


END
GO