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.

asked 10 Oct '16, 10:18

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

edited 13 Oct '16, 11:41

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

(11 Oct '16, 10:03) Nick Elson S...
Replies hidden

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

(11 Oct '16, 11:41) Breck Carter
Replies hidden

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.

(12 Oct '16, 00:10) Siger Matt

Yes - added results in code block above.

(12 Oct '16, 00:18) Siger Matt

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.

(12 Oct '16, 06:41) 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.

(12 Oct '16, 06:56) Breck Carter
Replies hidden
Comment Text Removed

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.

(12 Oct '16, 10:26) Siger Matt

This is my next test.

(12 Oct '16, 10:26) Siger Matt

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.

(14 Oct '16, 07:32) Breck Carter
showing 3 of 9 show all flat view

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 "Authentication mechanism not supported". Now - guess what ... Exchange does support plaintext-logins when configured correctly, but only using the method "LOGIN" ...

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

permanent link

answered 13 Oct '16, 11:27

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

edited 13 Oct '16, 11:39

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×73
×10
×1
×1

question asked: 10 Oct '16, 10:18

question was seen: 245 times

last updated: 14 Oct '16, 07:34