Configuring database mail on SQL Server 2008 R2

Peter Schmitz

Staff member
Database mail can be a very useful tool in case you administrate one or more SQL Servers, as you could configure the server to automatically send out mails upon completion (either succesful or failure) of jobs, to notify you of alerts you configure, etc.

To configure Database mail, log onto the server using SQL Server Management Studio (SSMS), and then expand the server and the "Management" folder below it:

database mail 1.png
Right-click it to bring up the Database Mail menu:

database mail 2.png

Click on "Configure Database Mail". This fires up the "Database Mail Configuration Wizard":

database mail 3.png

Click "Next". You will now be prompted to select a setup option. In our case, we will set up Database Mail from scratch, so we use the first option (default):

database mail 4.png

Click "Next". If you are running a default installation, you will now be prompted on whether you want to enable Database Mail:

database mail 5.png

Click "Yes". In the "New Database Mail Account" screen, you will can specify a name and description for the for the mail account configuration, and you will need to fill in the E-mail address and SMTP you want SQL Server to send the mails from. When in doubt, your network administrator should be able to provide you with those. You can also provide the security settings.

In the screenshot, the "Anonymous Authentication" option is used. I would not recommend this in a production environment setting.

database mail 6.png
Click "OK".

database mail 7.png
You could add additional profiles if you want. To do so, click the "Add" button on the right. Otherwise, click "Next":

database mail 8.png

You now need to decide whether you want to configure the mail account as public or private. Books Online (BOL) has the following to say about public or private profiles:

Profiles are either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (msdb) to send e-mail using that profile.

A profile may be a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile. If the user or role sending the e-mail message has a default private profile, Database Mail uses that profile. If the user or role has no default private profile, sp_send_dbmail uses the default public profile for the msdb database. If there is no default private profile for the user or role and no default public profile for the database, sp_send_dbmail returns an error. Only one profile can be marked as the default profile.

To send Database Mail users must also be a added to the DatabaseMailUsersRole database role in the msdb database using Management Studio or sp_addrolemember.

You can specifically state a public or private profile, or simply click "Next" to accept the profile to become public. Now you will be able to add some additional configuration settings if you want to:

database mail 9.png

A brief oversight of the individual options can be found in the table below:

Account Retry AttemptsNumber of retry attempts for a mail server to send e-mail.
Account Retry Delay (seconds)Delay between attempts for a mail server to send e-mail in seconds.
Maximum File Size (Bytes)Maximum file size in Bytes for an attachment for a mail serer to send e-mail.
Prohibited Attachment File ExtensionsProhibited file extensions for a mail server to send e-mail.
Database Mail Executable Minimum Lifetime (seconds)Minimum lifetime for Database Mail executable in seconds.
Logging LevelDetermines which events are written to the Database Mail event log.

When you are satisfied with the changes you made, click "Next". The wizard will show you an oversight of the choices you made:

database mail 10.png

Click "Finish" to implement your Database Mail according to your specifications. When it is done, you will receive a screen showing you whether the setup was successful:

database mail 11.png

Click "Close".

To test the new Database Mail, right-click "Database Mail" again, similar to step 2 from before. This time, select the option to "Send Test Email":

database mail 12.png

You will be prompted to fill in a recipient address, and you can configure the subject and body of the mail:

database mail 13.png

Click the "Send Test E-Mail" button to have the message be sent to the queue of the SMTP server:

database mail 14.png

If all went well, you will receive an E-mail that carries all the configured options from this tutorial:

database mail 15.png

In summary, setting up Database Mail is a quick and easy process. In future articles we will put it to good use when we configure operators and use Database Mail to send mails to these operators in case of successful or failed operations.