Enable SQL Server Agent Mail Profile

Problem

Database Mail is a feature in SQL Server that allows database applications to send email via SMTP. The database engine actually queues messages to be sent and these messages are passed to an external process to handle the email sending. After completing configuration of Database Mail using the Wizard provided in SQL Server Management Studio (SSMS), you may notice that while emails can be sent using the stored procedure sp_send_dbmail from SSMS, SQL Server Agent is unable to send emails on the completion of background jobs or to operators as configured in alerts.

Solution

The most likely cause of this problem is that the Mail Profile has not been enabled in the SQL Server Agent properties. This articles shows how to get this done.

Configuring SQL Server Agent Database Mail

There are two key objects you setup when configuring Database Mail – the Mail Profile and the Mail Account.

The mail profile is an ordered collection of mail accounts that provides an abstraction layer between the applications calling database mail and the actual mail accounts. A mail profile is defined in terms of security as a public or private profile. A public profile is available for any principal’s use while a private profile is available only to members of the sysadmin role and other principals that have the appropriate permissions. In the context of SQL Agent, when a profile is private, the SQL Agent service account must be granted privileges to use the profile. When a profile is NOT specified as the default profile, the caller must specify the profile name when executing sp_send_dbmail.

Figure 1 – Mail Profile Security

When applications call database mail, they refer to the profiles rather than specific mail accounts. This means that the mail profile can be associated with several accounts as shown in Figure 2 below. This capability provides some kind of fault tolerance assuming you have configured your separate accounts using different mail servers. Beyond this, you can also have more than one mail profile and decide which should be the default. When the sp_send_dbmail stored procedure is called without specifying a mail profile, the default mail profile is used. If no mail profile has been designated as the default, sp_send_dbmail will return an error.

Figure 2 – Multiple Mail Accounts

SQL Server Agent Unable to Send Mail

The error shown in Figure 3 below is the typical error one sees when SQL Server Agent is unable to call Database Mail. It would normally be thrown when a job configured to send a notification runs or when an alert condition is met and there is an attempt to fire the alert. The fix is quite simple and involves enabling the Mail Profile in the SQL Server Agent properties.

Figure 3 – Agent Unable to Send Email

Steps to Enable Mail Profile

From Object Explorer on SQL Server Management Studio, we right click SQL Server Agent and select Properties from the drop-down menu.

Figure 4 – SQL Agent Properties

In the resulting window, we find that SQL Server Agent Properties allows us to configure a large number of options for controlling SQL Server Agent’s behavior. These options are grouped by pages. In the present case, we select the Alert System page. On the right pane we observe that the Mail Profile is not enabled by default.

Figure 5 – Mail Profile Disabled

We then enable the mail profile by checking the box beside Enable Mail Profile and selecting the profile we created while configuring Database Mail (See Figure 6). If no Mail Profile has been previously configure, the Mail Profile drop down will be empty.

Figure 6 – Mail Profile Enabled

The following piece of code achieves the same result using T-SQL.

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
   @email_save_in_sent_folder=1, 
   @databasemail_profile=N'SQL Mail Profile Websense', 
   @use_databasemail=1
GO

In order for the change to take effect, we must restart SQL Server Agent. This can be done from SQL Server Management Studio (SSMS), the Windows Services or SQL Server Configuration Management. In the case of a clustered instance, it would be better to achieve this using the Failover Cluster Manager by taking the SQL Agent resource offline then bringing it back online.

Figure 7 – Restart SQL Server Agent

Next Steps

• Review your SQL Agent Error logs to determine whether you may be facing this problem. Then apply the solution.
• Learn more about Database Mail.
• Learn more about SQL Agent.

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A. Ecobank’s technology services hub. He has been working in the Information Technology sphere since 2008 after graduating with a Second-Class Upper Bachelor’s Degree in Electronic and Computer Engineering. While in the University, he had significant hands-on experience deploying 802.11b/g technologies for Internet access. His journey in IT has also taken him through IT Service Management based on the ITIL framework, application support, and currently, database ddministration.

Kenneth has been working with SQL Server and Oracle databases and their related technologies since 2010. He has been involved in deploying databases (both oracle and SQL Server), implementing High Availability Technologies such as AlwaysOn FCI and AG, Transaction Log Shipping and MySQL Replication. He has also worked on security related areas such as Hardening and Patching for PCI-DSS Compliance, database configuration for audit compliance as well as database consolidation projects.