How To Configure SMTP O365 Migration Using TLS 1.2 For SQL Database Mail

This article will explain to you how exactly you need to configure TLS 1.2 as a default protocol and setup SMTP O365 migration with Database mail using SQL 2012 and above versions for Windows Server 2012 and above

Common SMTP exceptions we usually faced

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

Just follow the steps. The below steps are verified, proven, and compatible with the below servers and SQL versions,

Windows Server 2012 R2 – SQL Server 2014 SP3
Windows Server 2012 R2 – SQL Server 2012
Windows Server 2016 – SQL Server 2016
Windows Server 2016 – SQL Server 2017 and above.

Step 1

Check if your .NET framework is 4.6 or above, especially for SQL 2014/2016 version. Otherwise, install the latest .Net version. To verify the current version, open PowerShell, and run as administrator. Copy and execute the below command

reg query "HKLM\SOFTWARE\Microsoft\Net Framework Setup\NDP\v4" /s

Step 2

Check if .NET 3.5 was installed (You can skip this installation for SQL Server 2017, but the .Net framework installation must be .Net 4.8 or above)

Powershell command to list out the installed.NET versions - Run as admin

gci 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP’ -recurse | gp -name Version -EA 0 | where { $_.PSChildName -match ‘^(?!S)\p{L}’} | select PSChildName, Version

If not installed, either install 3.5 or place a config file in Binn folder

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe.config

Place the XML content

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

Save file as DatabaseMail.exe.config, with UTF-8 and saved in config format

MSSQL13/14/15 – Version depends on the SQL server installed. Forex: MSSQL13 – 2014 and MSSQL14-2016

Step 3

Install the Database mail advanced configuration. Execute the first query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Step 4

Enable the Windows firewall in the Azure portal under networking for VM

Add a rule in Inbound port rules and Outbound port rules with the highest priority by enabling the recommended port 587 with TCP protocol

Step 5

Enable the TLS 1.2 by adding/updating the registry Keys under Regedit. Click on start and type registry editor, run as admin, go to the respective Path and add the key(folder) and (DWord 32-bit) value

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
"DefaultSecureProtocols"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v2.0.50727]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\.NETFramework\\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\\SOFTWARE\\Wow6432Node\\Microsoft\\.NETFramework\\v4.0.30319]
"SystemDefaultTlsVersions"=dword:00000001
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 2.0\\Client]
"DisabledByDefault"=dword:00000001

[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

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp]
"DefaultSecureProtocols"=dword:00000800

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp]
"DefaultSecureProtocols"=dword:00000800

NOTE: Make sure you add only the pre-defined registry keys. Any typo error will stop the registry functions instantly. Copy and paste exactly the same.

Step 6

Configure SQL Database mail with account Name, Email-address [email protected], Server Name, PORT, Enable SSL, and Basic Auth

Step 7

Restart the machine and try the Database mail. If this does not work, add the below registry keys

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 2.0\\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 2.0\\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 3.0\\Client] #apply only if the registry key (SSL 3.0) is present
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\SecurityProviders\\SCHANNEL\\Protocols\\SSL 3.0\\Server] #apply only if the registry key(SSL 3.0) is present
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

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

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

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

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

Step 8

Restart again and Check if the TLS 1.2 is enabled through PowerShell. Copy, paste, and execute

$key = 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\'
if (Test-Path $key) {
  $TLS12 = Get-ItemProperty $key
  if ($TLS12.DisabledByDefault -ne 0 -or $TLS12.Enabled -eq 0) {
    Throw "TLS 1.2 Not Enabled"
  }
  else{
 "TLS is enabled"
  }
}

Step 9

Execute the PowerShell and check if the email has triggered. Change values for $From - (Domain Account), $To - (User Account) , $Email.Credentials - (Domain Account and Password) variables

#Email Information
$From = "[email protected]"
$To = "[email protected]"
$Subject = "Test authenticated O365 smtp email"
$Body = "Test smtp email sent through Office 365's smtp relay."
#SMTP Relay Settings
$SMTP = "smtp.office365.com"
$Port = 587
$Email = New-Object Net.Mail.SmtpClient($SMTP, $Port)
$Email.EnableSsl = $true
$Email.Credentials = New-Object System.Net.NetworkCredential("[email protected]", "Add Password")
$Email.Send($From, $To, $Subject, $Body)

Step 10

Send test email from step-6 Database mail

Step 11

Execute the below SQL query and verify your email has been sent

select top 10 * from msdb.dbo.sysmail_sentitems order by send_request_date desc;
select top 10 * from msdb.dbo.sysmail_event_log order by log_date desc;

Step 12

If you are still struggling with the email, then the issue is probably in the Exchange Center. To resolve that, log in to the O365 and go to the Microsoft 365 Admin Center, select Users and follow the steps

Go to the Active User([email protected]) and select Manage Email Apps under the Mail tab and enable IMAP, POP, and Authenticated SMTP and execute the Powershell or Database mail test email and see the magic :-)

How to configure the SMTP O365 migration using TLS

5 Comments

Join the conversation! Your thoughts help the community grow.

tom leon4y ago
This worked for my single sql server, but not the cluster sql. anyone have any idea?
- Vikas Hooda4y ago
  I never tried on a cluster SQL. But you can give a try with these links below - https://www.databasejournal.com/ms-sql/smtp-email-setup-for-sql-server-cluster-server/ and https://learn.microsoft.com/en-us/answers/questions/501084/outbound-smtp-issues-in-sql-server.html
randy kautsar4y ago
Halloo, how about windows 10 ? is it possible ?
- Vikas Hooda4y ago
  Yes. By default, Windows 10 supports TLS 1.2. You only need to enable it mentioned in the post. Just follow the steps.
Phuong Pham4y ago
Thanks! It worked like a charm.