Sitecore 9 - An error occurred when trying to Log-in

Description

When you try to login to Sitecore backend you would get an error occured message.

When a session expires in Sitecore the information is logged in the properties table as "SC_TICKET". The "SC_TICKET" records corresponding to expired sessions are automatically deleted by Sitecore.Tasks.CleanupAuthenticationTicketsAgent from the Properties table of a database. However, if the agent fails to clean them up, the expired records remain in the Properties table. Large numbers of "SC_TICKET" records can lead to performance degradation of the login process or might not allow users to login.

Exception Details

ERROR String or binary data would be truncated.

The statement has been terminated.

Exception: System.Exception

Message: String or binary data would be truncated.

The statement has been terminated.

Source: Sitecore.Kernel

at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery()

at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass33_0.b__0()

at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)

at Sitecore.Data.Properties.SqlPropertyStore.SetStringValueCore(String prefixedName, String value)

at Sitecore.Data.Properties.PropertyStore.SetStringValue(String name, String value)

at Sitecore.Web.Authentication.DefaultTicketManager.CreateTicket(String userName, String startUrl, Boolean persist)

at Sitecore.Owin.Authentication.Pipelines.CookieAuthentication .SignedIn.CreateTicket.Process(SignedInArgs args)

at (Object , Object )

at Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args)

at Sitecore.Pipelines.DefaultCorePipelineManager .Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfNotExists)

at Sitecore.Pipelines.DefaultCorePipelineManager .Run(String pipelineName, PipelineArgs args, String pipelineDomain)

at Sitecore.Owin.Authentication.Security.Cookies.DefaultCookie AuthenticationProvider.ResponseSignedIn(CookieResponseSignedInContext context)

at Microsoft.Owin.Security.Cookies.CookieAuthenticationHandler.d__f.MoveNext()

— End of stack trace from previous location where exception was thrown —

Nested Exception

Exception: System.Data.DataException

Message: Error executing SQL command: INSERT INTO [Properties] ( [Key], [Value] ) VALUES ( @name, @value )

Solution

The issue is resolved in Sitecore 10. There is a hotfix for available for Sitecore 8.2, 9.0u1 and 9.0u2 check the KB article.

https://kb.sitecore.net/articles/615926#Solution

Sitecore 9.1.0 Initial release is not listed in the KB article.

Please find the hotfix available at the following URL:

https://sitecore.box.com/s/4ujfdao8iugx0l1g96nmo0oaj59jcs1t

Caution: Be aware that the hotfix was built specifically for each Sitecore version, and you should not install it on other Sitecore versions or in combination with other hotfixes, unless explicitly instructed by Sitecore Support. Ensure with Sitecore Support before applying.

Workaround

The workaround for the problem is to manually clear the records in the core db properties table associated with this issue.

Caution: All the content authors will be logged out when the query is executed.

USE [your_core_database_name];

DELETE FROM [dbo].[Properties]

WHERE [dbo].[Properties].[Key] like '%SC_TICKET%'

I had a situation only one user had issue and I dont want to execute the query in working hours that would need to be communicated and lot of work.

USE [your_core_database_name];

DELETE FROM [dbo].[Properties]

WHERE [dbo].[Properties].[Key] LIKE '%SC_TICKET%'

AND [dbo].[Properties].[Value] LIKE '%REPLACE_USER_ID%'

Photo by Romson Preechawit on Unsplash