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.
