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?
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
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
User contributions licensed under CC BY-SA 3.0