Database Mail is an important feature of SQL Server which helps users to send mail from SQL Server. It is mainly used to send notification related to jobs via mail. There are other use cases as well. To know more about how it works, please see our previous blog using below link.
Database Mail needs to be configured first in order to make use of it. In this blog we will see how to configure DB Mail in SQL server.
Pre-requisites
SQL Server installed (All Versions)
SMTP Details
Enabling Database Mail
Database mail needs to be enabled before starting configuration. It can be enabled in two ways.
First way is to configure it using system stored procedure “sp_configure”. Run below query in msdb to enable it.
EXEC sp_configure 'show advanced option',1
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs',1
RECONFIGURE;
Another way is to enable it via Configuration Wizard. Follow below steps to start configuration wizard.
1. In object explorer, expand Management under databases and right click on Database Mail.
2. Open Configure Database Mail.
3. Give Next in welcome page and you will land in task selection page.
4. Select first option since we are configuring it for the first time and give next. You will be prompted with a window asking permission to enable database mail. Give Yes to enable DB Mail.
Configuring DB Mail through Wizard includes following three steps.
Creating profile and accounts
Specifying profile security
Configure system parameters
Step 1 – Creating profile and accounts
If you enabled Database Mail using T-SQL, open Configuration Wizard by following below steps.
1. In object explorer, expand Management under databases and right click on Database Mail.
2. Give Next in welcome page.
3. In task selection page, select “Set up Database Mail by performing the following tasks” option since we are configuring it for the first time and give Next.
If it was done using configuration wizard, it will be automatically moved to next step. The window will look as below.
Profile Name: Name to identify the profile while sending mail.
Description (Optional): Mention about the profile.
Note that one profile can have multiple accounts. Profile will become more reliable when configured with multiple accounts.
Click on Add to configure SMTP details for an account.
Account Name: Mention any name for reference.
Description (Optional): Describe about the account.
E-mail address: The Email address which can be used to connect with the SMTP server.
Display name (Optional): Name which you would like to be displayed in the received mail.
Reply e-mail: Mail address to which all the replies should be sent.
Server name: Name or IP Address of the SMTP server. You can specify your SMTP server name if you have one. Or if you are using third party SMTP service such as Outlook, Gmail, Yahoo you can get the SMTP server name and related details from their official website.
For this demo I am going to use my Outlook account and “smtp-mail.outlook.com” is the smtp server for the same.
Port Number: Port which should be used for connecting with your SMTP server. Default port number is 25, but it may vary for based on your SMTP server and get the same from your administrator or provider.
Check “This server requires a secure connection (SSL)”, if your SMTP server require SSL.
SMTP Authentication: Based on your server configuration, you can choose any one of the following options.
Windows Authentication: The credential which is configured to login to SQL Server database engine will be used.
Basic Authentication: Enter user name and password which will be required by your SMTP server.
Anonymous Authentication: Select this option if the SMTP server doesn’t require any authentication.
Once you have added all the required fields, click Ok.
You can add multiple accounts if you want by clicking Add option on right side. Also, you can adjust the priority by moving rows Up and Down. Once you are done with adding accounts, give Next.
Step 2 – Managing Profile Security
In this step you can configure the profile either as Public or Private.
Public: Public profiles can be used by all the users.
Private: Only specified group of users can access private profiles.
Default: You can also define a default profile for each category.
If you want to configure the profile as public, go the public tab and select that profile. Follow the same for private profiles.
Select Yes in default column if you want to make that profile as default. Please note that only one profile can be set as default. When default profile is configured, it will be used if a profile is not explicitly mentioned at the time of sending mails.
Select “Show only existing public profiles” option to show the existing public profiles alone. So, when you are in the process of adding new public profile, enabling this option will only display the profile which is already added, since the adding process is still in progress for the new profile.
Step 3 – Configuring Database Mail System Parameters
The next step is to configure system parameters.
Account Retry Attempts: Describe how many times the external program can try to send e-mail to the SMTP server using each account in the profile.
Account Retry Delay: This describes the delay between each attempt. This value is in seconds.
Maximum File Size: Maximum size of file attachment which can be sent in the mail. This value is in bytes.
Prohibited Attachment File Extensions: This can be set to prevent files with specified extensions from being sent. By default, exe, dll, vbs, js files are prohibited. You can add additional extensions by clicking on the three dots. Entries should be separated by comma.
Database Mail Executable Minimum Lifetime (seconds): Defines how long the external program remains active before it shut down when there is no mail in the queue.
Logging Level: This parameter describes kind of events that needs to be logged in Database Mail log. You can choose either of the following levels.
Normal: To log only Errors.
Extended: To log Errors, Warnings, and Informational messages.
Verbose: This provides high level of logging. It logs Errors, Warnings, Informational messages, Success messages and any other internal messages. It is mainly used for troubleshooting.
Default logging level is Extended.
You can also hover on each parameter name to see its description. At any point of time, you can reset all the values to default by clicking on Reset All option.
Step 4 – Completing the Wizard
Verify the summary of actions which will be performed and click Finish.
Final page will show status message for each step. You can either save the report or email it by selecting the option from Report drop down if you would like. Otherwise close the window.
Now our sql server is ready to send mail. We can test the configuration by sending test e-mail. Follow below steps to send test e-mail.
In Management, right click on Database Mail and select “Send Test E-Mail”.
Select the profile you just created, enter recipient address. Also, alter the subject and body if you want.
Following window appears after you clicked “Send Test E-Mail”.
Make note of the number displayed in the text box. Check your mail client to confirm the mail delivery. If you haven’t received the mail, click on Troubleshoot.
You can also check the delivery status by querying “sysmail_allitems” view in MSDB. Execute below query in MSDB to check the status.
SELECT * FROM sysmail.allitems
WHERE mailitem_id=<Number you noted in earlier step>The result set contains details about the mail you sent and the status whether it is sent or unsent.
Please leave your comment if you have any queries. Thank you for reading!!!