SQL Server

Load Signed and/or Encrypted Email and Unwrap Security Layers

See more Email Object Examples

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. 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 @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 @email int
    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.)
    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