Configuring Database Mail

One of the most useful component of SQL Server is Database Mail. By the help of database mail, you can send important email notification for;

• Alerts for any events that occur in databases, jobs or etc.
• Query Results or reports as html or with attachments.

After talking about what we use database mail for, let’s talk about how it is configured.

Configure Database Mail

Configuring database mail has three steps;

Step 1- Enable Database Mail XPs

We need to enable Database Mail configuration if Database Mail configuration is disabled. To check its status;

Note: Database Mail XPs is an advanced option and before taking actions on this option, it is needed to enable ‘show advanced options’. Otherwise, below error is thrown;

sp_configure 'show advanced options',1
go
RECONFIGURE
go
sp_configure 'Database Mail XPs'

run_value shows us the current situation of the configuration.

1 stands for enable, 0 stands for disable.

if the value is 0, to enable it;

sp_configure 'show advanced options',1
go
RECONFIGURE
go
sp_configure 'Database Mail XPs', 1
GO
RECONFIGRE
GO

To configure, SSMS or T-SQL can be used. I will start to show with SSMS and then share the T-SQL codes.

Get Kendime Notlar’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

In Object explorer, click and expand Management folder and right click to Database Mail and click Configure Database Mail.

In the opening screen, click next,

Then choose Set up Database Mail by performing the following tasks, and click next

Step 2- Create Profile

You need to write a Profile Name and if you want, you can add a description for the profile and click Add.

To create a profile via T-SQL following scripts can be used;

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'Notification',  
    @description = 'Profile that is used to notify' ;  
GO

After creating a profile, it is needed to give permission to use Database Mail profile. Following script will perform this operation;

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'Notification',  
    @principal_name = 'public',  
    @is_default = 1 ;
GO

Step 3- Create Account

After creating a profile, you need to create an account. Account needs some specific information like email address, and credentials, etc.

Here is the explanation of the fields that are used in account creation;

• Account Name: Name of the Account that you want to create (necessary)
• Description: Description of Account (optional, can be blank)
• E-mail address: Valid e-mail addres that emails can be sent (necessary)
• Display name: Pretty Name that will shown in email (optional)
• Reply e-mail: e-mail address that is used for replies to e-mail messages sent from this account.
• Server Name & Port: stmp server name or IP address and port which the email account uses to send email
• This server requires a secure connection (SSL): Encrypts communication by using SSL
• Windows Authentication using Database Engine service credentials:
  Use this option if the Sql Server Database Engine servise is configured to use SMTP server.
• Basic Authentication
  Write the user name and password to communicate by SMTP by the SMTP server.
• Anonymous authentication
  If the SMTP server does not require to use credential/authentication, use this option.

To create an account via T-SQL, following script can be used by changing the values according to your environment information;

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'NotificationAccount',  
    @description = '',  
    @email_address = 'notification@servername.com',  
    @display_name = 'Sql Notifiation',  
    @mailserver_name = 'smtp.servername.com',
    @port = 25,
    @enable_ssl = 1,
    @username = 'notification@servername.com',
    @password = '1111' ;  
GO

After creating database mail account, it is needed to add Database Mail Account to Database Mail Profile that we created. Following script can be used for this operation;

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'Notification',  
    @account_name = 'NotificationAccount',  
    @sequence_number =1 ;  
GO

Test Database Mail

After all steps are done successfully, we are ready to test the database mail. Again there are two ways to send a test email;

• By SSMS

We can send a test email by the same place that we configure Database Mail. Click the Send Test E-Mail.

Then, choose the Database Mail Profile that you want to send email. Add To information and replace Subject and Body with your information.

• By T-SQL

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Notification',
     @recipients = 'email@email.com',
     @subject = 'Database Mail Test';
     @body = 'This is a test e-mail sent from Database Mail on Server.',

GO

This will be the output of the query result.

I tried to explain how database mail option can setup and used.

Hope this post will be helpful.

Feel free to contribute to make my writings get better and useful.