While SQL Anywhere has supported sending email for many releases, it was not able to send email through secure SMTP servers (such as the popular
smtp.gmail.com server) because they require TLS connections. This limitation
had been mentioned before by users trying to set up the SQL Anywhere Monitor to use GMail accounts for sending alert messages.
In
SQL Anywhere "Innsbruck", the
xp_startsmtp function has been enhanced to accept a certificate file that is used to authenticate a remote SMTP server. The remainder of this post will be a tutorial on setting up SQL Anywhere to send email through GMail's SMTP server.
According to Google , their SMTP server is located at
smtp.gmail.com, and TLS connections are made on port
587. In order to connect, we will need to supply
xp_startsmtp with the path to the root certificate that Google is using as their certifying authority. Most mail application include the root CA certificates of the major certifying authorities such as Verisign, Equifax, etc. However, since SQL Anywhere is not a mail reader, it does not come bundled with these root certificates. How can we determine what certifying authority GMail is using, and where do we get the root certificate?
One way is to determine this is to use a program like
OpenSSL to create a raw connection to the SMTP server and examine the certificate chain. + (As it turns out, since GMail is so popular, a quick Google search for "smtp.gmail.com root certificate' would give us the answer. However, we are trying to find a general solution that will work equally well for less popular services)?
You can create a raw connection to
smtp.gmail.com with openssl using:
openssl s_client -starttls smtp -connect smtp.gmail.com:587
This produces output that includes the lines:
---
Certificate chain
0 s:/C=US/ST=California/L=Mountain View/O=Google Inc/CN=smtp.gmail.com
i:/C=ZA/ST=Western Cape/L=Cape Town/O=Thawte Consulting cc/OU=Certification Services Division/CN=Thawte Premium Server CA/emailAddress=premium-server@thawte.com
---
Examining these lines tells us that the
smtp.gmail.com certificate is signed by the
Thawte Premium Server CA certificate issued by Thawte Consulting. There are two ways we can obtain a copy of this root certificate.
The first, and easiest, is to visit the certifying authorities' website and download their root certificate. For example, the Thawte certificates can be downloaded from
https://www.thawte.com/roots/index.html .
However, if it is a popular root certificate, chances are your operating system or browser may already contain the certificate. For example, to export the
Thawte Premium Server CA certificate out of IE 8.0 you can go Tools –> Internet Options –> Content –> Certificates –> Trusted Root Certificates. Find “Thawte Premium Server CA” in the list, and hit the “Export” button. In the Export wizard, choose “Base-64 (.CER) format”. Other browsers such as FireFox and Chrome allow you to export certificates in a similar way.
Now that we have all the pieces, we are ready to start sending emails. Start a SQL Anywhere “Innsbruck” database and connect to it through Interactive SQL. Issue the following query:
SELECT xp_startsmtp(<your gmail address>,
'smtp.gmail.com', 587, 60,
<your display name>,
<your gmail address>,
<your gmail password>,
<root certificate path>);
-- Ex. SELECT xp_startsmtp('john.doe@gmail.com', 'smtp.gmail.com', 587, 60, 'John Doe', 'john.doe@gmail.com', 'itsasecret', 'C:\certs\ThawtePremiumServerCA.crt');
If a connection was successful, this query will return a 0. If the connection was not successful, consult the SQL Anywhere documentation to determine the meaning of the error code.
Once the connection has been established, you can use
xp_sendmail to send emails.
SELECT xp_sendmail('john.doe@gmail.com', 'Hello, SMTP Server');
UPDATE (March 30, 2011) - Since the time that this was written, Google has switched to using the
Equifax Secure Certificate Authority certificate. Please use this certificate to send emails through GMail.
