Search code examples
sitecoresitecore8

Sitecore Role Manager, Adding a user to a role Error: "Cannot resolve the collation conflict"

In the role manager I am adding a user to a Role, e.g. sitecore\Sitecore Local Administrator. When adding a user to it (Menu; Member-> Add) I get the error: 

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Stack Trace: 

[SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442126
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5736904
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +628
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +3731
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +89
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +379
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +240
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +81
   System.Web.Security.SqlRoleProvider.AddUsersToRolesCore(SqlConnection conn, String usernames, String roleNames) +384
   System.Web.Security.SqlRoleProvider.AddUsersToRoles(String[] usernames, String[] roleNames) +724
   Sitecore.Security.SwitchingRoleProvider.AddUsersToRoles(String[] userNames, String[] roleNames) +164
   Sitecore.Security.<>c__DisplayClass1.<AddUsersToRoles>b__0() +32
   Sitecore.Data.DataProviders.NullRetryer.ExecuteNoResult(Action action, Action recover) +161
   Sitecore.Data.DataProviders.NullRetryer.ExecuteNoResult(Action action) +15
   Sitecore.Security.SitecoreRoleProvider.AddUsersToRoles(String[] userNames, String[] roleNames) +91
   System.Web.Security.Roles.AddUsersToRole(String[] usernames, String roleName) +140
   Sitecore.Shell.Framework.Commands.RoleManager.AddMember.AddAccounts(ListString roles, ListString users, String roleName) +174
   Sitecore.Shell.Framework.Commands.RoleManager.AddMember.Run(ClientPipelineArgs args) +505

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
   System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +92
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +101
   Sitecore.Reflection.ReflectionUtil.InvokeMethod(MethodInfo method, Object[] parameters, Object obj) +58
   Sitecore.Pipelines.Processor.Invoke(PipelineArgs args) +243
   Sitecore.Nexus.Pipelines.NexusPipelineApi.Resume(PipelineArgs args, Pipeline pipeline) +252
   Sitecore.Pipelines.Pipeline.Resume() +38
   Sitecore.Web.UI.WebControls.ContinuationManager.ResumePipeline(HttpContext httpContext) +461
   Sitecore.Web.UI.WebControls.ContinuationManager.OnPreRender(EventArgs e) +299
   System.Web.UI.Control.PreRenderRecursiveInternal() +88
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Control.PreRenderRecursiveInternal() +160
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4775
Solution

  • The default collation level in Sitecore databases is "SQL_Latin1_General_CP1_CI_AS". I am not sure where did you get "Latin1_General_CI_AS". You can try to execute something like:

    SELECT * FROM [model].[sys].[all_columns]
WHERE collation_name = 'Latin1_General_CI_AS'

    to get all columns with "Latin1_General_CI_AS" collation.

    When you will know column(it should be one from "aspnet_****" tables) that cause conflict, you could change column collation:

    ALTER TABLE dbo.aspnet_XXX ALTER COLUMN YYY  
varchar(ZZZ) COLLATE SQL_Latin1_General_CP1_CI_AS

    Added from discussion in comments: As far as I understand error is thrown in aspnet_UsersInRoles_AddUsersToRoles stored procedure. There are only tables from core database named "aspnet_****" and temporary tables. If problem is not in existing columns then it makes sense to check default database collation as temporary tables are created with this location. So, take a look on Sitecore_Core database properties and check its collation.