(SQL Server) Load Signed and/or Encrypted Email and Unwrap Security Layers

The LoadEml method loads an email (.eml file containing the MIME of an email) and automatically unwraps the digital signature and encryption security layers. A program can then examine the properties of the email object to see what was unwrapped.

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

    -- Provide PFX/.p12 files for any certs+keys needed for decryption.
    -- Signature verification does not need anything extra.  
    -- (Also, Chilkat will automatically find and use pre-installed certs + private keys needed for decryption
    -- on a Windows system.)
    DECLARE @success int
    EXEC sp_OAMethod @email, 'AddPfxSourceFile', @success OUT, '/pfxFiles/certs_and_keys_1.pfx', 'pfxPassword1'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END
    EXEC sp_OAMethod @email, 'AddPfxSourceFile', @success OUT, '/pfxFiles/certs_and_keys_2.pfx', 'pfxPassword2'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END
    -- ...

    -- Load a potentially signed and/or encrypted email.
    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'someDir/someEmail.eml'

    -- If the email was signed and/or encrypted, it was "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', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN


        PRINT 'This email was encrypted.'
        EXEC sp_OAGetProperty @email, 'Decrypted', @iTmp0 OUT
        IF @iTmp0 = 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', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN


        PRINT 'This email was signed.'
        EXEC sp_OAGetProperty @email, 'SignaturesValid', @iTmp0 OUT
        IF @iTmp0 = 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

    -- At this point, the contents of the email, including attachments, can be accessed normally..

    EXEC @hr = sp_OADestroy @email


END
GO