SQL Server- Implementation of Database Mail

1.     Background

The purpose of this article is to describe implementation of database mail configuration in SQL Server so that Database Mail can be utilized for sending email in various applications. Database Mail is easy to configure and maintain as well as one of the simpler way of implementing email notifications in the application.

2.     Pre-requisite                                    

1.   Database Mail configuration should be setup by service account which will be used for sending email notification.

2.   SQL Server Agent service should run.

3.   Service Account should have right of sending mail.

3.     Step by Step procedure to implement database mail:

                                i. Go to SQL Server Management Studioè Connect to the required SQL serve instanceè go to Management folderèDatabase MailèRight click on the Database Mailè click on Configure Database Mail:

  

                              ii. It will open the database mail configuration wizard, Click next

                            iii. Select the highlighted Database mail option and click next

                             iv. Give the name of the Profile e.g SQLCircuit and Description. Click on Add

                               v. Specify the details for the account which will be used for sending the mail e.g India\SQLCircuit :

We are using the following details for configuring Database Mail:

Account Name	India\SQLCircuit
SMTP Server	Smtp.gmail.com
SMTP port	465
Authentication	·         We are using Basic authentication here. For basic authentication, we need to pass store username and password in the database profile itself to authenticate on the SMTP server. ·         Windows Authentication is useful when we use SQL Server service account for authentication in SMTP server. ·         Anonymous Authentication: The SMTP server does not require any authentication. Database Mail will not use any credentials to authenticate on the SMTP server

                             vi. Now click on next to map Account to the profile. Here we have added Account name “India\SQLCircuit” to profile “SQLCircuit”

 

                           vii. Click next. It will open “Manage Profile Security”. Here you can make the profile public or private based on the requirement. Also you can set the profile as default.

                         viii. Click next, it will show system parameters for Database Mail

                             ix. Click Next to complete the wizard

Click finish.

4.     How to send email using Database Mail

                                i. For unit testing, go to Database mail, right click and select Send Test E-Mail

                              ii. Select the SQLCircuit Profile and give the email address to which you want to send notification. For testing purpose, I am sending mail to sqlcircuit@gmail.com  Click on ‘Send Test E-Mail’.

                            iii. Check the email and confirm that whether email is received or not. Below screenshot is showing that we have received the mail.

                             iv. We can send mail by using sp_send_dbmail system procedure. sp_send_dbmail is used send mail by using database mail profile configured in SQL Server. Use the below code to send mail to sqlcircuit@gmail.com

DECLARE @SUBJECTMESSAGE NVARCHAR(500)

DECLARE @tableHTML NVARCHAR(500)

SET @SUBJECTMESSAGE= 'Database Mail configuration has been completed'

SET @tableHTML = 'This is to inform you that Database Mail configuration has been setup in SQLCircuit Server.

Please utilize the notification services and please contact us at sqlcircuit@gmail.com for any queries

Thanks,

SQLCircuit Team

'

EXEC msdb.dbo.sp_send_dbmail

                     @recipients='sqlcircuit@gmail.com',   

                     @subject = @SUBJECTMESSAGE,   

                     @Profile_Name='sqlcircuit',  --Profile of SMTP Server 

                     @body =  @tableHTML,   

                     @body_format = 'HTML' ;

                               v. Check the email and confirm that whether email is received or not. Below screenshot is showing that we have received the mail.

5.     How to see the Database Mail Log

For checking the log of Database mail, right click on Database Mail and Select “View Database Mail Log”

 

It will show the complete log of Database Mail. We can utilize the log for troubleshooting the issues with database mail.

6.     Conclusion

By using the above steps, we can configure the Database Mail and can be utilize for sending email notification in various application.

----------------------------------------------------End of Document---------------------------------------------------