Sql DBMail Rejected by Exchange 2007

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?
tfountainAsked:
Who is Participating?
 
bull_riderConnect With a Mentor Commented:
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
 
bull_riderCommented:
Enable Database Mail stored procedures in SQL Server Surface Area configuration. That will make it work for sure.
0
 
tfountainAuthor Commented:
Already enabled.  I have done all of the obvious stuff.
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
bull_riderCommented:
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
 
tfountainAuthor Commented:
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
 
bull_riderCommented:
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
 
tfountainAuthor Commented:
Had already done this....did it again....same issue still exists......
0
 
bull_riderCommented:
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
 
St3veMaxCommented:
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
 
tfountainAuthor Commented:
No I am not creating an alias.  I have set it to a real domain account on the same domain.
0
 
bull_riderCommented:
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
 
St3veMaxCommented:
Looking at the error: Mailbox unavailable

Have you created a mailbox in AD/Exchange for this account?
0
 
bull_riderCommented:
Is this issue resolved for you?
0
 
tfountainAuthor Commented:
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
 
bull_riderCommented:
Could you please close this question? :)

Please let me know if you have any more queries.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.