Sending mail via Office 365 smtp using Invantive SQL

410 views Asked by At

I'm trying to send a e-mail using the Invantive Data Hub using the following query (password and username are redacted).

insert into smtp@Mail
( fromEmail
, toEmail
, subject
, body
, smtpHostAddress
, smtpUsername
, smtpPassword
)
VALUES
( "[email protected]"
, "[email protected]"
, "Test"
, "Body"
, "smtp.office365.com"
, "[email protected]"
, "*******"
)

This results in the following error:

Uitroep itgendhb077: Error in Invantive Data Hub.
The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM [DB6P191CA0024.EURP191.PROD.OUTLOOK.COM]

The username/password is correct (checked that), is there a way to trigger Invantive Data Hub to use a secure connection?

I have used the smtp syntax from this LinkedIn post.

1

There are 1 answers

3
Guido Leenders On BEST ANSWER

Sending email from Invantive SQL can sometimes be hard. In your case, I would recommend one of the following solutions:

  • List your server which originates the SMTP request as a trusted address in Exchange for relaying messages outside your local network (when allowed to configure that) and allow (transport unsecured) SMTP port 25 connections.
  • Or use a secured exchange.

The first one is a system administrator task; the use of secured transport can be enforced by specifying a value for the field enableSsl in smtp@mail.

This field enableSsl has become available in July 2017 and later releases. The full list of fields is:

  • bccEmail
  • body
  • ccEmail
  • fromEmail
  • fromName
  • headers
  • isBodyHtml
  • minimumDeliveryDurationMsec
  • priority
  • replyToEmail
  • replyToName
  • smtpHostAddress
  • smtpHostPortNumber
  • smtpPassword
  • smtpUserName
  • subject
  • toEmail

More Mail Fields

Please note that insert into smtp@mail has many more fields available, such as:

insert into Smtp@Mail 
(headers, fromEmail, fromName, replyToEmail, replyToName, toEmail, ccEmail, bccEmail, subject, body, isBodyHtml, priority, smtpHostAddress, smtpUsername, smtpPassword) 
values 
( 'X-SAMPLE:Y;X-SAMPLE2:Z'
, 'someone@somewhere'
, 'Test'
, 'me@home'
, 'Test'
, 'me@home'
, ''
, ''
, 'Ubersicht Rechnungen (HTML) [s-unittest]'
, '<p>Sehr geehrte Damen und Herren,</p>'
       || '<p>Um Ihnen unnötiges Nachhalten offener Rechnungen zu ersparen, informieren wir Sie mit diesem Schreiben über Ihr Konto bei ACME.</p>'
       || '<p>Wir wünschen Ihnen viel Erfolg bei Ihren Geschäften.</p>'
       || ' <p>Mit freundlichen Grüßen,</p>'
       || ' <p><b>Invantive GmbH</b></p>'
, 'true'
, -1
, 'smtp.server.address'
, 'some-user'
, 'some-password'
)

For instance, to include an attachment use the fields attachmentContents, attachmentName and attachmentMimeType. A full list of fields you get by querying either systemtablecolumns@datadictionary or pressing F4 in the query tool when the cursor is located on the smtp@mail text.

Defaults

You don't have to specify all values on your insert into smtp@mail. There are a number of options for which you can set defaults from your settings.xml connection string or through the set statement.

The available options can be using a query like select * from systemdatacontainerattributes@datadictionary or on systemproviderattributes:

  • smtp-minimum-deliver-duration-ms: Minimum deliver duration in milliseconds for the SMTP send plus inserted sleep when SMTP send finished earlier than the minimum.
  • smtp-send-timeout-ms: Timeout in milliseconds after which the SMTP send times out.
  • smtp-enable-ssl: Set whether SSL is enabled for SMTP connections.
  • smtp-password: The default SMTP password to authenticate with.
  • smtp-user-name: The default SMTP user name to authenticate with.
  • smtp-host-port-number: The default SMTP host post number to use.
  • smtp-host-address: The default SMTP host address to use.
  • mail-priority: Priority of the mail; negative is bulk, 0 is neutral, positive is urgent.
  • mail-body-html: Set whether the mail body is HTML.
  • mail-reply-to-name: The default REPLY TO name.
  • mail-reply-to-email: The default REPLY TO email address.
  • mail-from-name: The default FROM name.
  • mail-from-email: The default FROM email address.
  • pre-request-delay-ms: Pre-request delay in milliseconds.
  • invantive-use-cache: Whether to cache the results of a query.
  • invantive-sql-shuffle-fetch-results-data-containers: Whether to shuffle results fetched from data containers.
  • invantive-sql-forward-filters-to-data-containers: Whether to forward filters to data containers.