SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail)

Views: 16.377
Reading Time: 11 minutes

Many people ask me, "Is it possible to send emails via SQL Server?" or “How do I send emails via SQL Server?”. In this post I will show you how to enable the sending of emails on your SQL Server and how to send and monitor the emails sent.

A feature that is widely used in most systems, is the sending of automatic emails containing notifications, promotions, newsletter and much more. Therefore, it would be very interesting to send these emails directly from our SP's in the database, which can be scheduled by the SQL Server Agent to automate their execution.

Do not forget that for the user to be able to send email through SQL Server, he must be in the database role DatabaseMailUserRole msdb database (or elevated permissions, such as msdb owner, sysadmin, etc.)

Enabling server e-mail sending using T-SQL

View how to enable and configure Database mail via command line (using T-SQL)
By default, SQL Server comes with email sending options disabled as a security measure. I will show below how to enable these options:


Enabling SSMS to Send Mail on the Server

View how to configure Database Mail through Management Studio
Another way to enable and configure Database mail in SQL Server is through the SQL Server Management Studio interface.

1) Open the Object Explorer in Management Studio, right-click the mousse in the Management> Database mail option and select the option “Configure Database Mail”

2) You will see the screen below, which is just a presentation of Database Mail. If you prefer, you can even check the option “Skip this page in the future”, so that this screen no longer appears.

3) On this screen you will see 3 options. Initially select the first option (Set up Database Mail by performing the following tasks)

4) By default, the Database Mail feature is disabled on the SQL Server instance. When selecting the option “Set up Database Mail by performing the following tasks” on the previous screen, you will see this warning asking if you want to enable Database Mail. Click the “Yes” button

5) On this screen, you can define the name of the e-mail profile that will be needed to send the e-mails and add the SMTP accounts that you will use to perform the sending. Click on the “Add” button to add an SMTP account.

6) Here you will add your email account details, always remembering to check your email provider's SMTP address, port number, if the connection requires secure connection (SSL) and other authentication details.

7) Your SMTP account is now set up and you can now view it on the Profile SMTP accounts screen.

8) In this screen we will configure the security of the created email profile, ie define who can use it. You can mark this profile as public or private, where only a few users have access. I usually leave the profile public, but limit users' access to the sp_send_dbmail procedure via GRANT.

9) Here you can view and change Database Mail parameters such as maximum attachment size, prohibited attachment extensions, and email retry (which tries to send email again in case of failure)

10) And finally, you will see a summary of the changes that you have configured and that will be made in the instance after clicking on the “Finish” button.

11) Changes made successfully.

Testing email sending

After configuring Database Mail, we will test if the server is allowing sending normally. To do this, open Object Explorer in Management Studio, navigate to Management> Database Mail and right click on the option “Send Test E-mail…”

Just select the profile you created (In the example, it is the MSSQLServer), fill in the email of the recipient who will receive the test and click on the “Send Test E-mail” button

After submitting the test, a screen will open confirming that the email went to the Database Mail queue.

If you received the test email, just click on the “OK” button. If you haven't received it, click on the “Trobleshooting” button to be taken to the Microsoft help page (https://technet.microsoft.com/pt-br/library/ms187540(v=sql.105))

Sample test email sent by SQL Server Database Mail:

To view the Database Mail log and check if your emails were sent or the error message when trying to send, see more in the “Monitoring email sending” section of this post, below.

Sending Email

Now that we have set up the server, we are ready to send our emails. The sp we will use is sp_send_dbmail, from SQL Server itself, which allows sending text or HTML emails and attachments.

Sending Email in HTML Format

Sending Email Concatenating the Result of a Query to the Message Body

Sending email with query result as attachment (CSV)

Emailing 2 Files Attached

Monitoring the sending of emails

Tracking actions is always important in IT. Sending emails is no exception to the rule. As much as it is possible to monitor emails through the mail server, it is not always so fast to be able to validate this, mainly because it is a bit out of scope for the DBA / Scheduler, where this control is usually controlled by the Infrastructure team.

