SqlConnection.Open raises wrongly "error occurred during the pre-login handshake" due to thread starvation

[dmitriyse]

Describe the bug

SqlConnection.Open raises:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake

When the process experiences thread starvation.

Exception message:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - Success)

Stack trace:
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandli
ng, String accessToken, DbConnectionPool pool, Func`3 accessTokenCallback)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Program.<>c__DisplayClass0_0.<<<Main>$>b__1>d.MoveNext() in /home/dmi/SqlClientTest/ProgramCool.cs:line 23
--- End of stack trace from previous location ---
   at Program.<>c__DisplayClass0_0.<<<Main>$>b__1>d.MoveNext() in /home/dmi/SqlClientTest/ProgramCool.cs:line 25
--- End of stack trace from previous location ---
   at System.Threading.Tasks.Parallel.<>c__53`1.<<ForEachAsync>b__53_0>d.MoveNext()
--- End of stack trace from previous location ---
   at Program.<Main>$(String[] args) in /home/dmi/SqlClientTest/ProgramCool.cs:line 17
   at Program.<Main>(String[] args)

To reproduce

Use Azure SQL Database

using Microsoft.Data.SqlClient;

ThreadPool.SetMinThreads(1, 1);
var connectionString =
    "Data Source={your-server}.windows.net;Initial Catalog={your-catalouge};User ID=usr;Password=pwd;Min Pool Size=3;Connect Timeout=60;Encrypt=Strict;Trust Server Certificate=False;Pooling=False";

// Thread eater 
Func<Task> threadEater = null;
threadEater = async () =>
{
    Thread.Sleep(800);
    Task.Run(threadEater);
    Task.Run(threadEater);
};
Task.Run(threadEater);

await Parallel.ForEachAsync(Enumerable.Range(0, 512), new ParallelOptions
{
    MaxDegreeOfParallelism = 16
}, async (i, _) =>
{
    await using var connection = new SqlConnection(connectionString);
    connection.Open();
    Console.Write(".");
    Thread.Sleep(1000);
});

Expected behavior

It should await long enough until the thread starvation condition disappears, or it should raise a proper exception.

Further technical details

Microsoft.Data.SqlClient version: 5.2.2
.NET target: .Net 8.0
SQL Server version: Azure SQL, Pool
Operating system: Ubuntu 24.04

Additional context
It is hard to reproduce; just thread starvation is not enough. Parameters should be fine-tuned, even Console.Write makes sense.
Tested on 2 CPU / 8 Gb RAM VM; Azure SQL was in the same Region (Australia East).

The problem is happening here and there in PROD, but nobody usually analyses the correlation with thread starvation.

[cheenamalhotra]

This seems related to known design issues with Managed SNI, look at #422 for more detailed discussions. For now, we would recommend tuning up with threadpool size and providing stable conditions to apps running in Unix. In future releases, we hope to see some improvements in this space.