6

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?

Improve this question
2
  • Were there any changes made to the SQL Agent service account? Or, any changes in Exchange (perhaps a relay rule change) that would account for the change in ability to connect and send mail?
    – SQL_Hacker
    Aug 17, 2017 at 17:05
  • How many DB Mail Profiles are defined? Could you be using a functional one when sending a test email and be using a different one for the SQL Agent that no longer works?
    – John Eisbrener
    Aug 17, 2017 at 18:29

3 Answers 3

Reset to default
3

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...

Improve this answer
3

I've faced with the same issue. You can fix that with enabling Mail Profile from Agent Properties.

enter image description here

Improve this answer
2

I can think of two possibilities: the user who runs the SQL Server jobs may not have the right permissions (due to changes on the mail server end, possibly); or, the server itself has been down at night for some reason.

I suggest you create a job that will fail for certain, and run it via a schedule during the day, and at night. If it fails during the day, then the permissions for the SQL Server Agent user may no longer be correct for sending mail. If it succeeds during the day and fails at night, then maybe there's something going on with the mail server itself.

Note the error you reported:

Could not connect to mail server. (No such host is known).

That sounds like the mail server can't be found.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.