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:

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:

---