<< SQL Server 2021 Hosting - HostForLIFE :: SQL's Choose Function | SQL Server 2021 Hosting - HostForLIFE :: Difference Between SQL And NoSQL >>

SQL Server 2021 Hosting - HostForLIFE :: How To Configure SMTP O365 Migration Using TLS 1.2 For SQL Database Mail?

June 8, 2022 10:06 by author

Peter

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