Configuring Database Mail in SQL Server

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:

USE master 
go 

SELECT database_id AS 'Database ID', 
       NAME        AS 'Database Name', 
       CASE 
         WHEN is_broker_enabled = 0 THEN 'Service Broker is disabled.' 
         WHEN is_broker_enabled = 1 THEN 'Service Broker is Enabled.' 
       END         AS 'Service Broker Status' 
FROM   sys.databases 
WHERE  NAME = 'msdb.'

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:

Use master
go
alter database [MSDB] set single_user with rollback immediate
GO
alter database [MSDB] set Enable_Broker
GO
alter database [MSDB] set multi_user with rollback immediate
GO

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.

use master
go
EXEC xp_servicecontrol N'querystate',N'SQLAgent$CMS'

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:

USE master
Go
EXEC sp_configure 'show advanced options', 1 --Enable advance option
Go
RECONFIGURE
Go
EXEC sp_configure 'Database Mail XPs,' 1 --Enable database Mail option
Go
RECONFIGURE
Go
EXEC sp_configure 'show advanced options', 0 --Disabled advanced option
Go
RECONFIGURE
Go

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:

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'Database Mail Default SMTP account'
  , @description = 'This account will be used to send database mail'
  , @email_address = '[email protected]'
  , @display_name = 'DBA Support'
  , @replyto_address = ''
  , @mailserver_type = 'SMTP'
  , @mailserver_name = '192.168.1.60'
  , @port = 25
Go

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

-- Create a Database Mail profile
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database Mail Profile'
  , @description = 'This profile will be used to send database mail'
Go

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:

-- Add the account to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database Mail Profile'
  , @account_name = 'Database Mail Default SMTP account'
  , @sequence_number = 1
Go

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:

use MSDB
go
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database Mail Profile'
  , @recipients = '[email protected]'
  , @subject = 'Automated Test Results (Successful)'
  , @body = 'The stored procedure finished successfully.'
Go

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.

[table id=53 /]

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:

IF EXISTS(SELECT * 
            FROM msdb.dbo.sysmail_profileaccount pa 
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
            WHERE p.name = 'Database Mail Profile' 
              AND a.name = 'Database Mail Default SMTP account') 
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Database Mail Profile',@account_name = 'Database Mail Default SMTP account' 
  END  
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = 'Database Mail Default SMTP account') 
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Database Mail Default SMTP account' 
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = 'Database Mail Profile')  
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Database Mail Profile' 
  END

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

SELECT * 
            FROM msdb.dbo.sysmail_profileaccount pa 
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
            WHERE p.name = 'Database Mail Profile' 
              AND a.name = 'Database Mail Default SMTP account'

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.