Solved

Sql DBMail Rejected by Exchange 2007

Posted on 2009-05-11
15
1,332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
In this video we show how to create a Distribution Group in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >>…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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