This blog discusses troubleshooting steps to investigate the failure of SQL Server Database Mail service on sending email. It also discusses 2 special scenarios with SQL Server Database Mail that commonly cause mail sending failure
Scenario 1: Implicit SSL/TLS mode is not supported for SQL Server DB Mail. If your SMTP server requires implicit TLS, then SQL Server DB Mail will not be able to send email using this SMTP server.
Scenario 2: SQL DB Mail failure caused by SMTP mail server requesting TLS 1.2. In this case, TLS 1.2 needs to be enabled at DB Mail server.
Commonly Used Protocols and Port
========================
protocol |
No encryption |
TLS/SSL |
TLS/SSL |
Plain port |
Explicit port |
Implicit port |
|
FTP |
21 |
21 |
990 |
SMTP |
25 or 587 |
25 or 587 |
465 |
IMAP |
143 |
143 |
993 |
POP3 |
110 |
110 |
995 |
Telnet |
23 |
23 |
992 |
HTTP |
80 |
- |
443 |
General Troubleshooting Steps to Narrow Down RCA
=====================================
/*List all DB Mail event log*/
SELECT * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC
/*List all failed items */
SELECT er.log_id AS [LogID],
er.event_type AS [EventType],
er.log_date AS [LogDate],
er.description AS [Description],
er.process_id AS [ProcessID],
er.mailitem_id AS [MailItemID],
er.account_id AS [AccountID],
er.last_mod_date AS [LastModifiedDate],
er.last_mod_user AS [LastModifiedUser],
fi.send_request_user,
fi.send_request_date,
fi.recipients, fi.subject, fi.body
FROM msdb.dbo.sysmail_event_log er
LEFT JOIN msdb.dbo.sysmail_faileditems fi
ON er.mailitem_id = fi.mailitem_id
ORDER BY [LogDate] DESC
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Example:
Install Path:\Program Files\Microsoft SQL Server\MSSQL1.SQL2019\MSSQL\Binn\DatabaseMail.exe
Steps: Open SSMS, select Management, right-click Database Mail, and select Configure Database Mail -> Manage Database Mail accounts and profiles > Next.
Open CMD as administrator and run below command to telnet your target server IP and port
telnet [domain name or ip] [port]
For example, to verify connection to 192.168.0.10 on port 25, issue the command:
telnet 192.168.0.10 25
Configure TELNET in your server:
Windows 7, 8. 10:
Open Windows Start menu > Type "Control Panel" > Press Enter > “Programs” > "Programs and Features" > Turn Windows features on or off > Select "Telnet Client" > Press “OK"
Windows Server 2012, 2016:
Open “Server Manager” > “Add roles and features” > click “Next” until reaching the “Features” step > tick “Telnet Client” > click “Install” > when the feature installation finishes, click “Close”.
Below is a sample PowerShell Script to send DB mail
$UserCredential = Get-Credential
enter this:
user:test@chimex.onmicrosoft.com
password:yourpassword
Send-MailMessage -to "youremail" -from "test@chimex.onmicrosoft.com" -subject "powershell" -body "powershell" -BodyAsHtml -smtpserver smtp.office365.com -usessl -Credential $UserCredential
Special Scenarios:
===============
If your DB Mail profile has enabled SSL encryption, then STARTTLS would be required in the secured communication between your DB Mail server and SMTP Server.
For SQL Server Database Mail architecture, as our service relies on .Net System.Net.Mail (SmtpClient class) and System.Net.Mail does not support “Implicit SSL/TLS“mode, SQL Server Database Mail does not support it as well. In the past, port 465 can be used to support “Implicit SSL/TLS“mode (SMTP over SSL). Now it is no longer supported.
(more details , kindly refer SmtpClient.EnableSsl Property (System.Net.Mail) | Microsoft Docs )
More Details with STARTTLS and “Implicit SSL/TLS”
---------------------------------------------------------------------
“STARTTLS” is an email protocol command that would turn an insecure network connection into a secured one. If email client server has enabled/request SSL or TLS encryption, then STARTTLS would be required for SQL Server DB Mail service to successfully send email.
“Implicit SSL/TLS” is another mode for secured client to server communication. The major two differences between “STARTTLS” and “Implicit SSL/TLS” are summarized as below (while there are a couple of other differences) :
Multiple DB Mail cases have seen its failure being caused by client DB Mail server not supporting TLS 1.2 while the SMTP mail server is requesting TLS 1.2. Unfortunately, most often the error observed in Database Mail Log Viewer (sysmail_event_log) is very generic as below. From network monitor trace, you may not see any STARTTLS traffic as the connection can be reset by DB mail server after initial handshake.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2022-03-14T16:58:54). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )
If Schannel errors are detected in Windows system event logs around the same time when DB Mail failure happens, you are suggested to use below troubleshooting steps to see if the failure is related to TLS 1.2 requested by SMTP server.
Below are a few examples but there could be others. Any Schannel error occurring at the same time as the database mail failure should be cautiously checked.
{timestamp},Error,{servername},36887,Schannel,A fatal alert was received from the remote endpoint. The TLS protocol defined fatal alert code is 70.
{timestamp},Error,0,36871,Schannel,{servername},A fatal error occurred while creating a TLS client credential. The internal error state is 10013.
[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v2.0.50727]
"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\SOFTWARE\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\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
DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.