Not able to login to Sitecore if one or more publishing targets are down

Question

Other than the web, I have multiple publishing targets configured which running in different SQL servers and working properly, but if any of the additional publishing targets are not reachable, the user is not able to login to the CM and showing an error message

Error: An error occurred

For each additional targets, I have a connection configuration in ConnectionStrings.config and additional patch file like in this link

Below is the error from the log file.

8092 08:34:26 ERROR Exception processing remote events from database: web_secondary
Exception: System.Data.SqlClient.SqlException
Message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source: .Net SqlClient Data Provider
   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 Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openConnection)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass26_0.<CreateCommand>b__0()
   at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<>c__DisplayClass29_0.<CreateReader>b__0()
   at Sitecore.Data.DataProviders.NullRetryer.Execute[T](Func`1 action, Action recover)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters)
   at Sitecore.Data.DataProviders.Sql.SqlDataApi.<CreateObjectReader>d__27`1.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at Sitecore.Eventing.EventQueue.ProcessEvents(Action`2 handler)
   at Sitecore.Eventing.EventProvider.RaiseQueuedEvents()

Nested Exception

Exception: System.ComponentModel.Win32Exception
Message: The network path was not found

How we can allow the content authors to login to the CM instance even if any of the additional targets are down?

Sitecore Version: 9.2

Updated

Here is the Config patch used for additional publishing target

<?xml version="1.0" encoding="utf-8" ?>
 <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
     <sitecore>
         <eventing defaultProvider="sitecore">
            <eventQueueProvider defaultEventQueue="core">
                <eventQueue name="web_secondary" patch:after="eventQueue[@name='web']" type="Sitecore.Data.Eventing.$(database)EventQueue, Sitecore.Kernel">
                     <param ref="dataApis/dataApi[@name='$(database)']" param1="$(name)" />
                     <param hint="" ref="PropertyStoreProvider/store[@name='$(name)']" />
                </eventQueue>
            </eventQueueProvider>
         </eventing>
         <PropertyStoreProvider defaultStore="core">
             <store name="web_secondary" patch:after="store[@name='web']" prefix="web_secondary" getValueWithoutPrefix="true" singleInstance="true" type="Sitecore.Data.Properties.$(database)PropertyStore, Sitecore.Kernel">
                  <param ref="dataApis/dataApi[@name='$(database)']" param1="$(name)" />
                  <param resolve="true" type="Sitecore.Abstractions.BaseEventManager, Sitecore.Kernel" />
                  <param resolve="true" type="Sitecore.Abstractions.BaseCacheManager, Sitecore.Kernel" />
             </store>
         </PropertyStoreProvider>
         <databases>
         <!-- web_secondary -->
             <database id="web_secondary" patch:after="database[@id='web']" singleInstance="true" type="Sitecore.Data.DefaultDatabase, Sitecore.Kernel">
                 <param desc="name">$(id)</param>
                 <icon>Images/database_web.png</icon>
                 <securityEnabled>true</securityEnabled>
                 <dataProviders hint="list:AddDataProvider">
                     <dataProvider ref="dataProviders/main" param1="$(id)">
                         <disableGroup>publishing</disableGroup>
                         <prefetch hint="raw:AddPrefetch">
                              <sc.include file="/App_Config/Prefetch/Common.config" />
                              <sc.include file="/App_Config/Prefetch/Webdb.config" />
                         </prefetch>
                     </dataProvider>
                 </dataProviders>
                <PropertyStore ref="PropertyStoreProvider/store[@name='$(id)']" />
                    <remoteEvents.EventQueue>
                        <obj ref="eventing/eventQueueProvider/eventQueue[@name='$(id)']" />
                    </remoteEvents.EventQueue>
                    <archives hint="raw:AddArchive">
                        <archive name="archive" />
                        <archive name="recyclebin" />
                    </archives>
                    <cacheSizes hint="setting">
                         <data>100MB</data>
                         <items>50MB</items>
                         <paths>2500KB</paths>
                         <itempaths>50MB</itempaths>
                         <standardValues>2500KB</standardValues>
                    </cacheSizes>
              </database>
         </databases> 
     </sitecore>
 </configuration>

Answer 1

It seems sitecore service does a sanity check of all connection string entries prior to bringing up the sitecore client and since one of the connection string entries could not be verified ("web_secondary") it throws the error.

One workaround that will work is if you can temporarily change the connectionstring.config file such that:

you can comment the entry for "web_secondary" and instead add:

<add name="web_secondary" connectionString="{same value used for say web or some other publish target DB that is up}" />

This way at least you can bring up sitecore and uncheck the publish target that is down.

Answer 2

I do not have your configs with me but I have described below if SQL connection for web DB target DB itself is bad. You do NOT need to modify the connectionstring.config file anymore.

The error happens because of the "evenqueue" initialization call that runs through the Web DB sql connectionstring. So, solution for what you are are experiencing will be in the file ..\App_Config\Sitecore\CMS.Core\Sitecore.Eventing.config, you can comment the line for initialization of Web DB event queue such that:

<pipelines>
  <initialize>
      <processor type="Sitecore.Eventing.Remote.RemoteEventMap, Sitecore.Kernel" method="InitializeFromPipeline" patch:after="processor[@type='Sitecore.Pipelines.Loader.RegisterjQuery, Sitecore.Kernel']">
          <param desc="eventQueue" ref="eventing/eventQueueProvider/eventQueue[@name='core']" role:require="(Standalone or Reporting or Processing) and !ContentManagement and !ContentDelivery" />
          <!--<param desc="eventQueue" ref="eventing/eventQueueProvider/eventQueue[@name='web']" role:require="ContentManagement or ContentDelivery" />-->
      </processor>
  </initialize>
</pipelines>

This will bring up sitecore nevertheless and later when the target DB (web) is back online you should be able to publish as before. Again this example is for web DB and you might have something similar for the web_secondary DB.

Answer 3

Update from Sitecore Support team

Support team identified this as a bug and provided a hotfix for the same. After installing the hotfix and login started working fine. Now the error will come only when I try publishing to a target which is not reachable