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

Question

I know this is a bit vague, but I'm not being able to pinpoint the issue.

When I run the a bit of code against a local database it runs fine. When I use a remote database I get an error. It occurs midway of the program execution. The DBup upgrade runs, and then a manual query fails with this exception:

System.Data.SqlClient.SqlException (0x80131904): Resetting the connection results in a different state than the initial login. The login fails. Login failed for user 'sa'.

I'm creating SqlConnection manually using new SqlConnection() and I'm also using DbUp

I'm not sure what I can be doing wrong. Nor where to start debugging this. The ConnectionString does not change and I'm always using sa to connect to the database.

A good question to start is What does "Resetting the connection" mean? How am I doing it?

Answer 1

After a couple of hours of trial and error I got to a minimal piece of code that reproduces the error

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

Answer 2

It's not an answer to my question, but I worked around the issue by not disposing the SqlConnection but reusing it.

I still do not understand the problem