(SQL Server) Send DKIM Signed Email

Demonstrates how to send DKIM signed email.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

    DECLARE @mailman int
    -- Use "Chilkat_9_5_0.MailMan" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @dkim int
    -- Use "Chilkat_9_5_0.Dkim" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Dkim', @dkim OUT

    -- SMTP server settings...
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'SMTP_DOMAIN'
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'SMTP_LOGIN'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'SMTP_PASSWORD'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 465
    EXEC sp_OASetProperty @mailman, 'SmtpSsl', 1

    DECLARE @email int
    -- Use "Chilkat_9_5_0.Email" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OASetProperty @email, 'Subject', 'This is a test'
    EXEC sp_OASetProperty @email, 'Body', 'This is a test'
    EXEC sp_OASetProperty @email, 'From', 'Chilkat Support <support@chilkatsoft.com>'

    -- Add some recipients (BCC recipients are added below)
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Software', 'chilkat_software@yahoo.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Admin', 'admin@chilkatsoft.com'
    EXEC sp_OAMethod @email, 'AddCC', @success OUT, 'Chilkat Admin', 'admin@chilkat.io'

    -- When sending an email with a DKIM signature, the MIME
    -- of the email must not be modified prior to
    -- or during the sending process.  Therefore, the MIME of the
    -- email is assembled, the DKIM signature is added, and then
    -- SendMimeBd is called to send the email.

    -- First get the MIME of the email.  Calling RenderToMimeBd 
    -- causes the email to be signed and/or encrypted if those options
    -- have been selected.  The MIME returned by RenderToMimeBd
    -- is the exact MIME that would've been sent if SendEmail was
    -- called.  We'll add a DKIM signature header to this MIME and then send...
    DECLARE @bdMime int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdMime OUT

    EXEC sp_OAMethod @mailman, 'RenderToMimeBd', @success OUT, @email, @bdMime
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @dkim
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdMime
        RETURN
      END

    -- To create a DKIM signature,
    -- you'll need to provide the following:
    -- (1) An RSA private key in any common file format.
    -- (2) The domain name (typically the same domain
    -- as the sender of an email).
    -- (3) A selector for the domain name -- an arbitrary string
    -- to identify the matching public key in DNS.

    -- To allow an email recipient to verify the DKIM signature via an
    -- external email client supporting DKIM or using other software,
    -- you will need to create a DNS record with the selector
    -- and matching public key.  This example does not provide
    -- information about DKIM DNS record creation.  
    -- (Chilkat does not provide an API for creating DNS records.  
    -- Creating the DKIM DNS record is something you would do
    -- manually.)

    -- Set the domain and selector:
    -- You'll need to provide your own values here..
    EXEC sp_OASetProperty @dkim, 'DkimDomain', 'chilkatsoft.com'
    EXEC sp_OASetProperty @dkim, 'DkimSelector', 'brisbane'

    -- Load a private key from any common RSA private key
    -- format, such as DER, PKCS8, PEM, XML, etc.
    -- The LoadDkimPkFile method automatically detects
    -- the file format and reads it appropriately.
    -- If a password is not required, you'll still need to provide
    -- an empty password string argument, but it is ignored.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'passwd'
    DECLARE @privKey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadAnyFormatFile', @success OUT, 'qa_data/pem/rsa_passwd.pem', @password
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @dkim
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdMime
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    EXEC sp_OAMethod @dkim, 'SetDkimPrivateKey', @success OUT, @privKey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dkim, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @dkim
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdMime
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Add the DKIM-Signature header to the MIME in bdMime
    EXEC sp_OAMethod @dkim, 'DkimSign', @success OUT, @bdMime
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dkim, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @dkim
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdMime
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- If desired, examine the MIME of the email being sent.
    EXEC sp_OAMethod @bdMime, 'WriteFile', @success OUT, 'qa_output/dkimSigned.eml'

    -- ---- Prepare to send the email contained in bdMime ---

    -- To send to multiple email addresses, the strRecipients should be set to a comma-separated
    -- list of email addresses.

    -- Note: This is where we can add additional BCC addreses.
    -- Here we are adding "chilkat.support@gmail.com" as an additional BCC address.
    -- Notice that the 1st three email addresses are NOT BCC because these email addresses
    -- appear in the MIME header of the email (in the "To" and "CC" header fields).
    -- A BCC email address does not appear in the MIME, and thus the non-BCC recipients cannot see
    -- that the email was also sent to the BCC address.  (BCC means "blind carbon copy")
    DECLARE @strRecipients nvarchar(4000)
    SELECT @strRecipients = 'chilkat_software@yahoo.com, admin@chilkatsoft.com, admin@chilkat.io, chilkat.support@gmail.com'
    DECLARE @strFrom nvarchar(4000)
    SELECT @strFrom = 'support@chilkatsoft.com'

    EXEC sp_OAMethod @mailman, 'SendMimeBd', @success OUT, @strFrom, @strRecipients, @bdMime
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @dkim
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdMime
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

        PRINT 'Connection to SMTP server not closed cleanly.'
      END


    PRINT 'DKIM Signed Mail Sent!'

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @dkim
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @bdMime
    EXEC @hr = sp_OADestroy @privKey


END
GO