A Connection Was Successfully Established, But Then an Error Occurred During Login

This one gets misread constantly, and the misreading costs real time. It looks like a login problem, people go and check the password, and the password was never the issue. The clue is in the first half of the sentence.

Or the pre-login variant:

The client was unable to establish a connection because of an error during initialization before
login. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the
remote host.)

This error is widely misread as a login problem. It is not. The first sentence is the clue: the TCP connection succeeded. Something then broke during the handshake that happens before credentials are ever checked.

Which means checking the password is a waste of time, and so is looking for error 18456 in the log, there will not be one, because authentication never started.

The Handshake, Briefly

Understanding the order makes the causes obvious:

1. TCP connection opens, this succeeded 2. Pre-login: client and server agree encryption and protocol version, this is where it fails 3. TLS negotiation and certificate exchange 4. Login: credentials checked, never reached

So the causes are all protocol, encryption or network. Not permissions.

Cause 1: TLS Version Mismatch (the most common today)

Windows Server updates have progressively disabled TLS 1.0 and 1.1. Older SQL Server builds do not support TLS 1.2 without a patch. When both sides run out of shared protocols, the connection is closed at exactly this point.

Check the build first:

SELECT SERVERPROPERTY('ProductVersion')   AS version,
       SERVERPROPERTY('ProductLevel')     AS service_pack,
       SERVERPROPERTY('ProductUpdateLevel') AS cu,
       SERVERPROPERTY('Edition')          AS edition;

TLS 1.2 support needs, at minimum, SQL Server 2014 SP1 CU5 / 2012 SP2 CU8 / 2008 R2 SP3 with the relevant update, and the matching SNAC or ODBC driver on the client, which is the half people forget. An up-to-date server still fails if the application uses an ancient client library.

The enabled protocols live in the registry under:

HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols

Check both machines. The mismatch is by definition between them.

Cause 2: The Certificate

If the server has Force Encryption on, or the client demands encryption, a certificate problem surfaces here rather than as a certificate error:

EXEC xp_readerrorlog 0, 1, N'certificate', NULL, NULL, NULL, N'DESC';

An expired certificate, a private key the service account cannot read, or a self-signed certificate a strict client will not accept all produce this. The certificate-chain-not-trusted error is the polite version of the same family; this is what you get when the negotiation fails harder.

Cause 3: Genuine Network Interference

“An existing connection was forcibly closed by the remote host” is a TCP reset, and something has to send it:

• Firewall or IPS inspecting TCP 1433 and dropping what it does not understand
• A load balancer with an idle timeout shorter than the connection’s life
• VPN or NAT in the path
• NIC offloading or driver bugs, especially on virtualised hosts

The tell is that it works from some clients and not others, or works locally on the server and fails across the network.

Cause 4: The Server Is Refusing New Connections

Occasionally the instance itself is the cause, out of worker threads or memory, so it accepts the socket and then cannot service the login. Check whether the error log shows the instance under stress at the same timestamps:

EXEC xp_readerrorlog 0, 1, N'login', NULL, NULL, NULL, N'DESC';

Nothing at all around the failure time supports the network or TLS explanation, since the instance never saw a login attempt.

Working Out Which One You Have

-- What are current connections actually negotiating?
SELECT  c.session_id,
        c.encrypt_option,
        c.protocol_type,
        c.net_transport,
        c.client_net_address,
        s.program_name
FROM    sys.dm_exec_connections c
JOIN    sys.dm_exec_sessions   s ON s.session_id = c.session_id
WHERE   c.session_id > 50;

Then narrow it down by elimination:

• Connect locally on the server, if that works, the instance is fine and the problem is in the path or the client
• Try a different client machine, works elsewhere means a client-side TLS or driver issue
• Try with encryption explicitly off (where policy allows), if that works, it is TLS or the certificate
• Try sqlcmd -S server -C, trusting the certificate isolates certificate trust from protocol mismatch

Stopping It Recurring

• Patch SQL Server and client drivers together. The commonest version of this incident is a Windows update disabling old TLS on a server whose SQL build never supported the new one.
• Standardise on ODBC Driver 18 or later and set encryption behaviour deliberately rather than relying on defaults, which have changed and will change again.
• Diary certificate expiry. An expired certificate takes every application down simultaneously and this is the error they will all report.
• Know your idle timeouts if a load balancer sits in front of the instance.

Related Scripts

• Certificate Chain Not Trusted, the politer version of the same family
• Check Connection Encryption and Protocol
• Enabling TCP Connections
• Login Failed (Error 18456), for when the login actually is reached
• Forcing Encrypted Connections in SQL Server Using Certificates