HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Can send test database mail but job emails failing

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canfailingbutdatabasemailtestemailssendjob

Problem

I stopped receiving sql agent job failure notifications in the recent past. Upon investigation the error reported is:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-08-16T02:40:14). Exception Message: Could not connect to mail server. (No such host is known).

The mail account setup has not been changed and I verified it looks correct. Futhermore, I can successfully send and receive an email using the Send Test Email... function in SSMS. So why can't the jobs send the emails out?

Since the Send Test Email... function queues the email, it doesn't seem as there would be a permissions issue with sending it this way vs the job sending it. Also, that would indicate it is not a firewall issue. So the only difference is time of day...the jobs run in the middle of the night...but I can't think of any system configuration that would be changing then.

Any other ideas of things to check?

Solution

When you use Send Test Email, SSMS is using your credentials. When SQL Agent attempts to connect to the mail profile, it's using the SQL Agent service account credentials. That would be a major difference, and it wouldn't have anything to do with time of day...just credentials.
You can test it by scheduling a job that has a task that will fail (i.e. Select * from northwind) and let the SQL Agent run it, and find out if it will send the notification. That will at least eliminate the service account permission possibility...

Context

StackExchange Database Administrators Q#183752, answer score: 3

Revisions (0)

No revisions yet.