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

Views: 15.389
Reading Time: 11 minutes

Many people ask me, "Is it possible to send email through SQL Server?" Or "How to send email through SQL Server?". In this post I will show you how to enable email sending on your SQL Server server and how to send and monitor email sent.

One feature that is widely used on most systems is the automatic sending of emails containing notifications, promotions, newsletter and more. Therefore, it would be very interesting to send these emails directly from our database SPs, 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 Object Explorer in Management Studio, right-click the mousse Management> Database mail option and select the "Configure Database Mail" option.

2) You'll see the screen below, which is just a presentation of Database Mail. If you prefer, you can even check “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 you select the “Set up Database Mail by performing the following tasks” option on the previous screen, you will see this prompt asking if you want to enable Database Mail. Click the "Yes" button.

5) On this screen you can define the name of the email profile that will be required to send the emails and add the SMTP accounts you will use to send. Click 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 you have set and will be made to the instance after clicking 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 so, open Object Explorer in Management Studio, navigate to Management> Database Mail, and right-click the “Send Test E-mail…” option.

Just select the profile you created (In the example, it is MSSQLServer), fill in the recipient email that will receive the test and click the “Send Test Email” button.

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

If you have received the test email, simply click the "OK" button. If you did not receive it, click 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 have been sent or the error message while trying to send, see more in the “Tracking Email Sending” part 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 Log screen, in an interface similar to that of 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