SQL Server

Get the SMTP or POP3 Server's TLS Certificate

Demonstrates the Chilkat MailMan.GetServerCert method, which gets the digital certificate presented by the SMTP or POP3 server for the current SSL/TLS connection and stores it in a Cert object. Pass true for the SMTP server's certificate or false for the POP3 server's. This example connects to the SMTP server and prints the certificate's subject and issuer.

Background: During a TLS handshake the server presents a certificate proving its identity, and the client validates it against trusted authorities. Retrieving that certificate lets you inspect it yourself — checking the subject, issuer, or expiration for auditing, logging, or certificate-pinning style checks. It must be called while a TLS connection is established, since the certificate belongs to that specific session.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

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

    --  Demonstrates the MailMan.GetServerCert method, which gets the digital certificate
    --  presented by the SMTP or POP3 server for the current SSL/TLS connection.  Pass 1 for
    --  the SMTP server's certificate, or 0 for the POP3 server's certificate.

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

    --  Configure the SMTP server connection.
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.example.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
    EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'user@example.com'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'myPassword'

    --  Establish the TLS connection.
    EXEC sp_OAMethod @mailman, 'SmtpConnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      END

    --  Get the certificate the SMTP server presented (useSmtp = 1).
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @mailman, 'GetServerCert', @success OUT, 1, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
    PRINT 'Server certificate subject (CN): ' + @sTmp0

    EXEC sp_OAGetProperty @cert, 'IssuerCN', @sTmp0 OUT
    PRINT 'Issued by: ' + @sTmp0

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @cert


END
GO