System.Data.SqlClient.SqlException after CREATE/ALTER/PRINT


I'm coming from 2 other questions, and am trying to understand why this exception happens.

Entity Framework seed -> SqlException: Resetting the connection results in a different state than the initial login. The login fails. results-in-a-dif

What does "Resetting the connection" mean? System.Data.SqlClient.SqlException (0x80131904)

This code reproduces the exception.

string dbName = "TESTDB";
Run("master", $"CREATE DATABASE [{dbName}]");
Run(dbName, $"ALTER DATABASE [{dbName}] COLLATE Latin1_General_100_CI_AS");
Run(dbName, "PRINT 'HELLO'");

void Run(string catalog, string script)
    var cnxStr = new SqlConnectionStringBuilder
        DataSource = serverAndInstance,
        UserID = user,
        Password = password,
        InitialCatalog = catalog

    using var cn = new SqlConnection(cnxStr.ToString());
    using var cm = cn.CreateCommand();
    cm.CommandText = script;

The full stacktrace is

Unhandled Exception: System.Data.SqlClient.SqlException: Resetting the connection results in a different state than the initial login. The login fails.
Login failed for user 'user'.
Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

If I change the first Run(dbName... to Run("master"... it runs fine. So it's related to running ALTER DATABASE in the context of the same database

What does "Resetting the connection" mean? Why is the session "in the kill state." ? Should I avoid running "ALTER" statements inside the same database? Why?

asked on Stack Overflow Sep 3, 2020 by pitermarx

1 Answer


The error "Resetting the connection results in a different state than the initial login. The login fails." is due to a pooled connection being reused after the database state change (database collation change). Below is what happens internally that leads to the error.

When this code runs:

Run(dbName, $"ALTER DATABASE [{dbName}] COLLATE Latin1_General_100_CI_AS");

ADO.NET looks for an existing pooled connection by matching the connection string and security context. None is found because the connection string of the existing pooled connection (from the CREATE DATABASE query) is different (master database instead of TESTDB). ADO.NET then creates a new connection, which includes establishing a TCP/IP connection, authentication, and SQL Server session initialization. The ALTER DATABASE query is run on this new connection. The connection is added to the connection pool when it's disposed (goes out of the using scope).

Then this runs:

Run(dbName, "PRINT 'HELLO'");

ADO.NET finds the existing pooled TESTDB connection and uses that instead of instantiating a new connection. When the PRINT command is sent to SQL Server, the TDS request includes a reset connection flag to indicate it's a reused pooled connection. This causes SQL Server to internally invoke sp_reset_connection to do cleanup work like rollback uncommitted transactions, dropping temp tables, logout, login, etc.) as detailed here. However, sp_reset_connection cannot revert the connection back to the initial collation due to the database collation change, resulting in the login failure.

Below are some techniques to avoid the error. I suggest option 3.

  1. invoke the static SqlConnection.ClearAllPools() method after changing the collation

  2. Specify master instead of TESTDB for the ALTER DATABASE command so that the existing 'master' pooled connection is reused instead of creating a new connection. The subsequent PRINT command will then create a new connection for TESTDB since one does not exist in the pool.

  3. specify the collation on the CREATE DATABASE statement and remove the ALTER DATABASE command entirely

answered on Stack Overflow Sep 6, 2020 by Dan Guzman • edited Sep 7, 2020 by Dan Guzman

User contributions licensed under CC BY-SA 3.0