(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

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    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

    --  Any string argument automatically begins the 30-day trial.
    DECLARE @success int
    EXEC sp_OAMethod @mime, 'UnlockComponent', @success OUT, '30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    --  Load an S/MIME message from a file:
    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
        IF Not (@signerCert Is NULL )
          BEGIN

            EXEC sp_OAGetProperty @signerCert, 'SubjectCN', @sTmp0 OUT
            PRINT '**** Signer Cert: ' + @sTmp0
          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
        IF Not (@encryptCert Is NULL )
          BEGIN

            EXEC sp_OAGetProperty @encryptCert, 'SubjectCN', @sTmp0 OUT
            PRINT '**** Encrypt 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


END
GO