SqlConnection doesn't restore database in the new session if connection is lost

[llali]

Describe the bug

this issue doesn't happen all the time but if one connection (on master db) is used to run multiple queries and one of the queries is "Use [db name]", sometimes the query after this is executed in the master database

To reproduce

1- Open SqlConnecion passing master db connection string
2- Create a command from the connection and set the command text from the list below, one by one and run command.ExecuteNonQuery()
CREATE DATABASE [$(DatabaseName)]

USE [$(DatabaseName)]

CREATE Table tb1 (c1 int)

3. I'm not sure what is causing the connection session to be lost, but what happens is, sometimes we see the application role created in the master database.

Expected behavior

queries after "Use [db name]" should always executed in the db not master even if the connection is on the master database

Further technical details

Microsoft.Data.SqlClient version: (6.1.3)
.NET target: .NET Framework 4.7.2 and .NET 8.0
SQL Server version: (e.g. SQL Server 2022)
Operating system: Windows Server 2022

Additional context
Add any other context about the problem here.

[rhuijben]

Does the same thing happen if you use SqlConnection.ChangeDatabase("...") or the async variant?

I would guess this does a bit better bookkeeping than the db side version of the command.

[paulmedynski]

Investigation Summary

We reproduced this issue with unit tests using the simulated TDS server (no live SQL Server required). The root cause is that session recovery does not preserve the current database context when a connection is transparently reconnected.

What happens

1. A SqlConnection is opened with ConnectRetryCount >= 1 (the default), which negotiates TDS session recovery (FEATUREEXT_SRECOVERY).
2. The application switches the database via USE [db] or SqlConnection.ChangeDatabase().
3. The physical TCP connection is lost (server restart, network blip, etc.).
4. On the next command, ValidateAndReconnect detects the broken link, snapshots SessionData, and performs a reconnection login with session recovery.
5. After reconnection, connection.Database silently reverts to InitialCatalog instead of preserving the switched database.

The user observes their queries running against the wrong database with no error or indication that the context changed.

Reproducing tests

Three failing unit tests demonstrate the problem. All use a custom DatabaseContextQueryEngine (handles USE [db] → returns EnvChange(Database) + Info(5701) + Done) and a DisconnectableTdsServer that can sever all TCP connections while keeping the listener up for reconnection.

Test	What it does	Why it fails
UseDatabase_ConnectionDropped_DatabaseContextPreservedAfterReconnect	Opens connection → USE [switcheddb] → server disconnects all clients → SELECT 1 (triggers reconnect) → asserts connection.Database != "initialdb"	After reconnection, connection.Database is "initialdb" instead of "switcheddb"
ChangeDatabase_ConnectionDropped_DatabaseContextPreservedAfterReconnect	Same flow but uses connection.ChangeDatabase("switcheddb") instead of USE	Same failure — database reverts to InitialCatalog after reconnection
UseDatabase_ConnectionDropped_Pooled_DatabaseContextPreservedAfterReconnect	Same as the first test but with Pooling=true	Same failure — pooled reconnection also loses the database context

A fourth test (UseDatabase_ConnectionDropped_NoRetry_ThrowsOnNextCommand) confirms that with ConnectRetryCount=0, no silent reconnection occurs and the broken connection correctly throws SqlException — this is the expected behavior when session recovery is disabled.

Key assertion

The critical invariant is: connection.Database must never silently revert to InitialCatalog. Either reconnection preserves the switched database, or it throws an error. Silent data-goes-to-wrong-database is the worst outcome.

[paulmedynski]

Server-Side Analysis Update

We examined the SQL Server engine's session recovery implementation (TDS feature extension layer in featureext.cpp and login flow in login.cpp) to confirm the root cause and validate the fix.

Server behavior confirmed correct

The server's FDetermineSessionDb function uses a 3-source priority algorithm for determining the session database during login:

Priority	Source	Description
Source #0 (highest)	Recovery feature extension	Database from the ToBe session recovery chunk
Source #1	Login record	Initial catalog from the connection string
Source #2	syslogins row	User's default database

For recovered connections, Source #0 is mandatory — if the database USE fails (e.g. database dropped/offline), the connection fails entirely rather than silently falling back to a different database. The server's ParseSessionDataChunk correctly parses the recovery database from the client's TDS feature request and applies it via UseByName.

Root cause is client-side

The server correctly restores the database context when the client sends the right recovery data. The bug is in CompleteLogin() on the client side: after session recovery, it unconditionally trusted the server's ENV_CHANGE response without verifying it matched the recovery target database. If the ENV_CHANGE was wrong or missing, CurrentDatabase silently diverged from the user's expectation.

Fix validation

PR #4130 adds a post-recovery verification in CompleteLogin(): after session recovery, if CurrentDatabase (set by the server's ENV_CHANGE) differs from _recoverySessionData._database, a corrective USE [database] is sent to force alignment. This handles all identified failure paths:

• Server properly restores DB → no USE sent (zero overhead)
• Server sends wrong DB in ENV_CHANGE → USE corrects it
• Server omits DB ENV_CHANGE entirely → USE corrects it
• Double reconnection → _initialDatabase is updated each login, so the delta is correct

Test coverage

• 10 unit tests with simulated TDS server covering all 3 server behaviors (correct, wrong DB, omitted ENV_CHANGE)
• 9 manual tests against a real SQL Server covering USE/ChangeDatabase, pooling, MARS, stress loops (100 iterations), double kill, and DDL verification (CREATE TABLE lands in correct database)
• Gated behind Switch.Microsoft.Data.SqlClient.VerifyRecoveredDatabaseContext AppContext switch

[paulmedynski]

After more thorough testing, the initial reproduction turned out to be a false positive due to our testing infrastructure. Deeper analysis and attempts to root-cause have come up empty. I did find one defensive check we will add to the driver to handle a misbehaving server or otherwise missing/incorrect database context information during a session restore. I have yet to conclusively reproduce the issue. @llali - I will ping you directly to discuss further.