Configuring Database Mail (dbmail) on SQL Server Express 2008

Database Mail (aka DBMail) is an enterprise solution for sending e-mail messages from the Microsoft SQL Server Database Engine. It is easy configurable on a full scale MS SQL Server via Database Mail Configuration Wizard. None of SQL Express versions so far has this convenience, which means on SQLExpress the DBMail has to be configured manually.

All required information can be found on this page Enabling Database Mail on SQL Server Express. The most of it is applicable to either 2005 or 2008 SQLExpress version. For my own comfort (for sure I will need this on a later occasion) I wrote down all today's steps that lead to a successful DBMail configuration.

SQL Server Express instance configured today

• Product Version: 10.50.1600.1
• Product Level: RTM
• Edition: Express Edition with Advanced Services (64-bit)

DBMail Support Files
SQLExpress 2005 installation does not include DBMail support files:

• DatabaseMail.exe
• DatabaseMailengine.dll
• DatabaseMailprotocols.dll

Get these files from a regular SQL Server 2005 installation (not a Express one) and copy to MSSQL\Binn folder. No registration is required for the two DLL files.

SQL Server Express 2008 does contain DBMail support files. On my computer they are located in
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn

Starting DBMail
The following script turns the Database Mail option on.
USE master

GO
sp_configure 'show advanced options', 1
GO
reconfigure with override
GO
sp_configure 'Database Mail XPs', 1
GO
reconfigure 
GO
sp_configure 'show advanced options', 0
GO

use msdb
exec dbo.sysmail_start_sp

Using Database Mail Configuration Wizard in SSMS
If you are not on Express Edition, this Wizard is a great help and convenience. Otherwise skip to the next topic.

I am surprised that neither the SSMS nor the wizard allow a profile to be scripted out.

Using SSMS template
Each SQL Server SSMS install comes with a variety of templates. "Simple Database Mail Configuration" template has all basics scripted nicely.

All you have to do is to fill in values for Profile, Account, SMTP Server, Email Address, and Display Name. Though note that this a simplified script. For example, as it is, it does not prompt where to put values for SMTP port and SSL.

Choosing email account
Before going any further, gather email account settings to be used. The following settings are absolutely required. Replace them with your values.

• Email Address: myaddress@mydomain.com
• Display Name: John Smith
• Mail Server: smtp.mydomain.com
• Port: 25 -- this is default SMTP port
• User Name: myaddress@mydomain.com
• Password: mypassword
• SSL enabled: Yes/No

Creating new profile
use msdb
-- creates new profile

exec msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DefaultMailProfile',  -- provide your own value
@description = 'Default email profile'  -- provide your own value
GO


-- sets new profile as the default one

exec msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DefaultMailProfile',  -- provide your own value
@principal_name = 'public', -- do not change
@is_default = 1 -- do not change
GO



Creating an account and adding it to the profile

use msdb

-- creates an account

exec msdb.dbo.sysmail_add_account_sp

@account_name = 'DefaultMail',

@description = 'My email account',

@email_address = 'myaddress@mydomain.com',

@display_name = 'John Smith',

@replyto_address = null,

@mailserver_name = 'smtp.mydomain.com',

@username = 'myaddress@mydomain.com',

@password = 'mypassword',

@port = 25,

@enable_ssl = True

GO

-- adds the account to the default profile
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DefaultMailProfile',
@account_name = 'DefaultMail',
@sequence_number = 1
GO

Sending a test email

exec msdb.dbo.sp_send_dbmail 
@recipients='myfriend@myfrienddomain.com',
@subject = 'Test email sent via DBMail',
@body = 'This is a test email. Please do not reply.',
@reply_to = 'myaddress@mydomain.com'
GO

Verifying that the test email left the server

The last script normally returns message "Mail queued". There is no indication of a mail actually leaving the database server. Query msdb.dbo.sysmail_log and msdb.dbo.sysmail_mailitems tables to obtain complete results.

First of all, there should be recently created sysmail_log records indicating that "The mail queue was started...", and "DatabaseMail process is started".

A failed attempt to send an email creates sysmail_log entry with description "The mail could not be sent to the recipients because of...". Read the description and take appropriate steps. May be validate again email settings: SMTP server, port, user name and password.

DBMail tables and views
Database MSDB contains several DBMail tables. Note that these are all System Tables. The above configuration scripts make changes in DBMail tables. But of course a carefully calculated manual change is also an option.

Tables:

• sysmail_mailitems
• sysmail_log
• sysmail_configuration
• sysmail_servertype
• sysmail_profile
• sysmail_principalprofile
• sysmail_account
• sysmail_server
• sysmail_profileaccount
• sysmail_attachments
• sysmail_attachments_transfer
• sysmail_query_transfer
• sysmail_send_retries

Views

• sysmail_allitems
• sysmail_eventlog
• sysmail_faileditems