Sql – ASP.NET login control and ADO.NET connection pooling

ado.netasp.netsql server

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).

Best Answer

You could possiblly create a new Login control that inherits the asp:login control then override the ValidateUser method, much like:

public override bool ValidateUser(string strName, string strPassword)
{    
    bool boolReturn = false;

    UserProvider oUserProvider = new UserProvider();
    User oUser = oUserProvider.FindUser(strName);

    if (oUser == null)
        return boolReturn;

    boolReturn = oUser.ValidateUsersPasswordForLogon(strPassword);

    return boolReturn;
}

Then put a try/catch around the Creation of the User class (catching an SqlException) then retrying the connection?

I havnt done too much work with the aspnet membership classes so im not too sure

Related Topic