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

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
    DECLARE @sTmp0 nvarchar(4000)
    -- 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_9_5_0.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load an S/MIME message from a file:
    DECLARE @success int
    EXEC sp_OAMethod @mime, 'LoadMimeFile', @success OUT, 'signedMime.txt'
    IF @success <> 1
      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
        -- 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 GetSignerCert with an index of 0:
        DECLARE @signerCert int
        EXEC sp_OAMethod @mime, 'GetSignerCert', @signerCert OUT, 0
        EXEC sp_OAGetProperty @mime, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            EXEC sp_OAGetProperty @signerCert, 'SubjectCN', @sTmp0 OUT
            PRINT '**** Signer Cert: ' + @sTmp0
            EXEC @hr = sp_OADestroy @signerCert

          END
      END

    -- If the MIME was encrypted, get the certificate used for encryption:
    IF @isEncrypted = 1
      BEGIN
        -- The NumEncryptCerts property indicates how many certificates
        -- were used for encrypting.  This example will assume the value is 1.
        -- To get the 1st encrypt cert, call GetEncryptCert with an index of 0:
        DECLARE @encryptCert int
        EXEC sp_OAMethod @mime, 'GetEncryptCert', @encryptCert OUT, 0
        EXEC sp_OAGetProperty @mime, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            EXEC sp_OAGetProperty @encryptCert, 'SubjectCN', @sTmp0 OUT
            PRINT '**** Encrypt Cert: ' + @sTmp0
            EXEC @hr = sp_OADestroy @encryptCert

          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


END
GO