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 1 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
Purpose: To describe how to set up database mail on SQL Server 2005 in three easy steps.

Version Information: This document was written using SQL Server 2005. The instructions are the same for all editions of this version. Microsoft Outlook 2003 is used as the mail client.

Important Notes: To set up database mail, you'll need an email address and account settings. Database Mail DOES NOT work in the current release (SP2) of SQL Server 2005 64-bit editions. However, Microsoft has very helpfully posted a page on their support site showing you how to set up SQL Server Agent Mail.

Anyone who's ever set up SQL Mail on a previous version of SQL Server will remember how tricky it can be. SQL Server 2005 has simplified things with SQL Server Database Mail. Although it's much easier and more reliable, it's still not obvious how to set it up.

In this easy to understand tutorial we'll set up SQL Server Database mail in three simple steps:

1 - Set up the mail account
2 - Set up database mail
3 - Test database mail

Let's get started.

Step 1. Set Up the Mail Account for SQL Server Database Mail

First of all, we will create a new email account. In this example we'll use an account called databasemail@mycompany.com. You can use an existing account to try this out, as no special mail account settings are needed for SQL Server Database Mail. You can even use public mailbox accounts, such as Hotmail or Yahoo! However, keep your personal mail separate from your database mail – it makes life simpler.

Once the email account has been set up on the mail server, it’s best to check that it works by testing it in Outlook. This can save a lot of time and frustration when you set up database mail. If you’re using Outlook 2003, just click on the Tools menu, and then select Email Accounts which opens the following window:


Email Accounts in Outlook
Click on the Add a New Account radio button, then on Next. The window in Figure 2 appears.
Email Accounts Settings in Outlook
Enter the details of the email account you are going to use, including the POP3 and SMTP server names. If you don’t know these, check the details of one of your existing email accounts, or ask your mail administrator or ISP.

Finally, make sure that the Remember Password box has a tick in it.

Click on the Test Account Settings button to open this window:

Test Account Settings
If you hit problems at this stage, double check:
  • Email address
  • Incoming mail server name
  • Outgoing mail server name
  • User name
  • Password
If these are OK and you still have problems, bring up a DOS command box and type ping mail.mycompany.com and ping smtp.mycompany.com (or whatever your own mail administrator advises you to use)

If you can't ping both of your mail servers, particularly your SMTP server, you have network connectivity problems which need to be resolved first before going any further.

Once you've tested the account successfully, complete the setup of the email account, and then click Send/Receive in Outlook.

If everything's working, you should get an email message in your Inbox similar to the one in Figure 4:

Microsoft Office Outlook Test Message
    Step 2. Set up SQL Server Database Mail

OK, your mail account is now working. Let's do something more interesting!

This is the longest step, but it’s very simple.

First, open up SQL Server Management Studio. It doesn't matter if you do this on your own PC or on the server where database mail is going to be set up.

Open the Object Explorer and connect to the database server where you want to install database mail. Open up the Management node, right-click on the Database Mail node and select Configure Database Mail, as shown in Figure 5 below:

Configure database mail in SQL Server Management Studio
After right-clicking on Configure Database Mail, the Mail Configuration Wizard splash screen will appear. Click on Next, and select Set up Database Mail, as shown below:
Configure SQL Server Mail with Database Mail Configuration Wizard
Click on Next to continue. If SQL Server tells you that The Database Mail feature is not available, click on "Yes" to enable it.

The next thing you'll see is the New Profile form:

Database Mail Configuration Wizard - New Profile

Type a Profile Name of your choice, and an optional description. Click on Add and the window in Figure 8 appears:

New Database Mail Account
Next, click on OK. The database mail account will be added to the mail profile.

You’ve probably noticed that several mail accounts can be added to a profile. This is a great idea, because if one of your mail servers crashes, SQL Server can still send database mail. Built in resilience right from the start!

READ PART 2 OF THIS ARTICLE - Click Here to Read Part 2 of this Article >> >>

If you found this article useful, please visit our sponsors who keep us going...

SQL Enterprise Monitor 2009
This tool gives you unlimited server monitoring at a really great price!

Ensure critical services are running, compare enterprise-wide security and configuration settings, find troublesome queries and resource-intense database activities.
Download
Send to a Friend Printer Friendly Version
Part 2 || Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form