How to Configure Database Mail in SQL Server

INTRODUCTION

Notifications are usual in most modern systems. SQL Server implemented means of sending notifications by email early with the feature called SQL Mail. In SQL Server 2005, Database Mail was released, and we still have Database Mail working in current versions of SQL Server. Given this background, we can say that Database Mail is a very mature feature in SQL Server.  

I faced some use cases related to the Database Mail deployment in my experience.

The list is not full, but have a look at some issues:

1. Monitoring – In my days as a Database Administrator, in the absence of a full-blown monitoring tool, I adopted a script developed by an acquaintance who blogged on SQL Server Central. That script was HTML-based, but it incorporated SQL that extracted data from key system catalog views and sent the output by email to administrators.

• Job Results – I also used Database Mail to deliver the output of jobs to Database Administrators or even regular users who wanted to get such information. An SQL Agent lets you leverage Database Mail to simply send jobs’ status – success or failure. You can go further by developing scripts using the stored procedure sp_send_dbmail

• Reporting – In the case of simple reports, the sp_send_dbmail stored procedure can also be used to aggregate a result set and send it as a file or in an email with proper formatting.

REQUIREMENTS

The following are essential requirements to consider when configuring Database Mail:

1. Port 25 (SMTP) must be open between the SQL Server host and the Exchange Server. It is relevant in environments where the network is segmented by Firewalls.
2. SQL Server host must have mail relay permissions on the domain. A Microsoft Exchange administrator should be able to ensure it for you.
3. Create an exchange server account to send mails. It is not mandatory, but I personally prefer to have control over which account performs which actions in my environment.

PROCEDURE

To send emails from a SQL Server instance, you need to configure Database Mail. Do the following steps:

1. The welcome screen gives you an overview of the actions you are about to take. In this process, you will set up a mail profile, a mail account, security, and then configure the system parameters. (See Figure 1)

2. Now, you see the options to select with radio buttons. Since we are setting up database mail for the first time, we choose the first radio button.

3. Enable the Database Mail feature to proceed with the configuration.

4. Create a profile. A profile is a collection of mail accounts used by the SQL Server engine to send emails. Decoupling the profile and accounts allows flexibility and reliability. You can change the email account associated with your mail profile at will. Also, you can use multiple email accounts for the same profile, thus providing resilience.

5. View the details required to set up the mail account: the account name, the associated email account, the mail server, and credentials. As mentioned earlier, you can choose to send emails anonymously.

Once you’ve configured the mail account, you have successfully associated it with the mail profile. You can also add additional accounts. Note that for security reasons, such accounts, as specified in the email address field, should be denied interactive logon to servers. It reduces damage in case of being compromised.

7. The next step is to set up security for the profile. You choose whether to make the profile public or private. A public profile can be used by any principal on SQL Server added to the DatabaseMailUserRole database role. A private profile can be used only by the sysadmin account or other principal granted EXECUTE privileges on the sp_send_dbmail stored procedure. The public profile usually suits my purposes.

The last significant step is to set up the system parameters. People mostly find it easier to skip this step. We can change such configurations as retries, file size, and logging. I have personally found it useful to increase the file size limit when setting up a report larger than 1MB.

9. Complete the setup.

CONCLUSION

In this article, we have reviewed the step by step Database Mail configuration. We’ve also provided a few additional practical tips. In a follow-up article, we shall explore the system catalogs that expose data to monitor and troubleshoot the Database Mail.

REFERENCES

Database Mail Configuration Objects