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:
Thanks in advance for any help or ideas.
Edited to add error checking results: These are the calls:
For these results:
Removing the username, password, and certificate parameters for a simple call gives these results:
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.
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."
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" ...