Sending Emails Via SendGrid SMTP Relay

Once upon a time, it was possible to send emails from inside SQL Anywhere by using xp_sendmail with Google's free smtp.gmail.com service.

It may still be possible, but apparently not for me... all I get is return code 6 (Secure connection error) after clicking on Send Test Alert Email in Foxhound:

Email test result: SMTP start test AutoDrop email failed with return code 6 when sending "Foxhound Test AutoDrop Email (2017-12-01 09:58:38)". Secure connection error

There are a number of alternatives to Google's free SMTP email server; SendGrid is one of them, here's how to sign up:

• Go to https://sendgrid.com/.
  

• Click on Try it for free.
  

• Provide a user name and password, a real email address and some other information.
  

• Choose SMTP Relay (you're going to use SendGrid's API via SQL Anywhere's xp_sendmail() procedure).
  

• Specify a value for API key and receive a big-long-ugly-generated-password in return.
  

• Note the following values (you're going to use port 25):
  

  Server smtp.sendgrid.net Ports 25, 587 (for unencrypted/TLS connections) 465 (for SSL connections) Username apikey Password big-long-ugly-generated-password

Here's how to set up Foxhound's Alert Email feature to use the SendGrid SMTP Relay:

• Foxhound Options page - 2. Global Email Settings
  

  Check Use SMTP to send emails SMTP Sender: your.email@whatever.com SMTP Server: smtp.sendgrid.net SMTP Port: 25 SMTP Timeout: 60 SMTP Authorization User Name: apikey SMTP Authorization Password: big-long-ugly-generated-password SMTP Certificate Filespec: [leave empty]

• Monitor Options page - 7. Email Setup for [Default Settings]
  

  Use HTML in emails: [checked] Host[:port] for URLs in emails: localhost Send Alert emails: [unchecked] Email address(es) for Alerts: your.email@whatever.com Click on Send Test Alert Email Test result: OK

Here's how to use the SendGrid SMTP Relay in your own SQL:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

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          = 'your.email@whatever.com'; SET @smtp_server          = 'smtp.sendgrid.net'; SET @smtp_port            = 25;   -- 25 for standard SMTP, 465 for SSL, or 587 for TLS SET @timeout              = 60;   -- default is 60 seconds SET @smtp_sender_name     = 'Foxhound'; SET @smtp_auth_username   = 'apikey'; SET @smtp_auth_password   = 'big-long-ugly-generated-password'; SET @trusted_certificates = NULL; SET @recipient            = 'your.email@whatever.com'; SET @subject              = STRING ( 'sendgrid test subject at ', CURRENT TIMESTAMP ); SET @message              = STRING ( 'sendgrid 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;

Here's the MESSAGE ... TO CLIENT output:

xp_startsmtp @return_code = 0 xp_get_mail_error_code() = 235 xp_get_mail_error_text() = "Authentication successful " xp_sendmail @return_code = 0 xp_get_mail_error_code() = 250 xp_get_mail_error_text() = "Mail queued for delivery. " xp_stopsmtp @return_code = 0 xp_get_mail_error_code() = 250 xp_get_mail_error_text() = "Mail queued for delivery. "

Here's what the email looks like:

---