Why does sqlConnection.Close() not close the login?

Question

We have unit tests to test that our database install and uninstall features successfully work. The unit tests use the SqlClient.SqlConnection class to check the database contents before, during and after.

Our problem is that after using SqlClient.SqlConnection, the drop login part of the uninstall fails because it claims that a user is currently logged in. Even though we have called SqlConnection.Close(), the login seems to be still open.

Our code looks a little like this:

InstallTables();  // function uses smo to create tables in a database.
string connString = CreateLogin("userName", "password");  // create login with smo

// Test the returned connection string connects to the database
using (SqlConnection con = new SqlConnection(connString))
{
    con.Open();
    //test code to read the DB version out of a table
    //Dispose calls con.Close() - I have also tried calling it explicitly
}

DropTables();  // uses smo to drop tables from the database
DropLogin("userName", "password");   // uses smo to drop the login

The DropLogin fails with the following exception: System.Data.SqlClient.SqlException: Could not drop login 'engageSecurity_unittest_129418264074692569' as the user is currently logged in.

If I remove all the SqlConnection code until after the DropLogin, then everything runs fine.

Does anyone know why the user is not logged out when I call SqlConnection.Close() ?

Is this something to do with connection pooling?

Answer 1

Unless you're explicitly disabling Connection Pooling in your connection string, my guess is that even though you're disposing the connection it is still alive in the Connection Pool (in case you decide to re-use it):

SQL Server Connection Pooling (ADO.NET)

Try disabling Connection Pooling (by adding Pooling=false; in your connection string) and see what happens.

Answer 2

Alternatives to Pooling=false are SqlConnection.ClearPool and SqlConnection.ClearAllPools Method.