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?

pitermarx
  • 908
  • 1
  • 9
  • 22
  • See [Entity Framework seed -> SqlException: Resetting the connection results in a different state than the initial login. The login fails.](https://stackoverflow.com/q/50398421/1260204). Does that solve your problem? – Igor Sep 03 '20 at 10:52
  • 1
    If a database is attached to a server never use the AttachDB (localdb) property in the connection string (remove). The server owns the mdf database file and doesn't allow users to access the file directly. You must connect to the database through the server and instance. To debug, I use SQL Server Management Studio. SSMS login window will show server and instance name which should be used in the c# connection string. The login window also shows Window Credental which in connection string is equivalent to Integrated Security = true and uses the users (or group) windows credential. – jdweng Sep 03 '20 at 10:53
  • @Igor the solution there is to use plain ado.net, which I'm aready doing. – pitermarx Sep 03 '20 at 10:55
  • @jdweng I'm not using the (localdb) it's simply an SQLServer instance running on localhost – pitermarx Sep 03 '20 at 10:56
  • For a connection to work the local machine and remote machine has to have same credentials. Usually I will make the database credentials a Window User Group and then put users into the group. The Group has to be assigned on both local and remote machines. A company usually has Group Policy setup which can be used for the database credentials. – jdweng Sep 03 '20 at 10:56
  • @jdweng i'ts 2 completely diferent servers, with diferent credentials. Completely diferent connectionStrings. It's just that when I use the local server the error does not happen – pitermarx Sep 03 '20 at 10:57
  • Use same serve/instance as shown in the SSMS login window. Window should show Window Credential. Then add to connection string Integrated Security = true and remove the username/password from connection string. – jdweng Sep 03 '20 at 10:58
  • I'm not using integrated security in any of them. It's not even enabled – pitermarx Sep 03 '20 at 10:59
  • Just to clarify, the exception occurs midway of the execution. Some querys execute sucessfully and then one does not – pitermarx Sep 03 '20 at 11:00
  • Integrated security = true just means you are using the user login credentials which in SSMS is shown as Windows Credential. – jdweng Sep 03 '20 at 11:00
  • The same queries should be tested in SSMS which gives better error messages. I suspect some of your tables have different credentials and SSMS will indicate these errors so you can fix. – jdweng Sep 03 '20 at 11:01
  • how can i check if a table has diferent credentials? Why would that happen only remotely? – pitermarx Sep 03 '20 at 11:03
  • Another piece of information is that I'm always using the sa user – pitermarx Sep 03 '20 at 11:12
  • `I'm always using the sa user` this is a *very bad habit* that exposes you to hacking. Never use that account in application code. As for what's wrong - you have to provide information *in the question itself*. SQL Account authentication obviously works otherwise thousands of developers would have noticed 23 years ago. – Panagiotis Kanavos Sep 03 '20 at 12:14
  • @pitermarx post your code, the connection string (without the password), the line that throws and the *full exception string*, not just the message part. You can easily get that with `Exception.ToString()` or by clicking on the `Copy Details` link in the exception popup. The full text includes any inner exceptions and the call stack that led to the exception – Panagiotis Kanavos Sep 03 '20 at 12:16
  • @pitermarx as for the duplicate, it says to *use a different connection*. EF uses ADO.NET underneath, so using the ADO.NET classes alone wouldn't fix the problem. – Panagiotis Kanavos Sep 03 '20 at 12:21
  • I encounter the same issue when using "ALTER LOGIN [sa] WITH DEFAULT_LANGUAGE = us_english" : the command works but the subsequent Sql command raises this same exception. Maybe because an important data has been altered, Login in my case, Database is your case – Elo Jan 20 '23 at 10:48

2 Answers2

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

pitermarx
  • 908
  • 1
  • 9
  • 22
-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

pitermarx
  • 908
  • 1
  • 9
  • 22
  • That's almost as bad as using `sa`. You're now accumulating locks on any tables you use, blocking other connections. You haven't posted any code or even the full exception so it's impossible to understand the problem – Panagiotis Kanavos Sep 03 '20 at 12:17
  • The one thing this shows though is that the code you use to create connections does something strange. – Panagiotis Kanavos Sep 03 '20 at 12:18
  • What do the two connection strings look like? What SQL Server editions and versions do you use? ADO.NET pools connections and reuses them. Each time you close/dispose a connection it's reset and put back in the pool. Connections are pooled *by connection string* and the Windows account if Windows Authentication is used. An error about resetting the connection suggests pooling got mixed up (unlikely, otherwise you'd see a *lot* of similar questions) or that something changed in the remote server. Different databases perhaps? Switch to single-user mode? A user-instance database? – Panagiotis Kanavos Sep 03 '20 at 12:27
  • @PanagiotisKanavos thanks for the comments. I did not post the code because it's a big codebase and I'm not sure what is causing the issue. The code to create the connection is as simple as new SqlConnection(connectionString); and then the Open() – pitermarx Sep 03 '20 at 13:06
  • @pitermax the relevant code is only a couple of lines. Again, if a simple connection string caused such problems a *lot* of people would have noticed. Neither SQL Server nor ADO.NET are broken – Panagiotis Kanavos Sep 03 '20 at 13:08
  • On the other hand, using `sa` is broken any way you look at it. That's why it's disabled by default too - even if you use SQL Accounts it's better to use a new account with sysadmin rights than leaving the well-known `sa` account active – Panagiotis Kanavos Sep 03 '20 at 13:09
  • I'm not saying ADO.NET is broken. I'm trying to understand the problem. I also had the idea that the connections were already polled. Locally it's a SQL2016 express and remotely too. The options you are giving are 1. Pooling got mixed up. 2. Something changed in the remote server. - Diferent databases. I'm doing multi-database queries both remotely and locally - Switch to single-user. Are you suggesting I change to single-user? - User-instance database. I'm not using this feature. Are you suggesting I enable it? – pitermarx Sep 03 '20 at 13:12
  • 1
    I'd suggest reading the possible duplicate again because you seem to be doing exactly the same thing - creating a new database. The duplicate uses EF migrations, you're using DbUp. Which means a connection to the same *server* could end up trying to log into *different* databases from one phase to the next. – Panagiotis Kanavos Sep 03 '20 at 13:12
  • @PanagiotisKanavos thanks for the help. I created a new question, with a code example and more focused questions https://stackoverflow.com/questions/63726424/system-data-sqlclient-sqlexception-after-create-alter-print – pitermarx Sep 03 '20 at 17:59