Sending SQL Server Database Mail Alerts via Amazon’s SES

This post is selfishly done—in the sense that I’m mostly documenting a tiny bit of detail that I recently 'solved' in terms of figuring out how to get SQL Server’s Database Mail functionality to work with Amazon's excellent (as in highly available and absurdly affordable) Simple Email Service (SES).

Why Use Amazon's SES to Send Email

Of course, I’m sure some of you are wondering why I'd bother using Amazon’s SES for Database Mail in the first place.

For me the answer is simple: I've got a large number of (virtual) servers in my lab—and every once in a while I need them to be able to send email alerts so that I can test various aspects of failover, alerting, job failure, or whatever else I'm currently developing or working on at any given moment for myself of clients (meaning that I've got to make sure various 'scenarios' work across a wide variety of different Editions and versions of SQL Server).

To that end, I use to keep an SMTP server running in my lab, but that got old in a hurry. Life's too short to host your own SMTP server just for testing purposes. Likewise, a while back, I started using Amazon's SES to send notifications, updates, and such from my free SQL Server Videos site—and love how easy it is to call from within code. Then I noticed that Amazon also provides an SMTP interface for SES and had to try and see if I could just set up my dev/test servers in my lab to route via SMTP into my already approved SES accounts.

Increased Portability

Of course, while my reasons for writing this post are selfish (i.e., to document the single 'trick' I found that I needed to remember), the reality is that SMTP servers can be a bit finicky to access and relay—through in many real-world organizations. Amazon's SES, on the other hand, is 'ubiquitous' in that it's pretty much always on, and won't be getting modified/tweaked/removed some day by Sys Admins who get sick of managing another SMTP server on their network. That, and for anyone who might end up having SQL Server VMs that might want to pause/thaw/migrate/or-whatever, pointing them towards SES might make sense.

Either way, I've now spent exponentially more time explaining this post than the amount of info it'll contain in terms of actually setting up SQL Server's Database Mail to work correctly with Amazon's SES.

It's the Port Number, Silly

Funny thing is, I tried setting this up two or three times over a few weeks (half-hearted attempts—where I tried one or two things, got distracted, forgot about it, then tried a few days later).

In the end there's no big secret to getting things working—other than using the correct port: 587.

In terms of specifics, here's all you'll need to do to get things working:

  • Signup and/or Create an AWS Account with Amazon—if you don't already have one.
  • Signup for Amazon SES.
  • Verify a Sending Addressi.e., provide Amazon SES with the email address of the account you'd like to be able to send email fromat which point they send an email to the address in question, you respond/reply/click—something (I can't remember what) and that confirms that you 'own' the address in question.
  • Request Production Access for your SES accountif necessary. (Without production access you can only send from one verified address to another.)
  • Configure/Enable SMTP access for your SES account, and obtain your SES SMTP Credentials.

From there, it's simply a question of going through the Database Mail setup wizard/process—as I outlined in my post on "Configuring and Troubleshooting Database Mail"—and then you're done.

The only trick, however (and the entire purpose of this post), is that while Amazon's SES does listen on ports 25, 465, and 587, I wasn't able to get it to actually WORK until I specifically set my SMTP settings to use port 587 (and, of course, you have to specify that SMTP needs to be managed over TLS/SSL) as per the screenshot below:

Discuss this Blog Entry 3

on Aug 22, 2013

SES is not free, so why not use gmail or hotmail instead?

on Aug 22, 2013

@jxhyao. You can, as outlined here:
http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Use whatever works best for you. I happen to do a ton via SES - so $.10 per 10,000 messages is fine for me - and wanted to document the correct ports/etc here.

--Mike

on Dec 14, 2013

Thanks for help I was having same problem. But by checking "This server requires a secure connection" i got resolved. Thanks a lot.

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×