?
Solved

Sql DBMail Rejected by Exchange 2007

Posted on 2009-05-11
15
Medium Priority
?
1,365 Views
Last Modified: 2012-05-06
I have a server with SQL Server 2005, setup with dbmail.  I have a seperate server setup with Exchange 2007.
If within sql server I go to Management and then right click on Database Mail, it sucessfully sends an email to an internal address.  If I send an emali from a SP it gets rejected.
i.e.
DECLARE @mailitem_id int

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mail_Profile',
@recipients = 'user@somain.com',
@body = 'This is a test.',
@subject = 'This is a test',
@body_format = 'TEXT',
@mailitem_id = @mailitem_id OUTPUT;

SELECT @mailitem_id

The rejection message is:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2009-05-11T13:58:21). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Message rejected as spam by Content Filtering.). )

Any ideas?
0
Comment
Question by:tfountain
  • 8
  • 5
  • 2
15 Comments
 
LVL 6

Expert Comment

by:bull_rider
ID: 24357371
Enable Database Mail stored procedures in SQL Server Surface Area configuration. That will make it work for sure.
0
 

Author Comment

by:tfountain
ID: 24357410
Already enabled.  I have done all of the obvious stuff.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24357437
Did you visit the below URL? Its the best online on Database Mail for SQL Server 2005. Let me know if this helped.

http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tfountain
ID: 24357671
Thanks but no go bull rider.
That was the article I used to setup with initially.
I did discover one thing though:
When I use the "Right Click" on Database mail - Send test email, it works.  When I check the sysmail_event_log it shows "NT AUTHORITY\SYSTEM" in the last_mod_user.
When I send an email from code, it fails and I check the sysmail_event_log it shows "sa" in the last_mod_user.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24357795
Do you use SQL Server Agent to send database mail? If not, then try setting it up. And run the SQL Server Agent service under NT AUTHORITY\SYSTEM
To set up SQL Server Agent Mail to use Database Mail

   1.      In Object Explorer, expand a server.
   2.      Right-click SQL Server Agent, and then click Properties.
   3.      Click Alert System.
   4.      Select Enable Mail Profile.
   5.      In the Mail system list, select Database Mail.
   6.      In the Mail profile list, select a mail profile for Database Mail.
   7.      Restart SQL Server Agent.

0
 

Author Comment

by:tfountain
ID: 24357991
Had already done this....did it again....same issue still exists......
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24358048
Does the MSSQLSERVER service run under the context NT AUTHORITY\SYSTEM context as well. Can you set it but be aware that you have to restart the services to bring that into effect.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24358570
Are you substituting @domain.com for your domain? i.e. are you just creating an alias for your example?

If not; most email may require email to come from the same domain i.e. Steve@ExpertsExchange.com as opposed to Steve@SpoofDomain.com

HTH
0
 

Author Comment

by:tfountain
ID: 24358594
No I am not creating an alias.  I have set it to a real domain account on the same domain.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24360654
Do you have relay enabled in your mail server?

If you want to enable relay on the mail server do the following:

IIS > Default SMTP Server > Properties > Access > Authentication

Access Control > Anonymous access - Checked
Relay restrictions > Relay > - Only the list below (add the LAN IP address of the web server)

As you said, when you run from the sp you get sa as the last_mod_user. With that I assume, that the sp is running under sa context, can you run it with NT AUTHORITY\SYSYTEM context.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24361959
Looking at the error: Mailbox unavailable

Have you created a mailbox in AD/Exchange for this account?
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24365810
Is this issue resolved for you?
0
 

Author Comment

by:tfountain
ID: 24370654
Yes I finally got the issue resolved this afternoon.  Strangely enough, it had nothing to do with SQL server.....  Well at least the resolution I came up with did not.
In Exchange 2007, under the receive connectors there is an anti-spam tab.  In the tab there are 2 lists: IP Allow and IP Block.  I had to add the IP address of the SQL server to the IP Allow list.  Once I did that the problem went away.....
I am not sure that this was the best/proper fix but it worked in this case.
I imagine if I were doing it right or with the right accounts from SQL (combination I could not figure out despite all the suggestions), I would not have had to add it to the IP allow list.  I plan to have access to a test lab in the near future and then will do some more testing on it.  Unfortunately I did not have the luxury to spend that time right now.
Either way, Thanks for all your suggestions.  If you paste my resolution into another entry, I would like to award you the points for all the time you spent trying to help me(which may have helped in the final resolution...).
Thanks again bull rider.
0
 
LVL 6

Accepted Solution

by:
bull_rider earned 2000 total points
ID: 24370946
I sent you the link to check if its is being blocked by firewall. :) No issues. So finally this was the resolution:

In Exchange 2007, under the receive connectors there is an anti-spam tab.  In the tab there are 2 lists: IP Allow and IP Block.  I had to add the IP address of the SQL server to the IP Allow list.  Once I did that the problem went away.....
I am not sure that this was the best/proper fix but it worked in this case.
I imagine if I were doing it right or with the right accounts from SQL (combination I could not figure out despite all the suggestions), I would not have had to add it to the IP allow list.  I plan to have access to a test lab in the near future and then will do some more testing on it.

All the Best. :)
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24376321
Could you please close this question? :)

Please let me know if you have any more queries.
0

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question