Enable TLS 1.2 for SQL Server 2016 database mail

Question

I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution.

So here is my problem:

As per our company policy, we want database mail to be able to send emails over port 25 with TLS 1.2 enabled and with TLS 1.0 & TLS 1.1 disabled.

Our mail server is Exchange Server 2010, our SQL Server 2016 (Developer and Enterprise editions) boxes have OS of Windows Server 2016 Standard editions.

Our SQL Server version is:

select @@version
----------------------------------------
Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) 
    Dec 22 2017 11:25:00 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

We have the DB mail configuration as shown here.

The issue is whenever we turn on SSL

use msdb
exec dbo.sysmail_update_account_sp @account_id=2, @enable_ssl = 1;

We CANNOT send db mail (no matter whether our SMTP authentication is Windows Authentication, Basic authentication or Anonymous Authentication). The error message in db mail log is as follows:

Message

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2018-07-30T10:52:41). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

But if we turn off this SSL, there is no problem for db mail sent out.

So how can we enable SSL and uses TLS 1.2 for db mail?

I have enabled TLS 1.2 by adding registry as shown below

The details is from this link (see the FAQ section)

score 37 · Answer 1 · edited Aug 24 '20 at 14:37

TLS1.2 is the only version of TLS considered secure now (March 2019). It took considerable time and effort to discover that there are 2 essential, additional settings which are required to get this working which are not well known nor well documented, by Microsoft or on the web generally. The following could save you a great deal of time and effort.

These are the 2 new Registry settings that fixed the problem for us:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

This is a reference to the thread where we eventually found this information, buried halfway down the thread: TLS 1.2 in .NET Framework 4.0

Below is the content for a simple executable registry file that I put together that will make the 2 new settings and the settings already shown on the thread above (i.e. this makes all of the necessary Registry settings*):

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

Note 1: SQL needs to be restarted for these settings to take affect but it is better to restart Windows since the new settings will affect .NET 4.x generally.

Note 2: In SQL, the SSL-checkbox must be ticked in the mail profile to use TLS1.2.

*Note 3: FYI We ran the free tool, Crypto V2, with the "Best Practices" option enabled before starting on getting this working. We verified our changes afterwards using the new Crypto version 3.

Hopefully this will save considerable time, effort and frustration for others ;)

Just applied this to my machine. After not being able to send anything, I've sent about 150 messages now on the first try. Monitoring the situation, but this looks very promising. — jbz, Oct 28 '21 at 16:36
I can't thank enough for this solution. We were stuck on an issue for days. The error message in sysmail_faileditems was: Sending Mail using Account (Number) (Date). Exception Message: Cannot send mails to mail server. (Failure sending mail.).. It appeared to be random; sometimes the email has been sent and sometimes it has not. After applying this solution we did not experience any issue. — rasso, Jan 28 '22 at 06:55
Nice one! Unfortunately it was three hours of head scratching before I landed here. Could have been worse — Dave Pile, Jan 31 '22 at 01:31
I wanted to take this a step further: Our SQL database mail was failing as of today due to Microsoft finally turning off TLS <1.2 on Exchange Online 1/31/22.
Error was: "The mail could not be sent to the recipients because of the mail server failure. Exception Message: Cannot send mails to mail server. (Failure sending mail.)"

Adding this registry information and then right clicking SQL Agent in SSMS and restarting was enough to get Database Mail to work again (vs having to immediately reboot or restart all SQL services). Hope this helps someone else... — tb1, Feb 01 '22 at 19:37

Alberto Martinez · Answer 2 · 2018-10-29T23:34:16.407

It's odd because the linked article clearly states:

Support for TLS v1.2 included in the .NET Framework version 3.5 SP1 on Windows 8.1 and Windows Server 2012 R2

which contradicts the response you get from MS support. Edit: I found in this StackOverflow question that although .NET 3.5 didn't include support for TLS 1.2 at first it was added later by MS:

Support for TLS System Default Versions included in the .NET Framework 3.5.1 on Windows 7 SP1 and Server 2008 R2 SP1

Reading the FAQ section I think that the problem is that you missed one registry key when enabling TLS 1.2. The FAQ section says:

The correct registry settings are as follows:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2] 
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
  "DisabledByDefault"=dword:00000000
  "Enabled"=dword:00000001 
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
  "DisabledByDefault"=dword:00000000
  "Enabled"=dword:00000001

These settings are required for both server and client computers.

But in the screen shot that you provided I only see "Enabled"=dword:00000001, the "DisabledByDefault"=dword:00000000 key is missing.

Also it says:

Is TLS 1.1 supported on SQL Server 2016?

Yes. SQL Server 2016 and SQL Server 2017 on Windows versions ship with TLS 1.0 to TLS 1.2 support. You have to disable TLS 1.0 and 1.1 if you want to use only TLS 1.2 for client-server communication.

which can be interpreted as that you need to disable TLS 1.0 and 1.1 in order to use 1.2, but I'm not sure about this one.

You have raised good questions, yet I cannot answer directly as I do not have the environment now. But what I know is the MS support person had set up multiple test environments and still could not get it and he had to raise the question to the Dev team to get the answer for me. — jyao, Oct 29 '18 at 01:34

score 5 · Accepted Answer · answered Aug 09 '18 at 16:04

5

Since it seems nobody can answer this question, I opened a support case with Microsoft, and still it took almost 1 week for MS support to come back with an answer as he went through various internal resources to get the definite answer.

