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

0

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?

c#
.net
sql-server
sqlconnection
dbup
asked on Stack Overflow Sep 3, 2020 by pitermarx • edited Sep 3, 2020 by pitermarx

2 Answers

0

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

answered on Stack Overflow Sep 3, 2020 by pitermarx
-1

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

answered on Stack Overflow Sep 3, 2020 by pitermarx

User contributions licensed under CC BY-SA 3.0