Sitecore XP 9.3 to 10.4: Fixing Login Issues After Restoring the Database

Written by

Álvaro Montenegro

When migrating a Sitecore XP 9.3 database to 10.4, it’s common to face login issues — especially when copying users from production. This might happen if you’re transforming a production 9.3 db to 10.4.

Let’s take a look at the ‘Login failed‘ error after restoring the database.

The Issue

After restoring a database, usually from production, to a development or staging environment and upgrading it to the 10.4 version, you may encounter this error when accessing the platform:

HTML
Server Error in '/' Application.
Login failed for user 'masteruser'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'masteruser'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Login failed for user 'masteruser'.]
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) +1481
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1150
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +70
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +825
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +103
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1598
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +159
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +382
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +307
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +198
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +422
   System.Data.SqlClient.SqlConnection.Open() +199
   Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openConnection) +113
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass26_0.<CreateCommand>b__0() +48
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +293
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass29_0.<CreateReader>b__0() +30
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +293
   Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters) +299
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass95_0.<SelectIDs>b__0(Int32 batchNumber) +594
   System.Threading.Tasks.<>c__DisplayClass17_0`1.<ForWorker>b__1() +1396
   System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask) +32
   System.Threading.Tasks.<>c__DisplayClass176_0.<ExecuteSelfReplicating>b__0(Object <p0>) +278

[AggregateException: One or more errors occurred.]
   System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) +5514857
   System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) +14399792
   System.Threading.Tasks.Parallel.ForWorker(Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally) +1446
   System.Threading.Tasks.Parallel.For(Int32 fromInclusive, Int32 toExclusive, Action`1 body) +113
   Sitecore.Data.DataProviders.Sql.SqlDataProvider.SelectIDs(ID[] filterIds, CallContext context) +275
   Sitecore.Data.DataProviders.Sql.SqlDataProvider.PrefetchNotExistedItems(IdCollection prefetchingItems, CallContext context) +102
   Sitecore.Data.DataProviders.CompositeDataProvider.PrefetchResourceItems() +688
   Sitecore.Data.DefaultDatabase.AddDataProvider(DataProvider provider) +49

[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) +216
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +182
   Sitecore.Configuration.DefaultFactory.AssignProperties(Object obj, Object[] properties) +853
   Sitecore.Configuration.DefaultFactory.AssignProperties(XmlNode configNode, String[] parameters, Object obj, Boolean assert, Boolean deferred, IFactoryHelper helper) +641
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper helper) +326
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert) +72
   Sitecore.Configuration.DefaultFactory.CreateObject(String configPath, String[] parameters, Boolean assert) +703
   Sitecore.Configuration.DefaultFactory.GetDatabase(String name, Boolean assert) +157
   Sitecore.Configuration.DefaultFactory.GetDatabase(String name) +55
   Sitecore.Configuration.DefaultFactory.GetDatabases() +121
   Sitecore.Data.SqlServer.SqlServerLinkDataProviderFactory.CreateProvidersForAllDatabases() +161

[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) +118
   System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +182
   Sitecore.Reflection.ReflectionUtil.CallStaticMethod(String typeName, String methodName, Object[] parameters) +165
   Sitecore.Configuration.DefaultFactory.CreateFromFactoryMethod(XmlNode configNode, String[] parameters, Boolean assert) +534
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper helper) +91
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert) +72
   Sitecore.Configuration.DefaultFactory.GetConstructorParameters(XmlNode configNode, String[] parameters, Boolean assert) +161
   Sitecore.Configuration.DefaultFactory.CreateFromTypeName(XmlNode configNode, String[] parameters, Boolean assert) +114
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert, IFactoryHelper helper) +163
   Sitecore.Configuration.DefaultFactory.CreateObject(XmlNode configNode, String[] parameters, Boolean assert) +72
   Sitecore.Configuration.DefaultFactory.CreateObject(String configPath, String[] parameters, Boolean assert) +703
   Sitecore.Configuration.DefaultFactory.GetLinkDatabase() +36
   Sitecore.DefaultGlobals.Load() +294
   (Object , Object ) +55
   Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +1450
   Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfNotExists) +236
   Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain) +22
   Sitecore.Nexus.Web.HttpModule.Application_Start() +146
   Sitecore.Nexus.Web.HttpModule.Init(HttpApplication app) +918
   System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +584
   System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +168
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +277
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +369

