Wednesday, November 3, 2010

Windows Server 2008 and SQL Server 2008 - Database Mail Setup

I set up Database Mail on Windows Server 2008 and SQL Server 2008.  It wasn't easy, so I hope this will help some of you.  For Database Mail, you have to install IIS (why, Microsoft, why?) but SQL Server Agent does not have to be running (which I find counter-intuitive).
 
Start / All programs / Admin tools / Server manager

In Server Manager, click Features / Add features (on the right)

At the "Add Features Wizard" / "Select Features" dialog, in the list of features, locate and enable SMTP Server
At the "Add Features Wizard" / "Add role services ..." dialog, click "Add Required Role Services"


Back at the "Add Features Wizard" / "Select Features" dialog, click Next
At the "Add Features Wizard" / "Web Server IIS" dialog, click Next
At the "Add Features Wizard" / "Select Role Services" dialog, in the list of role services, click Next
Click "Install," wait for it to finish, and click "Close."

Start / All programs / Admin tools / Internet Information Services (IIS) 6.0 Manager

Expand local computer / right-click smtp virtual server / properties
At the SMTP Virtual Server dialog / Access tab / Authentication
Uncheck anonymous, uncheck basic, enable Integrated windows authentication / OK

At the SMTP Virtual Server dialog / Messages tab
Keep or change the Badmail directory / OK

Expand local computer / smtp virtual server / highlight domains / right-click domain name on the right / properties
Keep or change the mailroot drop directory / OK

Make sure the SMTP service is set to start automatically:
Start menu / Administrative tools / Services

Find and right-click "Simple Mail Transfer Protocol" / Properties
General tab / Startup type: Automatic / OK

Launch SSMS
Click New Query, and execute this query:
--- start of query ---
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
--- end of query ---

Note: if you receive an error like "The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration", change the RECONFIGURE command to RECONFIGURE WITH OVERRIDE.  I do not know how to correct the underlying error yet.

Run SSMS / Management / right-click Database Mail / Configure Database Mail

At the "Select Configuration Task," choose "Set up database mail..." / Next

When a developer runs sp_send_dbmail to email through this server, they will need to specify a profile name as a string.
For the Profile name:
The profile name can have letters, number, spaces, hyphens, etc.
I recommend keeping it short, and including the server name.
Example Profile name: "servername-db email"
At the SMTP accounts area, click Add
At the New Database Mail Account dialog
For the Account name:
This will distinguish identifying one smtp server from another.
Example Account name: "servername-db account"This will distinguish identifying one smtp server from another.
Database Mail will attempt to use each Account in this Profile until it succeeds.
For the Email address, Display name and Reply email:
Since it's not coming from a person, I recommend bounceback information: (do not include quotes)
Email address: "do-not-reply@organization.com"
Display name: "Big Organization"
Reply email: "do-not-reply@organization.com"
Server name: "localhost"
Enable Windows Authentication (since our SMTP virtual server is using Integrated windows authentication)
Click OK

At "Manage Profile Security," click Next
At "Configure System Parameters," click Next
At "Complete the Wizard," click Finish
At "Configuring," after the actions complete, click Close

Back at SSMS / Management / right-click Database mail
Click Send Test Email

Select your Database Mail Profile (perhaps "servername-db email")
To: recipient-email@organization.com
In the Subject field, I recommend appending a 1, 2, 3... in case you need to troubleshoot
At the Database Mail Test Email dialog, click OK.
You should recieve the email soon.

Another way to send email:
--- start of query ---
exec msdb.dbo.sp_send_dbmail
@profile_name='servername-db email' /* type your database profile */
,@recipients='recipient-email@organization.com'
,@subject='Test message 2'
,@body='This is the body of the test message.
Database Mail Sent Successfully.'
--- end of query ---

Slightly more complicated email:
--- start of query ---
declare @strBody varchar(150)
set @strBody='Database Mail works on '+@@servername+'.'
set @strBody=@strBody+ 'The time is '+cast(getdate() as varchar(20))
exec msdb.dbo.sp_send_dbmail
@profile_name='servername-db email' /* type your database profile */
,@recipients='to_recipient@organization.com'
,@copy_recipients='cc_recipient@organization.com'
,@from_address='from_sender@organization.com'
,@body_format='html' /* text or html, default is text */
,@subject='Database Mail 3'
,@body=@strBody
--- end of query ---

To check for problems:
1. SSMS / Management / right-click Database mail / View Database Mail log
2. SSMS / new query, execute these queries:
select * from msdb.dbo.sysmail_mailitems order by send_request_date desc
select * from msdb.dbo.sysmail_log order by log_date desc

1 comment:

Aze said...

Thanks William. Your effort helped me a lot.