xp_startsmtp with Hosted Exchange - Office 365

[former_SQLA_member1694868]

I was able to get email working from a SQLA 17 database using a GMail account with the help of Breck's great article.

For reference, the current Google certificate is a Geotrust certificate.

My question is what certificate (or any other adjustment) needs to be used for a hosted exchange type of setup, specifically Office 365. When I connect through our webmail, the certificate information lists a Baltimore CyberTrust certificate, but using that root certificate in SQLA 17 gives the error 6 on xp_startSMTP, which is what I was getting on the GMail testing until I got the certificate correct.

Quick DCX References for SA-17:

MAPI and SMTP System Procedures

MAPI and SMTP Status Codes

Thanks in advance for any help or ideas.

Edited to add error checking results: These are the calls:

@return_code = CALL xp_startsmtp ( smtp_sender = @smtp_sender, smtp_server = @smtp_server, smtp_port = @smtp_port, timeout = @timeout, smtp_sender_name = @smtp_sender_name, smtp_auth_username = @smtp_auth_username, smtp_auth_password = @smtp_auth_password, trusted_certificates = @trusted_certificates ); MESSAGE STRING ( 'xp_startsmtp return_code = ', @return_code ) TO CLIENT; MESSAGE STRING ( 'xp_startsmtp error code = ', xp_get_mail_error_code()) TO CLIENT; MESSAGE STRING ( 'xp_startsmtp error text = ', xp_get_mail_error_text()) TO CLIENT;

For these results:

6 0 ''

Removing the username, password, and certificate parameters for a simple call gives these results:

0 235 ''

The IANA.ORG SMTP code list says 235 is a status used for security errors without detail to protect the server's policies ("Something related to security caused the message to be returned, and the problem cannot be well expressed with any of the other provided detail codes. This status code may also be used when the condition cannot be further described because of security policies in force.") But it references a message return, not a login to the server so I'm not sure if that's the right 235 that I'm getting.

UPDATE: The username and password combo was bad (doh!). So with a good username and password by using the BALTIMORE certificate or the * (which allows use of the local computer's certificate store) and the secure = 1 or secure = NULL parameter (didn't matter) I was able to get to these error codes

`8 504 Authentication mechanism not supported'

Which leads me to the answer(?) below.

[Former Member]

Have you investigated this deeper with a call to "xp_ get_ mail_ error_code"( ) procedure? {new in version 1716)

[Breck_Carter]

Stupid question: Is your exchange email server using SMTP or MAPI?

[former_SQLA_member1694868]

Stupid response: I'm guessing SMTP as I set the SMTP server parameter as smtp.Office365.com. I'm reading that I may need to setup a "connector" within the Office365 admin tools but I'd rather get it to work with simply a name and password as that would be easier to setup at various clients' sites with their own names and passwords.

[former_SQLA_member1694868]

Yes - added results in code block above.

[Breck_Carter]

Historically "Exchange = MAPI" but it appears that Microsoft has created some flaming hoops through which you may jump in order to use SMTP with Office365.com.

Being younger and braver than I, perhaps you can make sense of the "SMTP relay" versus "SMTP client submission" described here.

[Breck_Carter]

Generally speaking, xp_get_mail_error_code() and xp_get_mail_error_text() are useful only if @return_code = -1.

In some cases when @return_code = 6 Google returns extra useful information in xp_get_mail_error_code() and xp_get_mail_error_text()... but that doesn't apply to you (you're not using Google SMTP).

Try making the call without the smtp_auth_username, smtp_auth_password and trusted_certificates parameters to see what @return_code you get. I suspect that it will not be an authentication error because the software being called doesn't recognize SMTP at all.

[former_SQLA_member1694868]

Results above. I think it may be using SMTP but as you mentioned MS seems to be quite interested in there being multiple flaming hoops so my hopes for an easy config may need to transition to medium config with either MAPI or the SMTP connector / relay.

[former_SQLA_member1694868]

This is my next test.

[Breck_Carter]

Return code = 0 means the call worked, and AFAIK 235 means authentication succeeded... which may mean nothing other than the fact the software being called did not return anything at all; i.e., this is an old-school no-authentication SMTP conversation.

It would be interesting to know what happened if you proceeded to actually try sending an email after receiving return code 0. At the very least it would provide no more clues... but it might!

I don't know what return code 8 means, but 504 may be telling you the software being called did not return something acceptable to the caller... which might be the case if you asked MAPI software to do SMTP.

[former_SQLA_member1694868]

Not sure if this is an answer yet or not, but my research into other products' interactions with Office 365 indicates that it is looking for the "LOGIN" method of SMTP authentication.

The docs indicate that xp_startsmtp supports only "PLAIN" or "CRAM-MD5."
Can anyone verify that these two are true? If so would there be any plans to support the "LOGIN" method of SMTP authentication?

Microsoft Setup Instructions

Example 1 - If the E-Mail-Server does not support "PLAIN", E-Mail-Notifications will fail - typically with "Auth...

Example 2 - Office365 only supports the LOGIN SASL on TLS port 587

[former_SQLA_member1694868]

Just checking in again to see if anyone "in the know" can answer or give a work-around for the Office365 LOGIN methods.

[graeme_perrow]

I can confirm that PLAIN and CRAM-MD5 are the only authentication methods supported. We have no current plans to change this. If Office365 doesn't support any other authentication methods, I can't think of any workarounds.

[former_SQLA_member1694868]

Do you know of any working examples of using SQLA 17 to send email via SMTP through the smtp.office365.com setup? The majority of our clients are using some form of that for their email setup.

[Breck_Carter]

Google AI, er, Google Search keeps getting better and better...

how do i send email from an executable via "smtp.office365.com"?

[Vlad]

Don't you mind if I suggest you to play with the Mail API.
I know people like struggling with old solutions that were working for them for years, but you can extend SA with a "relatively short" Java code to use Java API for emails (in case you don't like MS Mail API).
If Java is not your taste, you can use 3rd party services or google for some SMTP relay server.