SQL Server

Get the Certificate Used to Decrypt an Email

Demonstrates the Chilkat Email.LastDecryptCert method, which copies the certificate used to decrypt this email into a Cert object. It should be called after a successful decryption. This example loads an encrypted email and, if it was decrypted, reads the decrypting certificate's common name.

Background: When a message could be decrypted with one of several available private keys, it is often useful to know which certificate actually did the job — for auditing, for confirming the message was encrypted for the expected identity, or for logging. LastDecryptCert hands you that certificate as an object so you can inspect its subject, issuer, validity dates, and more.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the LastDecryptCert method, which copies the certificate used to decrypt
    --  this email into a Cert object.  Call it after a successful decryption.

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

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/encrypted.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    EXEC sp_OAGetProperty @email, 'ReceivedEncrypted', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'Decrypted', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN
            --  Get the certificate that was used to decrypt the email.
            DECLARE @cert int
            EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

            EXEC sp_OAMethod @email, 'LastDecryptCert', @success OUT, @cert
            IF @success = 1
              BEGIN

                EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
                PRINT 'Decrypted with certificate (CN): ' + @sTmp0
              END
          END
      END

    --  Note: The path "qa_data/..." is a relative local filesystem path,
    --  relative to the current working directory of the running application.

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @cert


END
GO