Conn.Close() and Login Error w/Subsequent Conn.Open()

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Christopher Walls (msnews_at_travantsolutions.com)
Date: 05/04/04 

Date: Tue, 4 May 2004 11:37:08 -0500

Overview:
=============
I have a data conversion program that is reading data by accessing the Sql
Data Access classes directly and also is using an object layer to
retrieve/store data. The code was working properly until recently (what
changed is unknown). I execute some number of statements without any
problems. However, now at the same point every time I get a Login Fails Sql
exception. Unfortunately it will be difficult to post all of the code,
though I may be able to post snippets.

I'm using v1.1 of the .NET Framework.
I'm using the ADO.NET SqlClient classes to connect to a SQL Server 2K
database.
The exact same connection string (user id/password) is used for all
Conn.Open() calls.

Scenario:
=============
Open Connection
Execute several Inserts via ExecuteNonQuery()
Close Connection

Open Connection
Retrieve records via Reader.
Close Reader (behavior closes connection)

Open Connection
Retrieve a record via Reader
Close Reader (behavior closes connection)

Open Connection
Begin Transaction
Execute Update via ExecuteNonQuery
Commit Transaction
Close Connection

Open Connection ==> Login failed for user 'LoginID' Sql Number: 18456

Troubleshooting
===================
* I've turned connection pooling off.
* I've traced through the code both by logging and stepping though and I'm
confident I'm closing the connection correctly.
* I've added a Conn.Dispose() statement after the Conn.Close() statement
* I've added a Reader.Dispose() statement after the Reader.Close() statement
* The command immediately proceeding the open that fails contains a
transaction. However, I've commented out the transaction code and the error
still occurs.
* I've monitored the Processes via SQL Server Manager's "Current Activity"
and I don't find any processes once my close() methods have executed.
* I've executed this code against different databases on the same server,
and then tried again on a different server.
* I create a new connection object each time prior to invoking open();
* I have try..catch blocks around the open and close so I am confident the
close is succeeding. I set the connection object to NULL after invoking
close() and dispose().

Questions
===================
1) How can I get more information as to why the login failed? It has to be
something I'm setting since I use the same login repeatedly prior to where
the code fails.
2) Would failing to close a connection properly cause subsequent logins to
fail? The code fails regardless if connection pooling is on or off.
3) Assuming a valid LoginID/Password, what other conditions would cause a
login to fail?

Any suggestions would be greatly appreciated.

- Chris

Relevant Pages