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

Question

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();
    cn.Open();
    cm.CommandText = script;
    cm.ExecuteNonQuery();
}

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?

I ran your code (with Integrated Security) and the exception happens when trying to print. I would assume printing is not part of your real code, is it? Did you test the user login? — insane_developer, Sep 03 '20 at 15:31
The Net library has a connection pool and you have to dispose the pool, otherwise, you cannot change an object that is already being used. — jdweng, Sep 03 '20 at 15:49
You might try `SqlConnection.ClearAllPools();` after the alter database. — Dan Guzman, Sep 03 '20 at 15:53
@DanGuzman If I clear the pools after the ALTER the code runs without errors. I would still like to understand why. — pitermarx, Sep 03 '20 at 17:58
See : https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling — jdweng, Sep 03 '20 at 18:06
@jdweng connections are pooled by connection string. In this case the connection string is different. The pool shouldn't have tried to reuse the previous connection. If anything, this [should lead to too many connections and fragmentation](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases). Something fishy is going on here — Panagiotis Kanavos, Sep 04 '20 at 06:11
It looks like running `ALTER DATABASE` with a connection string to that database is making the `SqlConnection` go to a "bad" state. As it is pooled, the next use of that connection fails. — pitermarx, Sep 04 '20 at 07:39
When altering a database in SSMS is find I have to close SSMS and reopen before c# code recolonizes changes. So I would assume you would have to do same when c# does same commands. — jdweng, Sep 04 '20 at 09:24
Close/Open SMSS. Have not found another way using SSMS. Assume same with c#. — jdweng, Sep 04 '20 at 10:29
I may be missing something... I'm not using SSMS, only .NET. How can I do a similar operation to closing SSMS in C#? — pitermarx, Sep 04 '20 at 11:17
I am not sure if you can run `PRINT` from ado.net. But, if you just want to print a message, why dont you use Console.WriteLine? — Felipe Oriani, Sep 04 '20 at 19:18
I dont want to print. It's just an example. Any query fails. — pitermarx, Sep 05 '20 at 14:56

Dan Guzman · Accepted Answer · 2020-09-07T11:08:08.097

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.

invoke the static SqlConnection.ClearAllPools() method after changing the collation
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.
specify the collation on the CREATE DATABASE statement and remove the ALTER DATABASE command entirely

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

1 Answers1

Linked