SQL DBA – Database Mail Setup – SQL Server 2005

Database Mail, a new addition to the SQL Server 2005 database engine, is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

Database Mail provides a robust, high-performance replacement for the most commonly requested features of SQL Mail. Database Mail is designed to operate with SMTP servers, and is tested with Microsoft SMTP servers.

Note: Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the “SQL Server Surface Area Configuration” tool or the “Database Mail Configuration Wizard“.

Database Mail Setup:

Enabling the Database Mail feature: By default the Database mail Stored-Procs are disabled.

For Express Edition, check the required files exists in MSSQL\Binn folder. If not then copy the following 3 files:
1. DatabaseMail90.exe
2. DatabaseMailEngine.dll
3. DatabaseMailProtocols.dll

Then execute the following Stored-Porcs:

exec dbo.sysmail_start_sp
exec dbo.sysmail_stop_sp

A User must be a member of the DatabaseMailUserRole database role in the msdb database.

Setting up Accounts & Profiles for DB Mail:
Create a Database Mail account for holding information about an SMTP account.
An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.

Note: After performing above steps the mails were not getting sent. I was configuring DB mail from my client machine, even with the admin account. I was getting various errors like:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 19 (2010-10-22T07:24:21). 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 IP_ADD:25).

Then I tried to setup the DB mail from the server itself and it worked in one go.

“The mail could not be sent to the recipients because of the mail server failure. ”
That’s why server name is wrong.

If you don’t know your server name, open ‘cmd’.
type>>nslookup
It will show Default Server name and then we can type it in Database Mail Configuration window.
Open Sql>>Management>>Database Mail [Right Click]>>Configure Database Mail>>Next>>Next>>Add

I am having error,
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2012-09-09T09:27:59). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: Downstream server error). )

I followed the exact steps above within my SQL 2008 Express and Smtp service is running in windows 2008 server

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2011-09-15T15:52:30). Exception Message: Could not connect to mail server. (The requested address is not valid in its context 74.125.127.108:25).
)…………….. why?