How do I send email from my database using GMail. I was trying to solve this problem recently, indirectly, while setting up e-mail alerts from Breck Carter's Foxhound monitoring tool. Foxhound runs entirely in SQL Anywhere and uses xp_startsmtp for sending alerts, and I couldn't make it work - getting either nothing or smtp error 504. It turned out the answer was in the documentation the whole time, but not being well versed in the subject, I didn't recognise it. My solution posted below. asked 02 Jun '21, 12:14 Justin Willey |
First, you need an email "app password" for your Google Account - this avoids the problems of "less secured applications" etc. See https://support.google.com/accounts/answer/185833?hl=en This password can only be used for the purpose you specify when you set it up - it's not a general password for the given Google Account. NB - if you change you main Google password, all app passwords are disabled, and need to be re-set. The key then is to use port 587 at smpt.gmail.com and to specify trusted_certificates='SMTPS=Yes;Secure=1' when calling xp_startsmtp. You don't need to download and trust public certificates / keys from Google or anything like that. This script, adapted from earlier posts on the subject, illustrates: BEGIN DECLARE @return_code INTEGER; DECLARE @smtp_sender LONG VARCHAR; DECLARE @smtp_server LONG VARCHAR; DECLARE @smtp_port INTEGER; DECLARE @timeout INTEGER; DECLARE @smtp_sender_name LONG VARCHAR; DECLARE @smtp_auth_username LONG VARCHAR; DECLARE @smtp_auth_password LONG VARCHAR; DECLARE @trusted_certificates LONG VARCHAR; DECLARE @recipient LONG VARCHAR; DECLARE @subject LONG VARCHAR; DECLARE @message LONG VARCHAR; SET @smtp_sender = 'whatever.you.want@myco.com'; SET @smtp_server = 'smtp.gmail.com'; SET @smtp_port = 587; SET @timeout = 60; -- default is 60 seconds SET @smtp_sender_name = 'Whatever You Want'; SET @smtp_auth_username = 'actual.gmail.account@myco.com'; SET @smtp_auth_password = 'ptgtsujskisdqwqe56thl'; -- "app password" NOT the Google Account password. SET @trusted_certificates = 'SMTPS=Yes;Secure=1'; SET @recipient = 'a.person@my-customer.com'; SET @subject = STRING ( 'gmail test subject at ', CURRENT TIMESTAMP ); SET @message = STRING ( 'gmail test message at ', CURRENT TIMESTAMP ); @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_get_mail_error_code() = ', xp_get_mail_error_code() ) TO CLIENT; MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT; @return_code = CALL xp_sendmail ( recipient = @recipient, subject = @subject, "message" = @message ); MESSAGE STRING ( 'xp_sendmail @return_code = ', @return_code ) TO CLIENT; MESSAGE STRING ( 'xp_get_mail_error_code() = ', xp_get_mail_error_code() ) TO CLIENT; MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT; @return_code = CALL xp_stopsmtp(); MESSAGE STRING ( 'xp_stopsmtp @return_code = ', @return_code ) TO CLIENT; MESSAGE STRING ( 'xp_get_mail_error_code() = ', xp_get_mail_error_code() ) TO CLIENT; MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT; EXCEPTION WHEN OTHERS THEN CALL xp_stopsmtp(); END; [In the Foxhound setup the text "SMTPS=Yes;Secure=1" (without the quotes) goes in the box called "SMTP Certificate Filespec".] answered 02 Jun '21, 12:26 Justin Willey Out of curiosity: Is that solution similar/related to Breck's older blog article - which you apparently had read then?
(02 Jun '21, 15:22)
Volker Barth
Replies hidden
2
Yes, indeed... in fact, I'm hoping to use Justin's discoveries to get gmail working again for me ( the Foxhound 5 docs only talk about using SendGrid :)
(02 Jun '21, 16:18)
Breck Carter
Comment Text Removed
How can we use the above snippet to send an email from an Office365 account? I changed the following:
It didn't work! Should I also change this line?
According to Office365, the supported encryption is STARTTLS
(22 Jun '21, 06:18)
Baron
Replies hidden
Here I get the following Errors: xp_startsmtp @return_code = 8 xp_get_mail_error_code() = 504 xp_get_mail_error_text() = "Authentication mechanism not supported." xp_sendmail @return_code = 3 xp_get_mail_error_code() = 504 xp_get_mail_error_text() = "Authentication mechanism not supported." xp_stopsmtp @return_code = 0 xp_get_mail_error_code() = 504 xp_get_mail_error_text() = "Authentication mechanism not supported."
(22 Jun '21, 06:32)
Baron
1
The instructions are only intended for use with smtp.gmail.com. Google and Microsoft do things differently. Where did you find outlook.office365.com documented as an smtp server? It works on port 80 as an HTTP server. Auntie Google says the Outlook smtp server is smtp-mail.outlook.com
(23 Jun '21, 06:46)
Breck Carter
Is it possible here to include more than one file as attachment? According to the documentation, the parameter include_file specifies an attachment file, which means only one file! I tried semicolon separated file names but without success.
(25 Jun '21, 03:48)
Baron
See the samples in the xp_sendmail() doc. AFAIK you can only specify one file as attachment but you can add the contents of multiple files base64-encoded within the mail body itself.
(25 Jun '21, 06:40)
Volker Barth
|
I see some differences in parameters between this code and documentation of xp_startsmtp. Docs says: xp_startsmtp( smtp_sender = email-address ... [, trusted_certificates = { public-certificate | * } [, secure = { 1 | 0 } ] Here in code you used 'Secure=1' as Key/Value in parameter trusted_certificates Is documentation wrong ? answered 03 Jun '21, 04:26 Alex99 Yes - you are quite right to point this out. If you have SMTPS=Yes in trusted_certificates, then you seem to have to have Secure=1 in there as well. If you don't, you get return code 6 and the message "Unable to open certificate file 'SMTPS=YES'" Having Secure=1 as a separate parameter makes no difference either way. Perhaps there is a parsing bug in the procedure? (Which is concerning if we are coding to a bug!) NB It doesn't matter if you have SMTPS=Yes;Secure=1 or Secure=1;SMTPS=Yes - both work.
(03 Jun '21, 06:38)
Justin Willey
Aggh - the plot thickens, red herrings abound. This works just as well, with no separate Secure=1 parameter: SET @trusted_certificates = 'SMTPS=Yes;blah=blah'; however this doesn't (same error about opening certificate): SET @trusted_certificates = 'SMTPS=Yes;'; so it looks like there is a parsing bug in xp_startsmtp
(03 Jun '21, 06:45)
Justin Willey
Replies hidden
(03 Jun '21, 11:01)
Breck Carter
|