SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
How to Set Up Database Mail on SQL Server 2005 - Part 2 of 2
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
Welcome back...

Click on the Next button again, and the screen in Figure 9 will appear:

Database Mail Configuration Wizard
For the sake of this article, we’ll make the profile a public one by ticking in the check box in the Public column. You might decide to make the profile private later, but let's get things working first.

Click on Next again and the following window appears:

 Configure System Parameters
We’ll use the default configuration parameters as they work fine in most cases. If you have intermittent problems, you might want to alter the settings on this page. Alter the Retry Attempts and Retry Delay if you have an unreliable connection or heavily loaded mail server.

Click on Next again and the summary screen shown in Figure 11 will appear.

Database Mail Configuration Wizard Setup Complete
Click on Finish and you should see the Configuration window shown as below:
Database Mail Final Configuration Screen
Click on Close. Congratulations! You’ve set up your first SQL Server Database Mail service. Now you just need to test it...

Step 3. Test SQL Server Database Mail

Testing SQL Server Database Mail is simple. Open SQL Server Management Studio, right click on the Database Mail node, and click on Send Test Email.

The window shown below in Figure 13 will appear:

Sending a Test Email with Database Mail
At this point, one of two things can happen. If Database Mail has been enabled, then the confirmation window in Figure 14 will be seen, confirming that the email was sent successfully:
Database Mail Test Email
However, if Database Mail has not been enabled for the SQL Server, the error message in Figure 15 will appear immediately:
SQL Server blocked access to procedure 'dbo.sp_send_dbmail'
Don’t panic! This happens because Microsoft has tied down many out-of-the-box settings in SQL Server to make it secure. Click on OK, but leave the Send a Test Email window open while you enable the database mail settings – it takes just a few seconds.

To enable database mail, simply click on the following from your Windows Start menu:

Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration

The window in Figure 16 will appear:
SQL Server 2005 Surface Area Configuration
Click on the hyperlink at the bottom, Surface Area Configuration for Features to open the following configuration window:
Configuring individual features to enable database mail
Simple click on the checkbox to Enable Database Mail stored procedures, then click on OK. You can close the Surface Area Configuration screen at this point.

Now switch back to the Send a Test Email window and try again. If everything's working at the SQL Server end, you should see the window in Figure 14.

At this point you should check the mailbox of the email account where you sent the test email (in our case, bob.evans@mycompany.com), and you should find that you’ve received an email similar to the following:

Successful Database Mail
Well done - you've successfully set up database mail on SQL Server!
Want More SQL Server Articles, Tips and Expert Advice?
If you liked this article please join SQL Server Club to receive our newsletter. Membership is free and you'll be first to hear about new articles, performance tuning tips written by practising DBAs, industry news, blogs, interviews and lots more.
Join SQL Server Club Today
Send to a Friend Printer Friendly Version
Part 1 || Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form