SQL Server

Load .eml and Decrypt (smime.p7m)

Loads an encrypted email from a .eml file and decrypts using certificate w/ private key found in a .p12 (or .pfx) file.

The Content-Type and Content-Disposition email headers of the email to be decrypted look similar to this:

Content-Type: application/x-pkcs7-mime; smime-type=enveloped-data;
	name="smime.p7m"
Content-Disposition: attachment; filename="smime.p7m"

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
    -- 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

    -- Add a PFX (or .p12) to be used for decryption
    EXEC sp_OAMethod @email, 'AddPfxSourceFile', @success OUT, 'myCert.p12', 'passwordForP12'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Loading the .eml automatically decrypts.
    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'encrypted.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- The email now exists as it was prior to encryption.
    -- Your app may access the email's subject, body, attachments,
    -- etc. using the Chilkat Email API...

    -- Save the decrypted email:
    EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'decrypted.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @email


END
GO