(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 v10.1.2 or later because it uses the Secrets class.

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 @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    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
    DECLARE @success int
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example2.com'
    IF @success <> 1
      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
    -- Use "Chilkat_9_5_0.Secrets" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 <> 1
      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 <> 1
      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 sp_OAMethod @imap, 'Search', @messageSet OUT, 'SUBJECT encrypted', 1
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 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

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

        PRINT 'No messages found.'
        EXEC @hr = sp_OADestroy @messageSet

        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        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 sp_OAMethod @imap, 'FetchSingle', @email OUT, @uid, 1
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @messageSet

        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @secrets
        EXEC @hr = sp_OADestroy @json
        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 sp_OAMethod @email, 'GetEncryptedByCert', @cert OUT
    EXEC sp_OAGetProperty @email, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
        PRINT 'Certificate DN: ' + @sTmp0
        EXEC @hr = sp_OADestroy @cert

      END

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

    EXEC @hr = sp_OADestroy @messageSet

    EXEC @hr = sp_OADestroy @email

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

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @secrets
    EXEC @hr = sp_OADestroy @json


END
GO