Wednesday, December 20, 2017

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: