Unable to get DBMail working on SQL Server 2005

Question

I have SQL Server 2005 SP3 (4053) running on a Windows Server 2003 R2 virtual machine. When trying to send a test dbmail message, either from the SSMS GUI or using sp_send_dbmail, I get the appropriate "mail queued" response, but the mail is not received.

I then get the following error in the dbmail log:

"The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 4 (2011-12-05T14:53:27). 
Exception Message: Cannot send mails to mail server. (Failure sending mail.).)"

Here's the configuration I currently have. (Server names changed to protect the innocent...)

profile_id  name                description                            last_mod_datetime       last_mod_user
----------- ------------------- -------------------------------------- ----------------------- ----------------
3           SQLSERVER1          Default mail profile for server.       2011-12-05 13:48:15.563 COMPANY\maxwed

account_id  name        description                        email_address     display_name  replyto_address    last_mod_datetime       last_mod_user
----------- ----------- ---------------------------------- ----------------- ------------- ------------------ ----------------------- --------------
4           SQLSERVER1  Default mail account for server    dba@company.com   SQLSERVER1                       2011-12-05 15:18:57.800 COMPANY\maxwed

account_id  servertype  servername           port        username credential_id use_default_credentials enable_ssl flags  last_mod_datetime       last_mod_user
----------- ----------- -------------------- ----------- -------- ------------- ----------------------- ---------- ----- ------------------------ --------------
4           SMTP        mail.company.com     25          NULL     NULL          1                       0          0      2011-12-05 15:18:57.800 COMPANY\maxwed

servertype   is_incoming is_outgoing last_mod_datetime       last_mod_user
------------ ----------- ----------- ----------------------- -------------
SMTP         0           1           2005-10-14 01:55:32.850 sa

paramname                         paramvalue        description                                                                    last_mod_datetime       last_mod_user
--------------------------------- ----------------- ------------------------------------------------------------------------------ ----------------------------------------
AccountRetryAttempts              1                 Number of retry attempts for a mail server                                     2005-10-14 01:55:28.193 sa
AccountRetryDelay                 60                Delay between each retry attempt to mail server                                2005-10-14 01:55:28.193 sa
DatabaseMailExeMinimumLifeTime    600               Minimum process lifetime in seconds                                            2005-10-14 01:55:28.193 sa
DefaultAttachmentEncoding         MIME              Default attachment encoding                                                    2005-10-14 01:55:28.177 sa
LoggingLevel                      3                 Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3   2011-12-05 13:37:25.143 COMPANY\maxwed
MaxFileSize                       1000000           Default maximum file size                                                      2005-10-14 01:55:28.193 sa
ProhibitedExtensions              exe,dll,vbs,js    Extensions not allowed in outgoing mails                                       2005-10-14 01:55:28.193 sa

I can ping the servers from each other, name resolution works fine... I've even gone so far as to telnet to the mail server, from the SQL Server, and send mail using SMTP commands.

That works fine. There are no errors in the windows error logs related to this. I talked with the Exchange admin, and he's seeing nothing on his side. He states that the server is configured as an open relay, with no restrictions. He also says there are no error messages on his end, not even a record of the emails reaching the SMTP server.

If I watch dbo.sysmail_log, I see that the mail status is 'retrying' before it changes to 'failed', but I get no other error message, not even a timeout. If I watch the mail queue with sysmail_help_queue_sp, I see the status go from INACTIVE, to RECEIVES_OCCURRING, then back to INACTIVE. Yet all I'm left with is the generic error message. All of the Googling I've done has shown more specific error messages.

So now I'm at a loss. Any and all suggestions would be greatly appreciated.

UPDATE:

Executing sysmail_help_queue_sp, I find out that even if I stop and restart the mail queue, using sysmail_stop_sp and sysmail_start_sp, the queue state remains INACTIVE. I understand that this should be RECEIVES_OCCURRING. My question is what does that mean, and how do I get it into the proper state?

Further update, I opened up Wireshark to see what was going on at the TCP level. Everything looks normal to a point. The SQL Server box opens a connection to the mail server to verify connectivity, the mail server responds, and the connection is closed. To that point, both my "working" box and this failing box are the same, but then the working box opens up a connection again, to actually send the email. The failing box does not. Don't know if that helps, but it's another observation...

Another update: I disabled the following completely: Symantec Antivirus, ISA Firewall and Windows Firewall was already disabled. Still not working. Sorry. :-)

Thanks, -David.

Answer 1

I've seen this error when anti-virus is blocking database mail. With McAfee it is the Prevent Mass Mailing Worms rule. So you can check that databasemail.exe is in the exceptions list

Answer 2

Check with your exchange admin to make sure that the Exchange server is aware of the new SQL server as a "mail relay".

The mail won't get anywhere without being on the relay list of the SMTP virtual server that you are using on the exchange server. I have forgotten to add a SQL server to the list, and took me a while to figure it out.