Disabling sp_reset_connection

Question

Due to some problems I'm experiencing, I am experimenting with connection pooling in .NET Framework 4. Using SQL Profiler, I can see that every time a connection is fetched from the connection pool, the stored procedure sp_reset_connection is executed.

To get rid of this reset (I don't actually need the sp_reset_connection). I've tried setting the Connection Reset parameter in the connection string to false, but this does not seem to have any effect. I'm thinking that maybe I'm misunderstanding the purpose of the Connection reset parameter.

I have noticed that the Connection reset parameter is not documented at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx. But it is mentioned on many other places, such as http://www.techrepublic.com/article/educate-yourself-about-net-sql-server-connection-string-syntax/6084879.

If I set the Conection Reset flag to an invalid value (such as 'hello'), I get an exception when opening the connection which indicatest hat the Connection Reset flag is actually used.

Does ADO.NET actually care about the Connection reset flag?

My code below:

  static void Main(string[] args)
  {
     const string connectionString =
        "Data Source=(local);Initial Catalog=MyDatabse;User ID=sa;Password=<removed>;Connection Timeout=5;Pooling=true;Min Pool Size=1; Max Pool Size=1; Enlist=false; Connection Reset=false;";

     var connections = new List<SqlConnection>();
     for (int i = 0; i < 1000000; i++)
     {
        using (var conn = new SqlConnection(connectionString))
        {
           conn.Open();

           SqlCommand command = new SqlCommand("SELECT * FROM DatabaseVersion", conn);
           command.ExecuteNonQuery();

           connections.Add(conn);
        }
     }
  }

why not just leave it like everyone else? This isn't your problem: ask for help on *that* instead. FYI, it does quite a lot: http://stackoverflow.com/questions/641120/what-exec-sp-reset-connection-shown-in-sql-profiler-means/2924456#2924456 — gbn, Oct 28 '11 at 09:25
I agree with gbn. For what reason do you think sp_reset_connection is not needed? — Jared Moore, Oct 28 '11 at 20:36

Jared Moore · Answer 1 · 2011-10-28T23:47:06.670

SqlClient does validate the Connection Reset flag (it must be convertable to boolean). But since .NET 3.5 SP1, the value is ignored.

Additionally, the SqlConnectionStringBuilder.ConnectionReset property was made obsolete in .NET 3.5 SP1.

The reason is that setting Connection Reset = false is a huge security hole - it can allow for reusing a session that has sensitive elements associated with it such as opened symmetric keys, temporary keys, or impersonation context. Additionally it could have executed ChangeDatabase to another database, so when you reopen it then it would be connected to the 'wrong' database.

The only benefit to setting Connection Reset = false is improved performance on SQL Server 7.0 due to avoiding a round trip.

Connection Reset is documented in the .NET 3.0 docs. I assume that the reason it was removed from the 3.5 SP1 docs and later is because it now does nothing.

Disabling sp_reset_connection

1 Answers1