SQL Server

Get the S/MIME Signer's Certificate

Demonstrates the Chilkat Mime.LastSignerCert method, which loads the signer certificate at a zero-based index from the most recent successful verification (or security-unwrapping) into a Cert. The first argument is the signer index and the second is the receiving Cert.

Note: The file paths are relative to the application's current working directory. Absolute paths may also be used. Supply the paths appropriate to your own environment.

Background: Verifying tells you the signature is mathematically valid, but you usually also need to know who signed — the subject, the issuer, the validity dates — to decide whether to trust it. This retrieves each signer's certificate after verification so your code can inspect and apply its own trust rules. A message can have multiple signers, so iterate from 0 to NumSignerCerts - 1.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

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

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

    EXEC sp_OAMethod @mime, 'LoadMimeFile', @success OUT, 'qa_data/signed.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    --  After a successful verification, load each signer's certificate to inspect it.  The 1st argument
    --  is the zero-based signer index and the 2nd is a Cert object that receives the certificate.
    EXEC sp_OAMethod @mime, 'Verify', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    DECLARE @n int
    EXEC sp_OAGetProperty @mime, 'NumSignerCerts', @n OUT
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @n - 1
      BEGIN
        EXEC sp_OAMethod @mime, 'LastSignerCert', @success OUT, @i, @cert
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @mime
            EXEC @hr = sp_OADestroy @cert
            RETURN
          END


        EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT

        EXEC sp_OAGetProperty @cert, 'IssuerCN', @sTmp1 OUT

        PRINT 'Signer ' + @i + ': ' + @sTmp0 + ' (issued by ' + @sTmp1 + ')'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @cert


END
GO