Configure DBMail for SQLExpress on Windows Azure

SQL Server Express is a very common version of SQL Server used when creating a website. In this article we will have a look at how you can configure DBMail in your SQL Server Express instance on a Windows Azure virtual machine. The majority of steps have not changed since SQL Server 2005 was first released.

Configure SMTP Service

For SQL Server DBMail to work on your Azure Virtual Server we need to have access to SMTP service to be able to send emails. To configure the SMTP service we have to add the SMTP server feature.

1. Start Server Manager | Add Roles and Features
2. Click Next until you are on the Select Features Page
3. Scroll down until you find SMTP Server | Ensure you check this option
4. Click Next through to the end and then install the feature.

Now that we have the SMTP Server feature installed we need to configure our environment in preparation for use with our instance of SQL Server SQL Express. Start IIS 6.0 Manager from the installed programs and expand the local computer object until you can see SMTP Virtual Server.

1. Right Click on the SMTP Virtual Server | Properties.
2. On the Access Tab | Select Authentication and ensure “Anonymous Access” is selected
3. On the Access Tab | Select Relay and ensure “All except the list below” is selected
4. On the Delivery Tab | Select Outbound Security
   1. Enter your SMTP provider details in Smart Host
   2. Eg – smtp.gmail.com
5. On the Delivery Tab | Select Outbound Connections
   1. Enter the associated Port for you your SMTP provider
   2. Eg – 587
6. On the Delivery Tab | Select Advanced
   1. Select Basic Authentication
   2. Enter your email account and password associated with your SMTP provider above
   3. Eg – [email protected]
   4. Ensure TLS Encryption is selected

Congratulations we have just configure our SMTP server that we are going to use with our SQL Server Express Instance.

Configure DBMail in SQL Express

To complete our setup to be able to send emails from within our Instance of SQL Server Express we need to configure DBMail. This configuration is normally pretty simple and is able to be completed from within SQL Server Management Studio (SSMS) however as SQL Express is a cut down version we do not have the ability to do this all via the GUI wizard.

The steps to take are listed below and should be run in SSMS on your SQL Server Express Instance.

Enable DBMail

-- Enable Advanced options to confirm DBMail is not already enabled
sp_configure 'Show Advanced Options',1
GO
RECONFIGURE
GO

-- Enable DBMail configuration option
sp_configure 'Database Mail XPs',1;
GO
RECONFIGURE
GO
-- Turn off Advanced Options
sp_configure 'Show Advanced Options',0
GO
RECONFIGURE
GO

Create DBMail Account

-- Create DBMail Account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailNotification',
@description = 'Mail Account for Email Notifications',
@email_address = '[email protected]',
@display_name = 'My Account',
@mailserver_name = 'localhost';

Create DBMail Profile

-- Create DBMail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MailProfile',
@description = 'Mail Profile';

Add DBMail Account to DBMail Profile

-- Add DBMail Profile Account
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailProfile',
@account_name = 'MailNotification',
@sequence_number = 1;

Set DBMail Principal Profile

-- Add DBMail Principal Profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MailProfile',
@principal_name = 'Public',
@is_default = 1;

Send a Test Email

DECLARE @EmailBody NVARCHAR(255);
SET @EmailBody = 'Test Email Notification setup';

EXEC msdb.dbo.sp_send_dbmail 
    @recipients='[email protected]',
    @subject = 'Blog Post Comment Notification',
    @body = @EmailBody,
    @body_format = 'HTML';

Enjoy the use of your DBMail setup.