SQL Server

Get the Signer Certificate of a Signed Email

Demonstrates the Chilkat Email.LastSignerCert method, which copies the certificate for the signer at a given zero-based index into a Cert object. Most signed emails have one signer (index 0), but a message can have more than one. This example loads a signed email and reads the first signer's common name.

Background: Confirming that a signature is valid (see SignaturesValid) tells you the content wasn't altered, but not who signed it. LastSignerCert gives you the signer's certificate as an object so you can examine its subject and issuer and evaluate trust — an essential step before relying on a signature to establish the sender's identity.

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 LastSignerCert method, which copies the certificate for the signer at
    --  the given zero-based index into a Cert object.  Most signed emails have one signer
    --  (index 0), but a message can have more than one.

    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/signed.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, 'ReceivedSigned', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN
        --  Get the certificate of the first signer (index 0).
        DECLARE @cert int
        EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

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

            EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
            PRINT 'Signer certificate (CN): ' + @sTmp0
          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