Disabling sp_reset_connection

Question

Due to some problems that I have encountered, I am experimenting with the connection pool in the .NET Framework 4. Using SQL Profiler, I see that every time the connection is retrieved from the connection pool, the stored procedure sp_reset_connection is executed.

To get rid of this reset (I really don't need sp_reset_connection). I tried setting the Connection reset parameter in the connection string to false, but this does not seem to have any effect. I think that perhaps I do not understand the purpose of the Connection reset parameter.

I noticed that the Connection reset parameter is not registered in http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx . But this is mentioned in many other places, for example 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 (for example, "hello"), I get an exception when opening a connection, which indicates that the Connection reset flag is actually being used.

Does ADO.NET really care about Connection reset flag?

My code is 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); } } } 

Answer 1

SqlClient checks the Connection Reset flag (it must be converted to a logical one). But with .NET 3.5 SP1, the value is ignored .

In addition, the SqlConnectionStringBuilder.ConnectionReset property was deprecated in .NET 3.5 SP1.

The reason is that setting Connection Reset = false is a huge security hole - it can allow you to reuse a session with associated sensitive elements, such as public symmetric keys, temporary keys, or an impersonation context. In addition, it could run ChangeDatabase in another database, so when you open it again, it will be associated with the “wrong” database.

The only benefit of setting Connection Reset = false is the increased performance on SQL Server 7.0 by avoiding round trip.

Connection Reset documented in the .NET 3.0 docs . I assume that the reason she was removed from documents 3.5 SP1 and later is because she is not doing anything now.