In addition, the mail server keeps logs of all email traffic from all accounts, making it harder to extract this information than simply querying SQL Server catalog tables, which are msdb.dbo.sysmail_mailitems and msdb.dbo.sysmail_event_log.

To make it easier to extract information, I usually create this view, and then just query it:

Email Monitoring

Another way is by using the SQL Server Management Studio interface.

After selecting the “View Database Mail Log” option, you will see the Database Mail logs screen, in an interface similar to the SQL Server Activity Monitor

Quick Troubleshooting Help (Troubleshooting Database Mail)

If you're having trouble setting up or sending emails, here are some tips that can help you resolve these issues.

Validate access data

This topic may sound silly, but make sure that you correctly entered the SMTP server address, port, user, password, and if you checked the option to use SSL if your SMTP server requests it.

I recently had a big battle with Database Mail to set up one Yahoo account and one hosted on Hostgator. Although the documentation indicates that I should use the 465 (SSL) port in both cases, I have used an email client (Outlook) configured using the 465 port and sent email normally and made a push test using the 465 port on a VBscript successfully, in Database Mail I could only send when I put the 587 (TLS) port. On port 465 the email was not sent at all, probably because it was SSL protocol.

Therefore, consult your email provider's documentation, and if you have problems setting up Database Mail, try using other alternate ports (Providers generally provide two ports for secure SMTP sending, one SSL and one TLS).

Verify that Service Broker is enabled for database msdb (must return 1):

If the query result is 0, then Service Broker is not active. As a result, sent messages will not be queued by it and sending will not occur. To fix this, run the command below:

I have seen several reports of people trying to activate Service Broker in MSDB, but the process crashed or simply did not activate because Broker could not get the locks in MSDB to make the necessary changes. If this happens to you, either drop all MSDB connections or restart the SQL Service service and try again.

Check if Database Mail is running on MSDB database:

Check message queue status:

Check Database Mail Logs:

Check information about sent items and message errors:

Firewall and Antivirus

Often the sending of E-mail may end up not happening because Firewall / Antivirus blocks the attempt of sending messages by SMTP protocol by the Database Mail process. One way to ensure that the problem is not Firewall is to either release the port used by the connection (usually 25, 465 or 587) or even disable Firewall / Antivirus temporarily just to test if they are blocking the sending or is it some other way. thing.

Send an email test using VBscript script:

Another alternative of checking if the problem is with SQL Server or the server is by trying to send an email in another way. For this you can use the VBscript script below:

After you create the script, simply run it from Command Prompt (CMD.exe):

Validate network protocols

Another validation that can be done is whether Named Pipes and TCP / IP network protocols are enabled for your instance. If not, enable and restart your instance's SQL Server service.

Review the SQL Server Service Account

Another point to consider if you are unable to send your emails is to verify that the service account being used to run SQL Server has permissions to access the external network. It does not necessarily have to be a Local Administrator or Domain Admin account, but it does need to have basic network access permissions to be able to send SMTP email.

For more details about service accounts, visit this link.

Install the latest version of Service Pack and Cumulative Updates

Microsoft SQL Server 2016 SP1
Microsoft SQL Server 2016 Latest Cumulative Update
For other editions, visit this link.

Install the latest version of Service Pack and Cumulative Updates

If you are using SQL Server 2016 RTM or SP1, be aware of a common Database Mail issue, which is that you need to install Microsoft .NET Framework 3.5 on your server to prevent your messages from getting stuck in the SQL Server queue. Service Broker and always have unsent status. Otherwise, Database Mail will not work due to a product bug that was fixed in Cumulative Update 2 (Reference Link), reoccurred on the 1 CU1 Service Pack and has been fixed again in the 1 CU2 Service Pack (Reference Link).

Microsoft link to help troubleshoot Database Mail issues
Troubleshooting Database Mail

How to send email mail from sql server, how to send email mail from within sql server

How to send email mail from sql server, how to send email mail from within sql server