The summary is:

SQL Server Database mail uses System.Net.Mail to do the work, the System.Net.Mail is able to send mail using TLS 1.2 but only when the build runtime version is 4.6 or above. SQL Server 2016 db mail is built for .Net 3.5, hence SQL Server 2016 db mail does not support TLS 1.2 as of now.

answered Aug 09 '18 at 16:04

jyao

3,043
1
11
26

Hi is there any update on the case above, I am having the exact issue , it's really strange when it's related to a version 2 years old than the recent one does not support the secured protocol ! any reply or help would be highly appreciated B regards – Mohammad Omar Oct 28 '18 at 13:18
No, no update, but let's hope SQL Server 2019 will fix this issue. – jyao Oct 28 '18 at 17:18
Even with .NET Framework 4.8 installed (on Windows Server 2016 with SQL Server 2017 - latest/last CU), I STILL had to add the HKLM:\Software\Microsoft\.NETFramework\v4.0.30319 registry key to get things working correctly - i.e., simply patching OS, SQL Server, and .NET is NOT enough - you HAVE to tell the registry to have System.Net.Mail use strong crypto. – Michael K Campbell Sep 11 '23 at 23:09

score 4 · Answer 4 · edited Aug 31 '23 at 06:51

We had a similar issue few weeks ago with SQLServer database mail. We use office365 SMTP server in Azure.

Configuration was "Windows 2012R2 + SQLServer 2012" and "Windows 2012R2 + SQLServer 2014".

Error on database mail was:

The message could not be sent to the recipients due to an email server error".

The error was not systematic. We suppose that when we reach an azure end point allowing TLS1.0 the email is well sent, but when we reach an end point using TLS1.2 our email is rejected.

If we analyze email header sent by database mail we can see that SMTP server use TLS1.2 but database mail sent email with TLS1.0 protocol.

Microsoft SMTP Server (version=TLS1_2,...)
Received: from ... with Microsoft SMTP Server (version=TLS1_0, ...

We fix the issue with adding following registry key on each server (Powershell Script)

#TLS1.2 configure strong encryption
#The strong cryptography uses more secure network protocols (TLS 1.2, TLS 1.1, and TLS 1.0) and blocks protocols that are not secure.
#64 bits applications
New-ItemProperty -Path "HKLM:\Software\Microsoft\.NETFramework\v2.0.50727" -Name "SchUseStrongCrypto" -Value "1"  -PropertyType "dword";
New-ItemProperty -Path "HKLM:\Software\Microsoft\.NETFramework\v4.0.30319" -Name "SchUseStrongCrypto" -Value "1"  -PropertyType "dword";
#32 bits applications
New-ItemProperty -Path "HKLM:\Software\WOW6432Node\Microsoft.NETFramework\v2.0.50727" -Name "SchUseStrongCrypto" -Value "1"  -PropertyType "dword";
New-ItemProperty -Path "HKLM:\Software\WOW6432Node\Microsoft.NETFramework\v4.0.30319" -Name "SchUseStrongCrypto" -Value "1"  -PropertyType "dword";
#SystemDefaultTlsVersions
#A value of 1 causes your app to allow the operating system to choose the protocol
New-ItemProperty -Path "HKLM:\Software\Microsoft.NETFramework\v2.0.50727" -Name "SystemDefaultTlsVersions" -Value "1"  -PropertyType "dword";
New-ItemProperty -Path "HKLM:\Software\Microsoft.NETFramework\v4.0.30319" -Name "SystemDefaultTlsVersions" -Value "1"  -PropertyType "dword";
#32 bits applications
New-ItemProperty -Path "HKLM:\Software\WOW6432Node\Microsoft.NETFramework\v2.0.50727" -Name "SystemDefaultTlsVersions" -Value "1"  -PropertyType "dword";
New-ItemProperty -Path "HKLM:\Software\WOW6432Node\Microsoft.NETFramework\v4.0.30319" -Name "SystemDefaultTlsVersions" -Value "1"  -PropertyType "dword";

Microsoft documented these Keys in their TLS 1.2 Article (that was published 2 days after your answer): https://learn.microsoft.com/en-us/mem/configmgr/core/plan-design/security/enable-tls-1-2-server Adding this for any of you leery about trusting RegEdit's from an unknown User's suggestion. — MikeTeeVee, May 03 '22 at 21:16
Tried for a long time to get SSL email to work with SQL Server, but not with the same framework 4.x with System.Net.Mail (which works to send email from same machine). Is there another secret trick to get SQL Server to send email via port 465 SSL TLS1.2? Currently, I had to write a custom console app and run a trigger to call it to be able to securely send email. Apparently, Implicit SSL is STILL not supported (in 2022)? I use System.Net.Mail and DNFW 4.7.2 on the same server! (and it works! - so it's most certainly a MS-SQL issue/bug) — MC9000, Sep 05 '22 at 12:54
For SQL 2017 (x64) on Windows Server 2016 we needed only New-ItemProperty -Path "HKLM:\Software\Microsoft\.NETFramework\v4.0.30319" -Name "SchUseStrongCrypto" -Value "1" -PropertyType "dword"; -- this came up because AWS was still supporting TLS 1.1 / 1.0 but has now begun turning it off. — Mark Sowul, Aug 30 '23 at 15:38

Enable TLS 1.2 for SQL Server 2016 database mail

4 Answers4

Linked