SQL Server 2005 provides the new database mail feature which allows a dba to configure e-mail on sql server without need of exchange server and account. Before actually configuring database mail dba needs to enable database mail stored procedures.

Enable database mail

In SQL Server 2005 there are two possible ways to enable database mail which is disabled by default : enable it via SQL Server 2005 Surface Area Configuration tool or via t-sql script using sp_configure

Enable database mail using SQL Server 2005 Surface Area Configuration tool

Go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration tools -> SQL Server Surface area configuration

The next window will show up

Click on Surface Area configuration for features .

Find Database mail node of the necessary sql server 2005 instance and mark enable database mail stored procedures checkbox.

Enable database mail using sp_configure

Since SQL Server Surface Area Configuration tool is gone in SQL Server 2008 you need to use t-sql statement to enable database mail. This way is much shorter than previous and can be applied to both SQL Server 2005 and SQL Server 2008. Just run the next script in you SQL Server Management Studio

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure

Configure database mail

The next steps are the same for SQL Server 2005 and SQL Server 2008.

Connect to sql server instance from SSMS (SQL Server Management studio). Right click on Database mail node and choose Configure database mail.

 Database mail configuration wizard will show up

It say that Database mail is a SQL Server component that usesthe Simple Mail Transfer Protocol (SMTP) to send e-mail messages. The wizard helps you perform setup and maintenance tasks for such as:

• Managing Database Mail accounts and profiles
• Managing Database Mail security
• Configuring Database Mail system parameteres 

Which means that before clicking next> button you need to have an active smtp account credentials on a mail server which sql server has physical access to. This may be a corporate mail server or a free mail service account from yahoo or any other, the main requirement is that e-mail service provider provides access to the mail box via pop3/smtp servers.

After you ensured that you have server name, port number, login and password and you can ping this smtp server from you sql server box click Next .

On the next Select Configuration Task page leave Set up Database Mail selected and click next

On the New Profile page type a Profile name and optionally description for the profile

Then click SMTP accounts Add button. New Database Mail Account dialog box will show up

Fill in all the fields with the smtp server params and account credentials. If you want or need to use exchange account specify exchange server name as server name. If the domain account sql server runs under has an account on exchange choose Windows Authentcation using Database engine credentials. If DB mail will send e-mails only to the e-mails inside exchange set anonymous authentication and if not - specify user name and password of a domain account (User name should look like DOMAIN\User) Click OK

Then click Next on the New Profile page. Then you will see Manage Profile Security page.

 Depending on your security rules choose if this profile will be public and default. Click Next.

 On the page Configure system parameters leave all default values for the moment. Click next.

On the last Cpmplete the Wizard page click finish.

Make sure that configuration has been successful

Test Database Mail

The last step is to ensure that our just configured database mail profile works fine. Right click on Database mail in SSMS and choose Send Test E-Maill

In the next window type your work e-mail and press Send Test E-Mail button.

Check your work mail box. If you received the test letter that database mail works fine if not - see view database mail log right clicking on database mail in SSMS. If you see errors like

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2009-03-18T12:26:03). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 94.100.177.1:25).

Check again you smtp account settings i.e. port number, password and try again with sending test e-mail.