Troubleshooting User Manager-related functionality issues


Description

In specific cases, a user might experience some issues while trying to load the User Manager for sites that have a large number of accounts. There might be the following related symptoms:

 

Loading time out

Loading User Manager might time out.

Solution

To avoid timeouts, add the commandTimeout attribute to the following configuration node (the default value of this attribute is 30 seconds):

<add name="sql" type="System.Web.Security.SqlMembershipProvider" connectionStringName="core" applicationName="sitecore" minRequiredPasswordLength="1" minRequiredNonalphanumericCharacters="0" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="256" commandTimeout="180"/>

 

System.Web.HttpUnhandledException when running a web request

The following unhandled exception might appear during the running of the current web request:

ERROR Application error.
Exception: System.Web.HttpUnhandledException
Message: Exception of type 'System.Web.HttpUnhandledException' was thrown.
Source: System.Web
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
Nested Exception
Exception: System.Data.SqlClient.SqlException
Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Source: .Net SqlClient Data Provider
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
...

Solution

Increase the default value of this setting:

<!--  DEFAULT SQL TIMEOUT
            The default timeout for all SQL commands issued by the Sitecore APIs, such as
            the SqlServerDataProvider, the OracleDataProvider or the SqlUtil classes.
            Default value: 00:05:00 (5 minutes)
      -->
    <setting name="DefaultSQLTimeout" value="00:05:00" />

 

Slow loading when the Core database with users is running locally

The loading might be very slow, even if the Core database with users is running locally.

Solution

To speed up the load time, navigate to the aspnet_Membership aspnet_Users table located in the core database in the SQL Server Management Studio. Check its indexes fragmentation and consider performing index defragmentation procedure if the fragmentation is higher than 30%. Check the index fragmentation and perform index rebuild according to the Microsoft Recommendations.

 

Timeout when using ASP.NET membership provider to store a lot of users

The User Manager application might be slow or fail by timeout when using ASP.NET membership provider to store a lot (tens of thousands) of users.

There is an example of failure by timeout:

[Win32Exception (0x80004005): The wait operation timed out]
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
System.Data.SqlClient.SqlDataReader.get_MetaData()
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
System.Web.Security.SqlMembershipProvider.GetAllUsers(Int32 pageIndex, Int32 pageSize, Int32& totalRecords)
Sitecore.Security.SwitchingMembershipProvider.b__6(MembershipProviderWrapper wrapper, Int32 index, Int32 count, Int32& providerRecords)
Sitecore.Common.PagingHelper`2.GetInputRecords(TInput input, GetElements getElements, Int32 startRecord, Int32 recordCount, Int32& totalRecords)
Sitecore.Common.PagingHelper`2.GetRecords(Int32 pageIndex, Int32 pageSize, IEnumerable`1 inputList, GetElements getElements, Int32& totalRecords)
Sitecore.Security.SwitchingMembershipProvider.GetUsers(MembershipProviderWrapperList wrappers, Int32 pageIndex, Int32 pageSize, Int32& totalRecords, GetElements getElements)
Sitecore.Security.SwitchingMembershipProvider.GetAllUsers(Int32 pageIndex, Int32 pageSize, Int32& totalRecords)
Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover)
Sitecore.Security.SitecoreMembershipProvider.GetAllUsers(Int32 pageIndex, Int32 pageSize, Int32& totalRecords)
...

Prerequisites

  1. Ensure you are using Sitecore XP 9.0 or later.
  2. Ensure Sitecore is the only application used by the membership provider.
    To verify the list of applications in use, run a SQL query against the database that stores membership data (usually, the Core or the Security database): 
    SELECT [ApplicationName] FROM [aspnet_Applications]

Solution

To optimize the aspnet_Membership_GetAllUsers stored procedure for a single application, download and execute the attached SQL script.

 

The search box is limited

The search box might be limited to 20 characters.

Solution

  1. Download the ScriptUserManager.txt file
  2. Add the JavaScript code from this file to the\sitecore\shell\Applications\Security\UserManager\UserManager.aspx page in order to change the maximum length of the search input control.