Configuring and Troubleshooting Database Mail for SQL Server

In the past I've blogged a bit about the importance of enabling your SQL Servers to be pro-active in terms of alerting you via email when something bad has happened or requires attention. I've also addressed a very common 'gotcha' that prevents the SQL Server Agent from raising email alerts/warnings when jobs fail or when configured alerts have been triggered.

But I've never really covered any specifics about setting up Database Mail.

Enabling and Configuring Database Mail

Happily, setting up Database Mail is FAIRLY straight-forward, and Microsoft has even documented much of the process online. In my estimation, though, the documentation would be a bit better if it was accompanied by screenshots. My guess, though, is that since Microsoft is making this documentation available in MULTIPLE languages, screenshots would make the documentation process substantially more complex and expensive to manage. Otherwise, the documentation that Microsoft provides is pretty comprehensive (even if it is a bit terse).

That said, a couple of screenshots might help. For starters, you’ll want to enable (or check on) Database Mail via the Management Node in SQL Server Management Studio – against the server you’re configuring:

 
Of course, if you’ve never configured Database Mail before, you’ll likely get a warning/confirmation dialog that looks similar to:

Which is just asking if you want to configure (or enable) Database Mail XPs (or specialized, eXtended Procedures (XP) designed to enable SQL Server to talk to mail servers). And while there's always the risk of increasing your surface attack area any time you open up additional functionality like enabling XPs, the reality is that the risk of compromise here is QUITE small—to the point where enabling XPs on MOST servers is something that’s 'totally' safe. That said, if there's a zero-day exploit or other problem discovered against this functionality, then you'll have to patch accordingly and so on. (So, in other words, what we’re primarily dealing with here is that Microsoft has taken a best-practices approach to forcing enablement of these features to be an explicit operation.)

From there, things are pretty straight-forward in terms of walking your way through the various wizards and options (unless you’re like me and don’t actually READ the options).

In fact, at this point, the actual UI or setup is pretty simple, as all you really need to do from this point is:

From here you'll need your SMTP credentials and configuration details, and will then just need to put them into the appropriate Database Mail 'screens' to get things working. And, note, while it typically makes sense to set these up against your own domain or SMTP servers, you CAN actually set  Database Mail to work against pretty much any mail server—as outlined here.

General Tips for Setup and Debugging

Otherwise, everything else you'll do when setting up Database Mail should be pretty self-explanatory. That said, I do have a couple bits of advice and guidance to offer in terms of setup and troubleshooting.