17

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and execute. I entered a rule for the firewall outbound port 587. I tried another mail account and profile with the same unsent issues. The only entries in the logs (db mail logs) are starting and ending of the service. There are no errors anywhere that I can find. The emails appear to simply enter the send queue and never leave it. The accounts can send and receive email on their own and from a SQL Server 2014 instance on another machine.

I've got a queue of items with sent status "unsent" and checked all the normal places with expected results in all of them, aside from a long queue of unsent mail:

SELECT * FROM msdb..sysmail_event_log order by log_id DESC

SELECT * FROM dbo.sysmail_mailitems

SELECT * FROM dbo.sysmail_sentitems

USE msdb
SELECT sent_status, *
FROM sysmail_allitems

 SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

 EXECUTE msdb.dbo.sysmail_help_status_sp

I have tried turning it off and on again... so did I miss a DMV etc. that could shed light on this situation? Is this a known issue with SQL Server 2016 that I haven't in my searches? Any other possible steps to get this mail sent?

  • Is the profile from that you are sending email is private ? – Kin Shah Jun 23 '16 at 22:03
  • Any error? What about the logs? And the mail server? Does the mail server receive any connection from sql? The question is missing lots of relevant info... – Paolo Jun 23 '16 at 22:26
  • @kin No, it's a public email. Both emails are office 365 and the accounts are sending and receiving fine from SQL 2014. Added a couple more details to end of 1st paragraph describing the situation. – Dave Jun 23 '16 at 23:15
  • I meant your database mail profile. – Kin Shah Jun 23 '16 at 23:28
  • Are you able to ping/telnet SMTP server from your SQL Server 2016 box? If yes, can you try running VB script from mssqltips.com/sqlservertip/1625/… link to rule out any SMTP issue. If you receive test mail then you can be sure that it is a problem with SQL server. – SQLPRODDBA Jun 24 '16 at 3:24
17

On a whim, when double checking permissions, I double clicked on the actual DB mail executable. The result on the SQL Server 2014 machine was an empty command window. On SQL Server 2016 clicking the DB Mail executable presented this message:

enter image description here

I cannot find this requirement anywhere in the SQL Server 2016 documentation, yet it is clearly a requirement. DB Mail is functioning perfectly with no other changes than installing .NET 3.5.

  • I had the same issue. Resolved by applying .net 3.5. – Sir Swears-a-lot Jul 31 '16 at 21:40
  • this fixed my issue too! it was driving me insane but installing NET3.5 fixed my issue! – NULL.Dude Nov 2 '17 at 20:13
  • This fixed the same issue for me on 2016 SP1 CU8 – James Anderson Apr 13 '18 at 13:16
  • where is the DB mail executable located? – JzInqXc9Dg Jun 22 '18 at 19:34
  • @Dave OH JESUS THIS IS AMAZING. Just wondering... do you use LogMeIn on these servers? We've encountered bizarre situations where LogMeIn uninstalls or deletes stuff, and they refuse to admit it. My database mail at one point worked fine on one box and definitely stopped working sometime around the time we moved to LogMeIn – John Zabroski Dec 28 '18 at 1:22
9

According to Microsoft Support, there's a bug in SQL server 2016 Setup that causes the database mail not to work without .net 3.5

There's a work around by creating a DatabaseMail.exe.config file in the same folder where the DatabaseMail.exe exists (Binn folder) write the following to the file and save with utf-8 encoding

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true"> 
<supportedRuntime version="v4.0"/>     
<supportedRuntime version="v2.0.50727"/>
</startup>
</configuration>

Source: FIX: SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed

4

Take alook at this instead. This way you only need to copy a file instead of installing .net 3.5. Need to install CU1 first. Check here for more details.

1

Besides already mentioned causes, it is also important to activate e-mail profile at SQL Server Agent level as indicated here:

  • Right Click SQL Server Agent > select Properties
  • Select Alert System in the left pane

  • Checkmark > Enable mail profile

  • Verify Mail system: Database Mail
  • Verify Mail Profile: SQLAlerts
  • Checkmark > Include body of e-mail in the notification message
  • Click OK.
  • Restart Agent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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