[HttpException (0x80004005): Exception has been thrown by the target of an invocation.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +532
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +111
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +719

☝️ Very common. Everyone already knows this. There are many StackOverflow solutions. Blablabla…

However…

When it’s hosted on Azure, there are additional steps that we need to follow to fix it properly.

The Cause

The restored database contains wrong users, which differ from those in the ConnectionStrings.config of the desired/target environment.

The Solution

We will retrieve the right Passwords from the ConnectionString of the environment, and update them directly in the database.

So the steps would be:

  1. Go to the ConnectionStrings.xml of your App Service using Kudu from App > Development Tools > Advanced Tools, and reach out to this address:
https://<your-app>.scm.azurewebsites.net/api/vfs/site/wwwroot/app_config/connectionstrings.xml
  1. Locate the <add name=”…” element corresponding to your database user (e.g., masteruser, webuser, securityuser) and copy the password.
    1. Now, update the users in the SQL Server using this FULL SQL script:
      • Open SQL Server Management Studio (SSMS), connect to the server, and run each statement separate by database:
    SQL
    -- =============================================
-- User creation script for multiple databases
-- =============================================

USE [sitecore-dev-core-db]
DROP USER IF EXISTS [coreuser]
GO

CREATE USER [coreuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [coreuser];
exec sp_addrolemember 'db_datawriter', [coreuser];

GO

grant execute to [coreuser]
grant connect to [coreuser]
GO

DROP USER IF EXISTS [securityuser]
GO

CREATE USER [securityuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [securityuser];
exec sp_addrolemember 'db_datawriter', [securityuser];

GO

grant execute to [securityuser]
grant connect to [securityuser]
GO

---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-master-db]
DROP USER IF EXISTS [masteruser]
GO

CREATE USER [masteruser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [masteruser];
exec sp_addrolemember 'db_datawriter', [masteruser];

GO

grant execute to [masteruser]
grant connect to [masteruser]
GO

---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-web-db]
DROP USER IF EXISTS [webuser]
GO

CREATE USER [webuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [webuser];
exec sp_addrolemember 'db_datawriter', [webuser];

GO

grant connect to [webuser]
grant execute to [webuser]
GO

---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-processingenginestorage-db]
DROP USER IF EXISTS [processingengineuser]
GO

CREATE USER [processingengineuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [processingengineuser];
exec sp_addrolemember 'db_datawriter', [processingengineuser];

GO

grant connect to [processingengineuser]
grant execute to [processingengineuser]
GO

---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-processingenginetasks-db]
DROP USER IF EXISTS [processingengineuser]
GO

CREATE USER [processingengineuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [processingengineuser];
exec sp_addrolemember 'db_datawriter', [processingengineuser];

GO

grant connect to [processingengineuser]
grant execute to [processingengineuser]
GO

---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-smm-db]
DROP USER IF EXISTS [xcsmmuser]
GO

CREATE USER [xcsmmuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [xcsmmuser];
exec sp_addrolemember 'db_datawriter', [xcsmmuser];
GO

grant connect to [xcsmmuser]
grant execute to [xcsmmuser]
GO

---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-shard0-db]
DROP USER IF EXISTS [xcsmmuser]
GO

CREATE USER [xcsmmuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [xcsmmuser];
exec sp_addrolemember 'db_datawriter', [xcsmmuser];
GO

grant connect to [xcsmmuser]
grant execute to [xcsmmuser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-shard1-db]
DROP USER IF EXISTS [xcsmmuser]
GO

CREATE USER [xcsmmuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [xcsmmuser];
exec sp_addrolemember 'db_datawriter', [xcsmmuser];
GO

grant connect to [xcsmmuser]
grant execute to [xcsmmuser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-reporting-db]
DROP USER IF EXISTS [reportinguser]
GO

CREATE USER [reportinguser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [reportinguser];
exec sp_addrolemember 'db_datawriter', [reportinguser];
GO

grant connect to [reportinguser]
grant execute to [reportinguser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-tasks-db]
DROP USER IF EXISTS [tasksuser]
GO

CREATE USER [tasksuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [tasksuser];
exec sp_addrolemember 'db_datawriter', [tasksuser];
GO

grant connect to [tasksuser]
grant execute to [tasksuser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-pools-db]
DROP USER IF EXISTS [poolsuser]
GO

CREATE USER [poolsuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [poolsuser];
exec sp_addrolemember 'db_datawriter', [poolsuser];
GO

grant connect to [poolsuser]
grant execute to [poolsuser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-refdata-db]
DROP USER IF EXISTS [xcrefdatauser]
GO

CREATE USER [xcrefdatauser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [xcrefdatauser];
exec sp_addrolemember 'db_datawriter', [xcrefdatauser];
GO

grant connect to [xcrefdatauser]
grant execute to [xcrefdatauser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-forms-db]
DROP USER IF EXISTS [formsuser]
GO

CREATE USER [formsuser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [formsuser];
exec sp_addrolemember 'db_datawriter', [formsuser];
GO

grant connect to [formsuser]
grant execute to [formsuser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-exmmaster-db]
DROP USER IF EXISTS [exmmasteruser]
GO

CREATE USER [exmmasteruser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [exmmasteruser];
exec sp_addrolemember 'db_datawriter', [exmmasteruser];
GO

grant connect to [exmmasteruser]
grant execute to [exmmasteruser]
GO
---------------------------------------------------------------------------------------------------------
USE [sitecore-dev-ma-db]
DROP USER IF EXISTS [xcmauser]
GO

CREATE USER [xcmauser] WITH PASSWORD = 'YourStrongPasswordHere123!';
GO

exec sp_addrolemember 'db_datareader', [xcmauser];
exec sp_addrolemember 'db_datawriter', [xcmauser];
GO

grant connect to [xcmauser]
grant execute to [xcmauser]
GO

    This will:

    • Create the login in the desired SQL Server
    • Grant read/write access to the databases
    1. Restart the App Service
      • Go to your Azure App Service and restart it to apply changes.

    👋 That’s it, thanks!

    Category:

    Tags: , , , , , ,

    Leave a Reply

    Your email address will not be published. Required fields are marked *


    The reCAPTCHA verification period has expired. Please reload the page.