Configuring Database Mail in SQL Server

Configuring Database Mail in SQL Server
3.3 (65%) 4 votes

SQL Server Database Mail has been introduced in SQL Server 2005. Database Mail is a component that can send emails using SQL Server Engine. Using Database Mail, an administrator or a developer can send query output to an end user. DBAs can configure it to get email alerts and notifications. Database Mail uses SMTP (Simple Mail Transfer Protocol) to deliver emails to recipients.

In this article, I am going to demonstrate how to configure SQL Server Database Mail.

Prerequisites to Enable Database Mail

To enable the database mail feature, the following prerequisites are required:

  1. Service Broker for MSDB database must be enabled.
  2. SQL Server Agent service must be running.

Service Broker must be enabled

Database Mail requires service broker to queue emails. If the service broker is disabled, the database mail cannot deliver the service broker messages and it remains in the service broker queue.
To verify that service broker is enabled, execute the following query:

The output looks as follows:

If the service broker on the MSDB database is disabled, execute following queries in sequence to enable the service broker:

SQL Server Agent Service Must be Running

Database Mail sends emails or messages using the SQL Server agent service. If the agent services are not running, then SQL Server can not send emails. While agent service is not running, all the messages will be queued, and when agent service starts, all queued messages will be delivered.

To verify the status of agent service, execute following command in SQL Server management studio.

The output looks as follows:

If SQL Server agent service is not running, then we need to start it. To start the SQL Server Agent service, open Services, go to Control Panel >> Administration Tools >> Services. In the list of services, search for SQL Server Agent. Righ-click SQL Server Agent and select Start, as shown in the following image:

Once the service broker is enabled, and SQL Agent Service started, enable the database email by changing the database instance configuration parameter. By default, database email feature is not enabled. It must be enabled by changing the configuration value of Database Mail XPs from 0 to 1. Again, the Database Mail XPs parameter is an advanced option; hence, before changing it, change the Show advanced option configuration parameter from 0 to 1.

To enable database mail, execute the following queries in sequence:

Once Database Mail is enabled, create a database mail profile and database mail account.

Configuring Database Mail

Once all prerequisites are met, configure the database mail account and database mail profile.

Creating Database Mail Profile and Account

To configure the database mail account and database mail profile, Open SQL Server Management Studio. In Object Explorer, Expand Management and right-click Configure Database Mail, as shown in the following image:

The Database Mail Configuration Wizard dialog box opens. In the dialog box, select the Setup Database Mail by performing the following tasks radio button and click Next.

Now, in the New Profile dialog box, provide a profile name, a description which is optional and click the Add button to create an SMTP account. You can configure multiple SMTP accounts. When the Database mail sends an email, it uses the SMTP account based on the priority listed in the SMTP account grid view. If an account fails while sending email, the profile uses the next account in the priority list. See the following image:

In the New Database Mail Account dialog box, provide an appropriate Account Name, Description (optional), Email address, Display Name, Reply Email (Optional), SMTP server and Port Number. If the SMTP server uses the SSL /TSL authentication, then check The server requires secure authentication option. In the authentication list, select a type of authentication, used to authenticate the SMTP server and click OK. See the following image:

Back in the Database Mail Configuration Wizard dialog box, SMTP account will be listed in the SMTP accounts grid view. See the following image:

Click Next to configure the database mail profile security. We can configure the database email profile either private or public. Only a specific user or role can send email using Using private profile. Any database user or role can send email using Public profile. Users must be created in the MSDB database, and those users must be a member of a database role named DatabaseMailUserRole to send an email. We can also make the profile as a default profile. Once profile security sets, click Next to configure the system parameter. See the following image to view the configuration:

In the Configure System Parameter window, we can set a number of retry attempts, maximum attachment file size, list of file extensions which are prohibited from sending as an attachment. See the following image:

Once the system parameter has been configured, click Next to review the entire configuration and list of actions. Click Finish to complete the database mail configuration. See the following image.

Once the configuration is complete, click Close to exit from the wizard. See the following image:

Creating and Configuring Database Mail Profile and Account using T-SQL

