An existing connection was forcibly closed by the remote host (OS error 10054)
Article
Before you start troubleshooting, we recommend that you check the
prerequisites
and go through the checklist.
This article details various scenarios and provides resolutions for the following errors:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Secure Channel, also known as
Schannel
, is a
Security Support Provider
(SSP). It contains a set of security protocols that provide identity authentication and secure private communication through encryption. One function of Schannel SSP is to implement different versions of the
Transport Layer Security (TLS) protocol
. This protocol is an industry standard that's designed to protect the privacy of information communicated over the Internet.
The TLS Handshake Protocol is responsible for the key exchange necessary to establish or resume secure sessions between two applications communicating over TCP. During the pre-login phase of the connection process, SQL Server and client applications use the TLS protocol to establish a secure channel for transmitting credentials.
The following scenarios detail errors that occur when the handshake can't be completed:
Scenario 1: No matching TLS protocols exist between the client and the server
Secure Socket Layer (SSL) and versions of TLS earlier than TLS 1.2 have several known vulnerabilities. You're encouraged to upgrade to TLS 1.2 and disable earlier versions wherever possible. Accordingly, system administrators could push out updates through group policy or other mechanisms to disable these insecure TLS versions on various computers within your environment.
Connectivity errors occur when your application uses an earlier version of Open Database Connectivity (ODBC) driver, OLE DB provider, .NET framework components, or a SQL Server version that doesn't support TLS 1.2. The issue occurs because the server and the client can't find a matching protocol (such as TLS 1.0 or TLS 1.1). A matching protocol is needed to complete the TLS handshake required to proceed with the connection.
Resolution
To resolve this issue, use one of the following methods:
Ask your system administrators to temporarily enable TLS 1.0 or TLS 1.1 on both the client and the server computers by performing one of the following actions:
Use the
Ciphers Suites
tab in the
IIS Crypto
tool to validate and make changes to the current TLS settings.
Scenario 2: Matching TLS protocols on the client and the server, but no matching TLS cipher suites
This scenario occurs when you or your administrator restricted certain algorithms on the client or the server for extra security.
The client and server TLS versions,
cipher suites
can be easily examined in the
Client Hello
and
Server Hello
packets in a network trace. The
Client Hello
packet advertises all the client cipher suites, while the
Server Hello
packet specifies one of them. If there are no matching suites, the server closes the connection instead of responding to the
Server Hello
packet.
Resolution
To check the issue, follow these steps:
If a network trace isn't available, check the functions value under this registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Cryptography\Configuration\Local\SSL\00010002
Use the following PowerShell command to find the TLS functions.
Use the Ciphers Suites tab in the IIS Crypto tool to check whether there are any matching algorithms. If no matching algorithms are found, contact Microsoft Support.
This issue occurs when the client or server is hosted on Windows 2012, 2016, and higher versions. Despite both OS versions possessing the same cipher (TLS_DHE*), Windows 2012 and 2016+ handle cryptography keys within the TLS differently. This can result in communication errors.
Scenario 4: SQL Server uses a certificate signed by a weak-hash algorithm, such as MD5, SHA224, or SHA512
SQL Server always encrypts network packets that are related to sign in. For this purpose, it uses a manually provisioned certificate or a self-signed certificate. If SQL Server finds a certificate that supports the server authentication function in the certificate store, it uses the certificate. SQL Server uses this certificate even if it hasn't been manually provisioned. If these certificates use a weak-hash algorithm (thumbprint algorithm) such as MD5, SHA224, or SHA512, they will not work with TLS 1.2 and cause the previously mentioned error.
Self-signed certificates aren't affected by this issue.
Select the Certificate tab and follow the relevant step:
If a certificate is displayed, select View to examine the Thumbprint algorithm to confirm whether it's using a weak-hash algorithm. Then, select Clear and go to step 4.
If a certificate isn't displayed, review the SQL Server error log for an entry that resembles the following and note down the hash or thumbprint value: 2017-05-30 14:59:30.89 spid15s The certificate [Cert Hash(sha1) "B3029394BB92AA8EDA0B8E37BAD09345B4992E3D"] was successfully loaded for encryption
Use the following steps to remove server authentication:
Select Start > Run, and type MMC. (MMC also known as the Microsoft Management Console.)
In MMC, open the certificates and select Computer Account in the Certificates snap-in screen.
Expand Personal > Certificates.
Locate the certificate that SQL Server is using by its name or by examining the Thumbprint value of different certificates in the certificate store and open its Properties pane.
On the General tab, select Enable only the following purposes and deselect Server Authentication.
Restart the SQL Server service.
Scenario 5: The client and the server are using TLS_DHE cipher suite for TLS handshake, but one of the systems doesn't have leading zero fixes for the TLS_DHE installed
Scenario 6: TCP Three-Way Handshake Timeout (SYN Fail, TCP Rejection) due to shortage of IOCP workers
In systems with high workloads on SQL Server 2017 and earlier, you might observe intermittent 10054 error caused by TCP three-way handshake failures, leading to TCP rejections. The root cause of this issue might be in the delay in processing TCPAcceptEx requests. This delay can be due to a shortage of IOCP (Input/Output Completion Port) listener workers responsible for managing the acceptance of incoming connections. The insufficient number of IOCP workers and busy servicing other requests leads to delayed processing of connection requests, ultimately resulting in handshake failures and TCP rejections. You may also observe login timeouts during the start SSL handshake (if any) or the processing of login requests, which involve in authentication checks.
Resolution
A shortage of IOCP workers and SOS Worker resources allocated to handling authentication and encryption operations is the main cause of the TCP three-way handshake timeouts and additional login timeouts. SQL Server 2019 includes several performance improvements in this area. One notable enhancement is the implementation of a dedicated login dispatcher pool. This optimizes the allocation of resources for login-related tasks, which reduces the occurrence of timeouts and improves overall system performance.
Other scenarios where TLS connections fail
If the error message you encounter doesn't correspond to any of the previous scenarios, refer to the following additional scenarios:
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.
