Sitecore hangs when adding SXA tenant

Question

I've recently installed SXA (and its dependencies). When I right-click on Content, choose Insert Tenant, enter a name and click OK, it just sits there forever with the "running script" progress bar.

The log file shows the error below repeated over many times:

Nested Exception

Exception: System.Data.SqlClient.SqlException Message: Login failed for user 'poolsuser'. Source: .Net SqlClient Data Provider
at 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)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource 1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource 1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.b__0() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func 1 func)
at Sitecore.Xdb.Processing.Queue.SqlServer.SqlServerProcessingPoolFactory.CreateProcessingPoolCore(GenericProcessingPoolDefinition poolDefinition, Guid poolId)

10020 09:33:05 ERROR Exception when executing agent aggregation/contactProcessing Exception: Sitecore.Xdb.Processing.Queue.ProcessingPoolException Message: Login failed for user 'poolsuser'. Source: Sitecore.Xdb.Processing.Queue.SqlServer
at Sitecore.Xdb.Processing.Queue.SqlServer.SqlServerProcessingPool 2.OnCheckOut(Int16 count)
at Sitecore.Xdb.Processing.Queue.ProcessingPool 1.CheckOut(Int16 count)
at Sitecore.Analytics.Aggregation.Data.Contact.ContactWorkDispatcher.TryGetNext(ItemBatch 1& batch)
at Sitecore.Analytics.Aggregation.Data.Contact.ContactProcessingAgent.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Analytics.Core.Agent.d__1.MoveNext() --- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Analytics.Core.AsyncBackgroundService.d__22.MoveNext() --- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Analytics.Core.AsyncBackgroundService.d__26.MoveNext()

It's complaining of a failed login for the poolsuser user, but when I do a global search throughout all the config files for pooluser, I find no matches.

Any ideas ?

Answer 1

This is because the "pooluser" which is the Sql user for the Processing Pools connection string in Sitecore is not existing in your database. In order to find this you have to login into the SQL Management Studio and look for the "pooluser" login inside the Security for SqlProcessingPoolsUser. Check the json files "xconnect-xp0.json" and "sitecore-XP0.json" script you run to install sitecore you can find the "pooluser" referred there. You can create the missing user login manually inside the security to fix this or re-run the installation to fix this.

Answer 2

Your Sitecore instance is using "poolsuser" to connect to your_instance_name.Processing.Pools database.

Login failed for user 'poolsuser'. Source: .Net SqlClient Data Provider

exception could mean that username or password is invalid. It could also mean that login is disabled. Unfortunately more details (like error state code) are not logged.

Possible error state codes can be found in this article: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms366351(v=sql.105)

You should follow these steps:

1. Try to check logs in Event Viewer for further details. Especially "Security" or "Application" Windows logs. If you can find further details like error state code, compare it with codes from article mentioned above to determine precisely what is wrong

2. If 1. is not successful I would try these things:

   • Verify that "poolsuser" exists in your_instance_name.Processing.Pools database. If yes, change password for user to one in config file. This can be done by opening ConnectionStrings.config file + SQL Server Management Studio (SSMS) with your_instance_name.Processing.Pools database properties opened: Just change password to the one that is mentioned in ConnectionStrings.config file in SSMS
   • Check whether "poolsuser" has 'db_datareader' and 'db_datawriter' database role membership checked for your_instance_name.Processing.Pools database: