Configure Database mail in SQL Server

Prerequisites to Enable Database Mail

Before Configure Database mail we must enable Service Broker for MSDB database and also need to check SQL Server Agent service running or not.

Check Service Broker status

Execute the following query to check that the service broker is enabled or not.

If it is disabled, then enable the service broker by executing the below queries

SQL Server Agent service running status

Execute below command  to check the status of agent service

If the SQL Server Agent service is ‘Stopped’, start the SQL Server Agent service. Use SQL Server configuration manager to start it.

Or Run ‘services.msc’ to open Services. Search for SQL Server Agent. Right-click on SQL Server Agent and select Start.

Configuring Database Mail by SSMS

In Object Explorer, Expand Management and right-click the select Configure Database Mail

Welcome screen, Click Next

Select the Setup Database Mail by performing the following tasks radio button and click Next.

By default, the database email feature is not enabled. It must be enabled, simply click ‘Yes’

In Profile name provide a profile name, here we have given ‘SQL_DB_Email’. The description is optional and click the Add button to create an SMTP account.

In Account name, provide Account NameDescription (optional). In Email address we are using GMAIL, in Display Name we are using same as Account Name & Reply Email (Optional). In SMTP server and Port Number we are using GMAIL SMTP server & port.

SMTP account will be listed in the SMTP accounts grid view.

Click Next to configure the database mail profile security. We will configure the database email profile public. Any database user or role can send emails using Public profile. Users must be created in the MSDB database, and those users must be a member of a database role named DatabaseMailUserRole to send an email. We can also make the profile as a default profile. Once profile security sets, click Next to configure the system parameter.

Configure System Parameter window, we can set a number of retry attempts, maximum attachment file size, list of file extensions that are prohibited from sending as an attachment.

Review the entire configuration and list of actions. Click Finish to complete.

You may view the report, then close.

Test email using SSMS

To send a test email using SSMS, Open SQL Server Management Studio >> Expand Management in SQL Server Management >> Right-click  Database Mail and select Send Test Mail.

In the Database Mail Profile drop-down, select the name of the profile used to send an email (Database Mail Profile), In the To text box, provide the email address of the email recipient and click Send Test Mail.

Click ‘OK’

Check your email inbox.

We can also send a test emails using the T-SQL code. Execute the following T-SQL script

Check your email inbox.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *