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.
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 Address—i.e., provide Amazon SES with the email address of the account you'd like to be able to send email from—at 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 account—if 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: