0

I have searched everywhere (it feels) for a solution and / or guidance. My last resort is to ask. I have SQL Server 2016 running on a Windows Server. Database mail is not working. I have setup the profile and have submitted a test email by right clicking on the Database Mail node in the SSMS left window.

The email is in an 'unsent' status when I query SQL using SELECT * FROM msdb.dbo.sysmail_allitems;

I created a VBscript file to send an email using CDO - using the same email server values (host name, port, email from, user name, password, etc). The CDO script successfully sends the email.

I'm not sure where to go to troubleshoot SQL.

There is no data being written to SELECT * FROM msdb.dbo.sysmail_event_log;

Admittedly I am not very good with the SQL configuration - I just need to get it setup. I'm trying to determine why the status is just staying in 'unsent'. Is there an action I can do to force a send? Is there some message or log somewhere I can look at to see why it isn't move forward?

EDIT: I have put the same credentials into sql reporting services configuration 'email settings' - and I am able to create subscriptions and successfully send emails. And that confuses me because I see that SSRS Subscriptions require access to the SQL agent and creates a scheduled job.

So what is different about my database mail account in SQL then the one I created in SSRS? ( I understand that I am not posting pictures but that those wizards have personal info so I don't know the value in posting screenshots for data values I can't share ).

Any help is appreciated. Thanks

Michael
  • 11
  • 4
  • I would like to add that the 'sent_date' column is null. Which means (as I have read) that SQL has not tried to send the message yet. – Michael Aug 09 '21 at 22:09
  • SQL Server uses an external .NET Framework-based tool, DatabaseMail.exe, to deliver DB mail messages. Does `exec msdb..sysmail_help_status_sp` return `STARTED`? Are there related error messages in `select * from msdb..sysmail_event_log`? Are there related error messages in the server's Event Log viewer? – AlwaysLearning Aug 10 '21 at 05:03
  • @AlwaysLearning. Thank you - found out the windows server had .Net Framework 4 but not 3.5. That was installed and now am getting closer but still not sending email. To answer your questions: Yes - now sysmail_help_status_sp returns STARTED. In the log file, I'm receiving the following error: "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2021-08-11T09:02:36). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )" – Michael Aug 11 '21 at 13:07
  • Sounds like you're on the right track now. There should be more info in the form of SMTP 4xx and 5xx error codes that will help narrow down the final problem. Also look in the Event Viewer's Application log for messages from the `DatabaseMail` source. – AlwaysLearning Aug 11 '21 at 13:26

1 Answers1

1

This issue is resolved. Thank you @AlwaysLearning. I had to update the server by installing .Net Framework 3.5. Then - following your guidance I ultimately found my problem was with the SSL check box and port 465. I was able to send email using port 25 with no SSL, and also to send TLS on port 587. But SSL and port 465 are still causing problems.

For me however, that is not my main concern. That problem has been passed to someone else in the chain to work on. I am able to use Database Mail.

Michael
  • 11
  • 4