We can also configure the database mail profile and mail account using T-SQL. To configure the database mail, the user must have the ‘execute’ permission on the following stored procedure.

  1. Sysmail_add_account_sp
  2. Sysmail_add_profile_sp
  3. Sysmail_add_profileaccount_sp

The Sysmail_add_account_sp procedure creates an account for the database mail. To create an account for database mail, execute the following code:

The Sysmail_add_profile_sp procedure creates a database mail profile. To create a database mail profile, execute the following code:

The Sysmail_add_profileaccount_sp procedure adds the SMTP account to a database mail profile. To add SMTP account to the database mail profile, execute the following code:

Sending Test Mail using Database Mail

Once the database mail is configured, as I mentioned previously, to send an email, the user must be created in the MSDB database and this user must be a member of DatabaseMailUserRole.  It allows a database user to send emails using Database Mail. The database user must have an executed permission on the following stored procedures, used to manage database mail:

  • sysmail_help_status_sp
  • sysmail_delete_mailitems_sp
  • sysmail_allitems
  • sysmail_event_log
  • sysmail_faileditems
  • sysmail_mailattachments
  • sysmail_sentitems
  • sysmail_unsentitems

To send a test email using SSMS, Open SQL Server Management Studio >> Expand Management in SQL Server Management >> Right-click  Database Mail and select Send Test Mail. See the following image:

The Send test email dialog box opens. In the Database Mail Profile drop-down, select the name of the profile used to send an email (Database Mail Profile), In the To text box, provide the email address of the email recipient and click Send Test Mail. See the following image:

We can also send email using T-SQL code. To do that, execute the following T-SQL script:

List of essential tables to check the email status

Following is the list of tables, used to view configuration database mail, database account and status of the email.

SrConfigurationList of table and view or Query
1.View list of profiles

  • msdb.sys.sysmail_profile

2.View list of accounts

  • msdb.sys.sysmail_account

3.View Mail server configuration

  • msdb.sys.sysmail_server

  • msdb.sys.sysmail_servertype

  • msdb.sys.sysmail_configuration

4.View Email Sent Status

  • msdb.sysmail_allitems

  • msdb.sysmail_sentitems

  • msdb.sys.sysmail_unsentitems

  • msdb.sys.sysmail_faileditems

5.View Status of events

  • madb.sys.sysmail_event_log

To view the database mail log, open SQL Server Management studio >> expand Management >> Right-click Database Mail and select View Database Mail Log. See the following image:

The Log file Viewer dialog opens, as shown in the following image:

Dropping Database Mail Account and Profile

To drop the Database Mail profile, first drop the Database Mail profile. To do that, open  Configure Database Mail Wizard, as explained at the beginning of the article. In the wizard, select Manage Database Mail account and profiles and click Next. See the following image:

On the Manage Profiles and Accounts page, select View Change Or delete an existing profile and click Next. See the following image:

On the Manage Existing Profile page,  select the name of the profile which you want to delete from the Profile Name drop-down box and click the Delete button and click Next. See the following image:

Next, the screen will display the summery of action, review the summary and click Finish to drop the profile. See the following image:

After dropping the database mail profile, drop the database account. To do that, on Open Database Mail Configuration Wizard >> select Manage Database Mail accounts and Profile >> Choose the View, change or delete an existing account option and click Next. See the following image:

On the Manage existing account page, from the Account Name drop-down box, select the name of account which you want to delete and click the Delete button, as shown in the following image:

The following screen will display the summery of action, review the summary and click Finish to drop the account. See the following image:

Dropping Database Mail Account and Profile using T-SQL

You can also drop the database mail account and database mail profile by executing T-SQL Script. To drop the database profile and account, execute the following code:

To verify that database mail and database account has been dropped, execute the following query:

Summary

In this article, I have explained:

  1. What are Database Mail and Its purpose?
  2. Pre-requisites to configure Database Mail.
  3. Configure Database Mail account and Database Mail Profile using SQL Server management studio.
  4. Configure Database Mail account and Database Mail Profile using T-SQL.
  5. Send Test email using SSMS and T-SQL.
  6. Gracefully Drop Database Mail profile and Database Mail account.
Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay

Latest posts by Nisarg Upadhyay (see all)