SQL Server

Send DKIM Signed Email

See more DKIM / DomainKey Examples

Demonstrates how to send DKIM signed email. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

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

    DECLARE @dkim int
    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
    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
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdMime OUT

    EXEC sp_OAMethod @mailman, 'RenderToMimeBd', @success OUT, @email, @bdMime
    IF @success = 0
      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
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadAnyFormatFile', @success OUT, 'qa_data/pem/rsa_passwd.pem', @password
    IF @success = 0
      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 = 0
      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 = 0
      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 = 0
      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