tfountain
asked on
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?
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?
bull_rider
Enable Database Mail stored procedures in SQL Server Surface Area configuration. That will make it work for sure.
ASKER
Already enabled. I have done all of the obvious stuff.
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
http://www.databasejournal.com/features/mssql/article.php/3626056/Database-Mail-in-SQL-Server-2005.htm
ASKER
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.
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.
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.
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.
ASKER
Had already done this....did it again....same issue still exists......
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.
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
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
ASKER
No I am not creating an alias. I have set it to a real domain account on the same domain.
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.
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.
Looking at the error: Mailbox unavailable
Have you created a mailbox in AD/Exchange for this account?
Have you created a mailbox in AD/Exchange for this account?
Is this issue resolved for you?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you please close this question? :)
Please let me know if you have any more queries.
Please let me know if you have any more queries.