Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Enable SQL Server Agent Mail Profile


By:   |   Last Updated: 2019-04-17   |   Comments (2)   |   Related Tips: More > 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.


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.


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.


Last Updated: 2019-04-17


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 08, 2019 - 4:03:12 PM - Kenneth Igiri Back To Top

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 07, 2019 - 6:10:47 PM - Miguel Quintana Back To Top

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


Learn more about SQL Server tools