Using ASP.NET 2.0 SqlMembershipProvider with MSSQL 2005.
To illustrate issue :
1) Create simple ASP.NET app with a System.Web.UI.WebControls.Login control on a page. Change Web.config to use forms authentication and System.Web.Security.SqlMembershipProvider.
2) Run web app in browser and login. Close browser.
3) Using some SQL tool (like SQL Server Management Studio), run sp_who2 to see connections. Find SPID for connection used by the SqlMembershipProvider in step 2, and execute
kill <the found SPID>
(Assumes connection pooling is used, which it is by default).
4) Rerun web app in browser. Try to login again. This time a SqlException like the following is raised:
[SqlException (0x80131904): A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning() +13
System.Data.SqlClient.TdsParserStateObject.WriteSni() +682631
System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode) +265
System.Data.SqlClient.TdsParserStateObject.ExecuteFlush() +51
System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) +4163
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
System.Web.UI.WebControls.Login.AttemptLogin() +105
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Now, I understand why this is happening - i.e. its due to a pooled connection which has been killed, and is thus invalid when the Login control tries to reuse. The "kill" session command is performed in step 3 to simulate what would happen, for example, when you need to restore a database. You would need to kill all connections to a DB before being able to restore it.
Forcing the web app to restart after the kill command also solves it, but I'm wondering if there is a relatively painless way to make the Login process more robust. It would be ideal if somehow, I could catch this exception during the authentication process, and retry the login (since ADO.NET seems to clear the connection pool after a failure like this occurs, I think the retry would work).