[Note from Pinal]: In this episode of the Notes from the Field series database expert Ryan Adams explains a very critical error related to Database Mail. Ryan is one guy who spends more time with real world issues with SQL Server than anything else. He has mastered the art of resolving complex errors and document them so easily that one can’t find anywhere else. In this blog post Ryan addresses a very interesting error related to Database Mail Breaks with TLS 1.0 Disabled Discovery. Read the experience of Ryan in his own words.
I ran across an issue where I had just configured database mail on a new server, went to send a test email, and I waited to receive it. I waited…and waited…and waited, but still nothing. I started to investigate and verified that database mail was indeed enabled, Service Broker was running, and the mail queue was started and running. I went to go look at the sysmail_allitems view to see the status of my test emails to find that the sent_status column showed them as unsent. The only errors I could find were these:
Log: SQL Server Agent
Message
[474] Unable to refresh Database Mail profile Server Mail. (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database.
System.Data.SqlClient.SqlException: profile name is not valid at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.Td)
Log: SQL Server Agent
Message
[260] Unable to start mail session.
I spent the better part of a day trying to figure this out. Those error messages were not exactly helpful in pointing to the root cause. Since this was a new server that I did not build myself nor did I install SQL myself I decided to uninstall and reinstall SQL. When I ran the install it failed and upon investigation of the install log file I found an error that pointed me toward the issue.
Error: 17182, Severity: 16, State: 1.
2016-03-23 14:49:21.08 spid12s TDSSNIClient initialization failed with error 0x80090331, status code 0x80. Reason: Unable to initialize SSL support. The client and server cannot communicate, because they do not possess a common algorithm.
At this point I knew I had a protocol issue. The thing to note here is that this is actually blocking the install of SQL Server and I’ll explain why that is in just a minute.
Why
You may recall something called the POODLE attack that revealed a vulnerability in SSL 3.0 and TLS 1.0. This particular server had SSL 3.0, TLS 1.0, and TLS 1.1 disabled in the registry. Also note that TLS 1.2 was NOT disabled. The server was running Windows 2012 R2. These protocols were disabled to prevent the possibility of a POODLE attack. If you are wondering how to disable these protocols on your servers then look at Microsoft Security Advisory 3009008. To disable them for the whole OS scroll down to the Suggested Actions section and look under the heading “Disable SSL 3.0 in Windows For Server Software”.
I also want to note that the PCI Security Standards Council pushed back the date for getting off of SSL and TLS 1.0 to June 30th, 2018. In addition to that, it should also be noted that Microsoft’s Schannel implementation of TLS 1.0 is patched against all known vulnerabilities.
You might be wondering how SQL Server was originally installed if having the protocols disabled prevented my subsequent installation attempt. The reason is that the registry changes to disable them was made after the original install of SQL Server. I’m glad this error happened on my subsequent install or I might not have ever figured this out.
Database Mail Breaks
So far the only issue I have run across by disabling these protocols (post installation) is that Database Mail can’t send emails and they just queue up with only the above vague messages. The one that says it is unable to refresh a profile is an error and the other one is a warning in the SQLAgent log. Most people don’t have alerts raised for this and if you sent those via Database Mail, you’ll never get them anyway. This is where I want everyone to be cautious. If these protocols have been disabled (and you may not be aware of it) then you will not get alerts for things like failed SQLAgent jobs that could be backing up your DBs or Hard IO errors, etc… That means you could have issues on your SQL Server and might not even know about it, because the email notification is just sitting in the queue.
The Cause
The reason this issue exists is because Database Mail does not yet use the .Net Framework 4.6.1. That is the newest version of the Framework and the only one that supports TLS 1.1 and TLS 1.2 for SMTP. The .NET framework version 3.5 SP1 and earlier did not provide support for applications to use TLS 1.2 as a cryptographic protocol. In the update below they enabled the usage of TLS 1.2 in the .NET Framework 3.5 SP1.
The Fix
Microsoft could have fixed this in two different ways. The first is that Database Mail be changed to utilize the .Net Framework 4.6.1 if it is installed.
If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Where is the actual link referenced at the end of the article? Database Mail is failing on my SQL Server 2014 web server ever since I disabled TLS 1.0 and I need to find a solution.
“Check out THIS ARTICLE to obtain the fix.”
what article?
Hi David,
That article was removed so I have modified the blog post to remove the link.
Dave,
Do you know how to fix this? I am running SQL Server 2014 (SP2-GDR) (12.0.5207.0) and this is on a public web server that needs to pass PCI scans, therefore the reason I disabled TLS 1.0. Looking at the DatabaseMail.exe in the \Binn folder I see that it was compiled on 2/21/2014, which is before .Net 4.6.1 was available. Is this addressed in one of the later CU updated for SQL 2014? Or is there another known fix?
Hello,
is there any workaround on the issue above?
because till date, database mail is not working after disabling tls 1.0 + 1.1 Crypto. protocols.
I’m unable to send using TLS 1.2 with DbMail on sql 2017 express on Server 2016. Server is running .Net Framework 4.7.2 (461814) and works fine testing to smtp.gmail.com on port 25 and also allowing less secure apps. Disable less secure apps and switching to port 587 returns authentication error:
Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at) So this is certainly pointing to a TLS issue. I’ve read up on more forums than you can imagine over the last week and no solution found.