Enable SQL Server Agent Mail Profile

By:   |   Comments (3)   |   Related: > SQL Server Agent


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.

SQL Server Agent Mail Profile Security
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.

Multiple Mail Accounts in SQL Server Agent
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.

SQL Server Agent Unable to Send Email
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.

enable mail profile for sql server agent 004
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.

SQL Server Agent Mail Profile Disabled
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.

Enable SQL Server Agent Mail Profile
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.

enable mail profile for sql server agent 007
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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth Igiri Kenneth Igiri is a Database Administrator with eProcess International S.A. Ecobank’s technology services hub. He has over eight years of experience with SQL Server and Oracle.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, October 7, 2021 - 12:15:21 PM - Gabe Back To Top (89319)
Great tip, Miguel! It appears this is fixed in SQL 2016 SP1 but there are also two other 'workarounds' listed in this MS KB article (KB3186435):

https://support.microsoft.com/en-us/topic/kb3186435-fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-does-not-have-the-net-framework-3-5-installed-or-stops-working-after-applying-sql-server-update-3480beb6-1329-74d6-0f3a-e8e3d893326c

Wednesday, May 8, 2019 - 4:03:12 PM - Kenneth Igiri Back To Top (79994)

Absolutely right Miguel. In our experience .Net 3.5 NEVER got installed by default on Windows 2012 and above. When we checked there didn't seem to be any official documentation stating .Net 3.5 was required for SQL Server 2016. We always had to enable .Net 3.5 as a feature by running the Roles and Features wizard from Server Manager.


Tuesday, May 7, 2019 - 6:10:47 PM - Miguel Quintana Back To Top (79977)

Keep in mind that SQL2016 still needs Net framework 3.5 for database mail to work, and it doesn't always get installed by default on Windows 2012 or 2016 ugh.

The error is weird too... the email gets stuck on "unsent" status.

It took me really long tine to figure this out.

Miguel















get free sql tips
agree to terms