Since 8.0+, Sitecore has been adding various connection strings to the ConnectionStrings.config file that are not SQL Connection strings. Out of the box, Sitecore provides one called reporting.apikey which is normally empty. However, Sitecore modules, such as Email Experience Manager have been adding a slew of new connection strings for things cipher keys, Dispatch server paths, EmailCampaignServer URL’s, etc.

These non-standard, non-SQL related connection strings, ordinarily have no problem living in the ConnectionString.config file in normal ASP.NET applications. However, Sitecore’s /sitecore/service/Heartbeat.aspx has some OLD, Legacy code that is checking for database connectivity.

What is the Sitecore Heartbeat?

The Sitecore Heartbeat, in general, is how Sitecore knows that it’s healthy, alive, and working. If the Heartbeat.aspx ever throws a 500 error, it means there’s something critically wrong with Sitecore itself and needs to be checked out.

But, turns out… Sitecore doesn’t use the Heartbeat.aspx page for its own checking. In fact, Sitecore hasn’t been using the Heartbeat.aspx since the creation of Sitecore 8, with the exception of the Rich Text Editor.

Sitecore initializes its internal Heartbeat in the pipeline in the Sitecore.Pipelines.Loader.InitializeHeartbeat processor. This processor sets up an AlarmClock task that ensures that the worker process stays active. The internal heartbeat does NOT check database connectivity.

How do I tell if I suffer from this error?

Well, unless you have had some forethought and cleverness to utilize the heartbeat.aspx page in your own infrastructure checking, you probably don’t know that you suffer from this problem. It appears that this issue has been cropping up more and more as people move Sitecore to Azure and use the Heartbeat.aspx as the probe URL to detect if Sitecore is running.

Additionally, I have seen clients use Heartbeat.aspx on LoadBalancer’s to know when Sitecore is down. This has caused strife with mature Sitecore implementations as the ConnectionStrings.config becomes more and more complex. Adding in Email Experience Manager seems to exacerbate the issue as well.

You will see this error pop up in the sitecore log when the Heartbeat.aspx page is directly accessed and causes a 500 error:

2384 08:52:16 FATAL SINGLE MSG: Sitecore heartbeat: Exception: System.Exception Message: Database data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true is not available Nested Exception 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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.Web.Services.Heartbeat.HeartbeatHelper.CheckDatabase(String connectionString) at Sitecore.Web.Services.Heartbeat.HeartbeatHelper.CheckDatabases(BeatResults results)

Where does this error occur?

Taking a deep dive into the code, let’s start at the Heartbeat.aspx page. We see that this page inherits from Sitecore.Web.Services.HeartbeatCode

The Page_Load looks like this:

protected void Page_Load(object sender, EventArgs e) { Assert.IsNotNull(sender, "sender"); Assert.IsNotNull((object) e, "e"); BeatResults beatResults = this.DoBeat(); foreach (string warning in beatResults.Warnings) Log.SingleWarn("Sitecore heartbeat: " + warning, (object) this); if (beatResults.IsOk) return; foreach (Exception error in beatResults.Errors) Log.SingleFatal("Sitecore heartbeat: ", error, (object) this); this.Response.StatusCode = 500; this.Response.End(); } }

The DoBeat() method looks like this:

protected virtual BeatResults DoBeat() { BeatResults beatResults = new BeatResults(); this.CheckDatabase(beatResults); return beatResults; } protected void CheckDatabase(BeatResults beatResults) { Assert.IsNotNull((object) beatResults, "beatResults"); HeartbeatHelper.CheckDatabases(beatResults); }

The CheckDatabase() method is what’s causing the real issue. You can see the entire helper class here:

using Sitecore.Diagnostics; using System; using System.Collections.Generic; using System.Configuration; using System.Data.SqlClient; using System.Linq; namespace Sitecore.Web.Services.Heartbeat { /// <summary>The heartbeat helper.</summary> public class HeartbeatHelper { /// <summary> /// The check databases. We iterate through all connection strings. /// If connection string is present- it supposed to be used. /// </summary> /// <param name="results">The results.</param> public static void CheckDatabases(BeatResults results) { Assert.IsNotNull((object) results, "results"); IEnumerable<string> excludeConnection = Sitecore.Configuration.Services.Heartbeat.ExcludeConnection; foreach (ConnectionStringSettings connectionString in (ConfigurationElementCollection) ConfigurationManager.ConnectionStrings) { try { if (!excludeConnection.Contains<string>(connectionString.Name, (IEqualityComparer<string>) StringComparer.InvariantCultureIgnoreCase)) HeartbeatHelper.CheckDatabase(connectionString.ConnectionString); } catch (Exception ex) { results.Errors.Add(new Exception("Database {0} is not available".FormatWith((object) connectionString.ConnectionString), ex)); } } } /// <summary>Checks availability of SQL server.</summary> /// <param name="connectionString">The connection string.</param> private static void CheckDatabase(string connectionString) { Assert.IsNotNullOrEmpty(connectionString, "connectionString"); if (connectionString.StartsWith("mongodb://")) return; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand sqlCommand1 = new SqlCommand("select * from sys.tables where 1=2", connection); int num = 1; sqlCommand1.CommandTimeout = num; using (SqlCommand sqlCommand2 = sqlCommand1) { connection.Open(); sqlCommand2.ExecuteNonQuery(); } } } } }

How do I fix it?

Sitecore, at some point in the very long past, (possibly since Sitecore 5.2+) introduced a Sitecore Setting called Sitecore.Services.Heartbeat.ExcludeConnection. You can see that the default value is a strange LocalSqlServer which doesn’t even exist in the stock connection strings.

By adding (a pipe-delimited list???? OMG) a list of connection strings name to this setting, Sitecore will ignore any connection strings from the CheckDatabases() method portion of the Heartbeat.aspx. Hang on… really? So, out of the box… I need to add a connection string to this setting, through a patch config, just to get Heartbeat.aspx to work? I would expect that this should work on a vanilla install.

But yes, that is the case right now. As I see it, this is a legacy bug that has been in the system for a very long time. As such, I have logged a bug report with Sitecore Support. My proposed solution is that the HeartbeatHelper class should be adjusted to validate a connection string as a valid SQL Connection string before attempting to check.

I consider a valid SQL connection string as a connection string that at least contains “Data Source=”. If there was a check that checked for this and ignored everything else, that would solve the problem holistically. Then that would leave the Sitecore setting specifically for SQL connection strings that, for whatever reason, you didn’t want to cause the heartbeat to fail for.

I hope this helps someone. This was a fun one to track down.