How to Configure Database Mail

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users.

Configuring Database Mail using SSMS is the easiest way. Please see below the steps

 

Navigate to the Management folder of the appropriate server, right-click on Database Mail, and select Configure Database Mail

1

 

Click Next to pass the welcome screen

2

 

select the option Set up Database Mail by performing the following tasks and click Next

3

If database mail has not yet been enabled for this SQL Server instance, then you will see the screen shown. If it has been enabled, you won’t see the screen. Click Yes and proceed

 

FeatureNotAVailable

 

A profile is a collection of one or more SMTP accounts that can be used by SQL Server to send messages. In other words, when SQL Server wants to send a message, the message is sent to the profile, and then the profile is responsible for seeing that the e-mail is actually delivered. For fault tolerance, a profile can include more than one SMTP account.

To create a new profile, you must enter a profile name, an optional description, and then add and configure one or more SMTP accounts.

Enter Profile Name and Optional Description and click Add to create and configure SMTP Account.

4

 

Database Mail Account page.

5

To create/configure database mail account you need to fill in the details accordingly

Account Name

Optional Description

Outgoing Mail Server (SMTP). This specifies attributes of the SMTP Server that will be sending the e-mail, including these six options:

E-mail address – the e-mail account that has been set up for use with SQL Server’s database mail (for example, dbteam@company.com).

Display name – the display name of the above e-mail address that is shown as part of an e-mail.

Reply e-mail – the e-mail address used if someone should reply to an e-mail sent from the e-mail address entered above. Database Mail can’t respond to e-mails it receives, so you can either leave this option empty, or add your own e-mail address, just in case someone should respond to an e-mail received from SQL Server.

Server name – the name of the SMTP mail server.

Port number – the port number used by your organization’s SMTP server

This server requires a secure connection (SSL) – some SMTP servers require that SSL be turned on for additional security

6

There are three authentication modes available. In this tutorial, we have used Anonymous authentication (This option is rarely used because it allows anyone to access the SMTP server).  So, select accordingly.

Click OK to return to New Profile page

 

 

7

click on Next to reach the Manage Profile Security screen. I already had two profiles configured, that is why the below screen shows two more profiles.

Now you have to assign the profile as either public or private. A private profile is only usable by specific users or roles, while a public profile allows any user or role (with access to msdb) to send mail.

8

Click Next to move on to the Configure System Parameters screen.

This option in the Database Mail Configuration Wizard allows you to set the values of specific Database Mail parameters for the profile. Leave them to default and click Next

9

 

Review of the summary screen, click on Finish

10

 

If the Configuring… screen reports success, then Database Mail has been successfully set up

11

 

Now, In order to check whether the configured Database mail is working, right-click on the Database Mail folder in SSMS and select Send Test E-Mail,

12

 

In Database Mail Profile, select the profile just created and in To, type in your email address and click on Send Test E-Mail.

13

 

 

14

Database Mail has been configure.

 

 

Configure a SQL Server Agent Operator

configure a user to receive notifications about Microsoft SQL Server Agent jobs

When we configure a Maintenance Plan to send an e-mail, created with either the Maintenance Plan Wizard or the Maintenance Plan Designer, we can’t enter an e-mail address directly into the Maintenance Plan. Instead, we configure e-mails to be sent to an operator.

An operator is an alias for a specific person (such as yourself), or a group (such as a DBA mail group). This alias is more than just a name; it is actually a set of attributes that include the operator’s name, the operator’s contact information, and the operator’s availability schedule.

 

To create a new operator, open SSMS, navigate to the SQL Server instance you wish to configure, open up the SQL Server Agent folder, navigate to the Operators folder, right-click on it and select New Operator

15

 

New Operator screen has lots of options, we will focus only on the three that are most important.
Name – enter the name of the person or group who you want to receive notification e-mails.
Enabled – this option is selected by default, and you don’t want to change it, otherwise you won’t be able to receive any notices from SQL Server.
E-mail name – enter your e-mail address, or the group’s e-mail address here.

 

16

 

That’s it. All the other options are optional, and you can use them if you want to, we have left them to blank. click on OK

 

How To configure SQL Server Agent to use Database Mail

 

  • In Object Explorer, expand a SQL Server instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Click Alert System.
  • Select Enable Mail Profile.
  • In the Mail system list, select Database Mail.
  • In the Mail profile list, select a mail profile for Database Mail.
  • Restart SQL Server Agent.

 

 I hope this was helpful.

 

 

Advertisements

No global profile is configured. Specify a profile name in the @profile_name parameter

Yesterday, a developer from application team sent an email saying, he is not able to send mail alerts using database mail from a production server.

I asked which DB Profile name [@profile_name] you are using.   As we have different profiles in different servers.  [Which is not a best practice.  We should use same profile name within our environment to keep uniformity]

He was using the correct profile. Then I requested him to share the error details.

Error Details were as below:

“No global profile is configured. Specify a profile name in the @profile_name parameter”

I realized the profile was not configured as default profile.  So, I made the DB mail profile as default and it resolved the issue.

 

Below is the procedure to configure Global Profile in Database Mail.

  1. Connect to the target SQL Server with SSMS (SQL Server Management Studio)
  2. Expand the Managementsection
  3. Right-click on Database mailand select Configure Database Mail

1

4.  From the Wizard, click on Next >

2

5.  From the Task screen, select Manage Profile Security and click on Next>

3

6.  From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down. Select Yes from the drop-down then click on Next >

4

 

7.  From the Complete the Wizard screen, click on Finish

5