(SQL Server) Decrypt S/MIME and Verify S/MIME Signatures

SQL Server example to decrypt S/MIME and verify S/MIME signatures. The S/MIME is unwrapped to get the original MIME prior to signing/encrypting.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @mime int
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load an S/MIME message from a file:
    EXEC sp_OAMethod @mime, 'LoadMimeFile', @success OUT, 'signedMime.txt'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- The ContainsEncryptedParts/ContainsSignedParts methods
    -- can be called to determine if the MIME is encrypted and/or signed:
    DECLARE @isEncrypted int
    EXEC sp_OAMethod @mime, 'ContainsEncryptedParts', @isEncrypted OUT
    DECLARE @isSigned int
    EXEC sp_OAMethod @mime, 'ContainsSignedParts', @isSigned OUT

    -- We don't want the "unwrap extras".  You'll see what those
    -- are in a few moments...
    EXEC sp_OASetProperty @mime, 'UnwrapExtras', 0

    -- To verify the signature, call UnwrapSecurity.  This will
    -- verify the signature(s) and decrypt the S/MIME and restore
    -- the MIME to the unsigned/unencrypted state.
    -- The results of what was found are present in the MIME object's
    -- properties, as well as extra header fields that are added
    -- to the unwrapped MIME.
    EXEC sp_OAMethod @mime, 'UnwrapSecurity', @success OUT
    IF @success <> 1
      BEGIN
        -- UnwrapSecurity returns 1 if all signatures were
        -- verified and all parts decrypted.
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- The mime.UnwrapExtras property controls whether or not
    -- these additional fields are added to the unwrapped MIME:
    -- 
    -- X-NumPartsSigned: 1
    -- X-SignaturesValid: yes
    -- X-NumPartsEncrypted: 1
    -- X-Decrypted: no
    -- 
    -- The X-NumPartsSigned/X-SignaturesValid headers are added
    -- if the MIME was signed.  
    -- 
    -- The X-NumPartsEncrypted/X-Decrypted headers are added
    -- if the MIME was encrypted.
    -- 

    -- If the MIME was signed, get the certificate used for signing.
    IF @isSigned = 1
      BEGIN
        DECLARE @signerCert int
        EXEC @hr = sp_OACreate 'Chilkat.Cert', @signerCert OUT

        -- The NumSignerCerts property indicates how many certificates
        -- were used for signing.  This example will assume the value is 1.
        -- To get the 1st signer cert, call LastSignerCert with an index of 0:
        EXEC sp_OAMethod @mime, 'LastSignerCert', @success OUT, 0, @signerCert
        IF @success = 1
          BEGIN

            EXEC sp_OAGetProperty @signerCert, 'SubjectCN', @sTmp0 OUT
            PRINT '**** Signer Cert: ' + @sTmp0
          END
      END

    -- If the MIME was encrypted, get the certificate used for decryption
    IF @isEncrypted = 1
      BEGIN
        -- The NumDecryptCerts property indicates how many certificates
        -- were used for decrypting.  This example will assume the value is 1.
        -- To get the 1st decrypt cert, call LastDecryptCert with an index of 0:
        DECLARE @decryptCert int
        EXEC @hr = sp_OACreate 'Chilkat.Cert', @decryptCert OUT

        EXEC sp_OAMethod @mime, 'LastDecryptCert', @success OUT, 0, @decryptCert
        IF @success = 1
          BEGIN

            EXEC sp_OAGetProperty @decryptCert, 'SubjectCN', @sTmp0 OUT
            PRINT '**** Decrypt Cert: ' + @sTmp0
          END
      END

    -- Display the unwrapped MIME:
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- Save the unwrapped MIME to a file:
    EXEC sp_OAMethod @mime, 'SaveMime', @success OUT, 'unwrappedMime.txt'

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @signerCert
    EXEC @hr = sp_OADestroy @decryptCert


END
GO