Sql-server – Enable TLS 1.2 for SQL Server 2016 database mail

database-mailsql serversql-server-2016tls-1.2

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.

enter image description 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

enter image description here

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

Best Answer

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 ;)

Related Question