Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Database Mail vs sp_send_dbmail issue

Posted on 2014-08-07
10
Medium Priority
?
873 Views
Last Modified: 2014-08-07
I have setup database mail in SQL 2008 and I received the test message in my inbox.

Headers from SSMS test message
Received: from mail.domainX.com (10.22.3.186) by kvs-exch.domainX.com
 (10.22.3.188) with Microsoft SMTP Server (TLS) id 8.1.436.0; Wed, 6 Aug 2014
 15:24:55 -0500
Received: from SQLDB (10.22.3.183) by mail.domainX.com (10.22.3.186) with
 Microsoft SMTP Server id 8.1.436.0; Wed, 6 Aug 2014 15:24:55 -0500
Sensitivity: Normal
Importance: Normal
MIME-Version: 1.0
From: SQLDB Server <sqldb@domainX.com>
To: <userX@domainX.com>
Date: Wed, 6 Aug 2014 15:24:55 -0500
Subject: Database Mail Test
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
Message-ID: <3575c90f-cf53-408d-9691-2808825a50cd@kvs-edge.domainX.com>
Return-Path: sqldb@domainX.com
X-MS-Exchange-Organization-PRD: domainX.com
Received-SPF: None (kvs-edge.domainX.com: sqldb@domainX.com does not
 designate permitted sender hosts)
X-MS-Exchange-Organization-PCL: 2
X-MS-Exchange-Organization-Antispam-Report: DV:3.3.14006.476;SV:3.3.8330.61;SID:SenderIDStatus None;OrigIP:10.22.3.183
X-MS-Exchange-Organization-SCL: 1
X-MS-Exchange-Organization-SenderIdResult: NONE


Emails from trigger are treated as spam.  The code is sending email direct to the exchange server (not the SMTP server above).

EXEC msdb.dbo.sp_send_dbmail
@profile_name='EmailSERVER',
@recipients = 'userx@domainx.com',
@subject = 'New user added',
@body = @msg

Headers from code above
Received: from kvs-exch.domainX.com ([10.22.3.188]) by kvs-exch.domainX.com
 ([10.22.3.188]) with mapi; Wed, 6 Aug 2014 15:26:32 -0500
Content-Type: application/ms-tnef; name="winmail.dat"
Content-Transfer-Encoding: binary
From: <sqldb@domainX.com>
To: User X <userX@domainX.com>
Date: Wed, 6 Aug 2014 15:26:31 -0500
Subject: Comments have been updated
Thread-Topic: Comments have been updated
Thread-Index: Ac+xs84GQuCaHt1ZRP6EUTZnT/sSCA==
Message-ID: <C9CBCDDAE7AC994EBF8E03620829739CCDCA78A9D9@kvs-exch.domainX.com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach:
X-MS-Exchange-Organization-SenderIdResult: None
X-MS-Exchange-Organization-SCL: -1
X-MS-Exchange-Organization-PCL: 2
X-MS-Exchange-Organization-PRD: domainX.com
X-MS-TNEF-Correlator: <C9CBCDDAE7AC994EBF8E03620829739CCDCA78A9D9@kvs-exch.domainX.com>
received-spf: None (kvs-edge.domainX.com: sqldb@domainX.com does not
 designate permitted sender hosts)
MIME-Version: 1.0


Why doesn't sp_send_dbmail send email to the same server as the test message from SSMS?  I only have the 1 profile.
I expect sp_send_dbmail to send email to the server setup in the profile and it isn't do this.
0
Comment
Question by:hbaber
  • 5
  • 5
10 Comments
 
LVL 23

Accepted Solution

by:
nemws1 earned 2000 total points
ID: 40246590
You mean the e-mail server associated with the *account* - not  the profile.  MSSQL Profiles do not specify e-mail servers, but MSSQL Accounts in DB mail *do*.

Double-check your settings here:
USE msdb;
GO
SELECT *
FROM sysmail_server;

Open in new window

0
 
LVL 1

Author Comment

by:hbaber
ID: 40246887
Their is the offending entry!  Why is it using the incorrect server thou when I tell it which server to use in the profile?
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40246909
I find SQL's e-mail configuration to be overly complex.

In SSMS, connect to the database server in question and expand out the "Management" tab (this is the object explorer).  Double click on "Database Mail".  Then click on "Manage Database Mail accounts and profiles" and click "Next".  Then select "View, change, or delete an existing account" and click "Next".

Select any accounts you are using via the dropdown selection beside "Account name".  In each account, you will see an entry for "Server name:" below.  Find and correct the errant entry and click "Finish" at the bottom.

Hopefully that will get your e-mail around your spam filter. :)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:hbaber
ID: 40246936
I am still at a lost why the spam filter blocks the code generated email but not the test email from SSMS.

Thanks for the help.
0
 
LVL 1

Author Comment

by:hbaber
ID: 40247050
Removing the wrong entry has broken email now.
now I get this error....
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-08-07T13:39:17). Exception Message: Could not connect to mail server. (No such host is known). )
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40247060
Did you remove the entry or fix it?  I would have just updated the e-mail server to the correct servername.
0
 
LVL 1

Author Comment

by:hbaber
ID: 40247064
I removed it :(
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40247075
Well, your profile is most likely still pointing to that account.  Try creating a new account with the proper addresses and e-mail server and attach that to the profile you're using in your msdb.dbo.sp_send_dbmail call (you have 'EmailSERVER' above).
0
 
LVL 1

Author Comment

by:hbaber
ID: 40247287
http://sqlwithmanoj.com/2010/09/29/database-mail-setup-sql-server-2005/

The section "Important Tables used in configuring Database mail and check their status" helped me clean up everything and recreated account/profile using wizard, added IP bypass to spam filter and email is working now.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40247299
Awesome news. :)
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

572 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