System.Data.SqlClient.SqlException após CREATE / ALTER / PRINT

Question

Estou vindo de 2 outras perguntas e estou tentando entender por que essa exceção acontece.

Semente do Entity Framework -> SqlException: a redefinição da conexão resulta em um estado diferente do login inicial. O login falha. resultados-em-um-dif

O que significa "Redefinir a conexão"? System.Data.SqlClient.SqlException (0x80131904)

Este código reproduz a exceção.

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();
}

O stacktrace completo é

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()
...

Se eu mudar o primeiro Run(dbName...para Run("master"...ele funcionará bem. Portanto, está relacionado à execução ALTER DATABASEno contexto do mesmo banco de dados

O que significa "Redefinir a conexão"? Por que a sessão está "no estado de eliminação". ? Devo evitar a execução de instruções "ALTER" dentro do mesmo banco de dados? Por quê?

Answer 1

O erro "Redefinir a conexão resulta em um estado diferente do login inicial. O login falha." é devido a uma conexão em pool sendo reutilizada após a mudança de estado do banco de dados (mudança de agrupamento do banco de dados). Abaixo está o que acontece internamente que leva ao erro.

Quando este código é executado:

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

O ADO.NET procura por uma conexão em pool existente, combinando a string de conexão e o contexto de segurança. Nenhum foi encontrado porque a string de conexão da conexão em pool existente (da CREATE DATABASEconsulta) é diferente ( masterbanco de dados em vez de TESTDB). O ADO.NET então cria uma nova conexão, que inclui o estabelecimento de uma conexão TCP / IP, autenticação e inicialização de sessão do SQL Server. A ALTER DATABASEconsulta é executada nesta nova conexão. A conexão é adicionada ao pool de conexão quando é descartada (sai do usingescopo).

Então isto executa:

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

ADO.NET encontra a TESTDBconexão existente em pool e a usa em vez de instanciar uma nova conexão. Quando o PRINTcomando é enviado ao SQL Server, a solicitação TDS inclui um sinalizador de redefinição de conexão para indicar que é uma conexão em pool reutilizada. Isso faz com que o SQL Server seja chamado internamente sp_reset_connectionpara fazer o trabalho de limpeza, como rollback de transações não confirmadas, descartando tabelas temporárias, logout, login, etc.) conforme detalhado aqui . No entanto, sp_reset_connectionnão é possível reverter a conexão de volta ao agrupamento inicial devido à alteração do agrupamento do banco de dados, resultando na falha de login.

Abaixo estão algumas técnicas para evitar o erro. Sugiro a opção 3.

1. invocar o SqlConnection.ClearAllPools()método estático após alterar o agrupamento

2. Especifique em mastervez de TESTDBpara o ALTER DATABASEcomando para que a conexão em pool 'principal' existente seja reutilizada em vez de criar uma nova conexão. O PRINTcomando subsequente criará uma nova conexão para TESTDBuma vez que não existe uma no pool.

3. especifique o agrupamento na CREATE DATABASEinstrução e remova o ALTER DATABASEcomando inteiramente