Using xp_startsmtp with GMail

[justin_willey]

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.

[former_SQLA_member1694921]

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 ?

[justin_willey]

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.

[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

[Breck_Carter]

[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".]

[VolkerBarth]

Out of curiosity: Is that solution similar/related to Breck's older blog article - which you apparently had read then?

[Breck_Carter]

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 🙂

[Baron]

How can we use the above snippet to send an email from an Office365 account? I changed the following:

SET @smtp_server = 'outlook.office365.com';

SET @smtp_port = 587;

It didn't work! Should I also change this line?

SET @trusted_certificates = 'SMTPS=Yes;Secure=1';

According to Office365, the supported encryption is STARTTLS

[Baron]

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."

[Breck_Carter]

How can we use the above snippet to send an email from an Office365 account?

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

[Baron]

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.

[VolkerBarth]

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.