We're migrating our system from dedicated hosting to Azure. Part of this process is a data feed system that emails clients and our system admins when there are issues with the data feeds.
We use SQL Database Mail and a simple IIS 6.0 SMTP server set up on the box. Then in our SQL Stored procedures we call the sp_send_dbmail to send our messages.
We've tried to replicate this on our Azure Virtual Machine and the emails do not seem to be going through. The SMTP server was installed and set up, identically to the original dedicated box. The database mail configuration is the same as well. Our profiles and accounts have the same names so that any migrated code referencing them would not have to be changed. I can't even get the "Send Test E-mail..." to work from the Database Mail node in SSMS.
Is there something I am missing in Azure, a port I need to open up or an endpoint to set up? We're runnning a Windows 2016 datacenter VM, with SQL Server 2016.
It turns out the problem is that SQL Server 2016 no longer requires .NET 3.5 to install but some of the underlying components still use it.
See this article: https://joeydantoni.com/2016/07/27/sql-server-2016-database-mail-not-working/
I was unable to use the exact solution in the article but following is the solution that worked for me.
Create the file:
C:\Program Files\Microsoft SQL Server\MSSQL13.REPLINKDEV\MSSQL\Binn\DatabaseMail.exe.config
Put this into the file:
<?xml version="1.0"?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
<supportedRuntime version="v2.0.50727"/>
</startup>
</configuration>
Restart SQL Server
You can tell if it's working if you can open DatabaseMail.exe in that same folder. If not, something is most likely wrong with the configuration file.