ConfigMgr 2007 Fix: Unable to connect to site database after it’s moved to a failover cluster

Hi everyone, Tyler Franke here, and today I wanted to tell you about an interesting issue I ran into the other day after moving a site database to a failover cluster.

After following our written procedures to move the site database into a SQL Server 2008 named instance hosted in Windows Server 2008 Failover Clustering, you may find that your site server is unable to connect to the database. An error like the following may be encountered when launching the ConfigMgr 2007 console or the console may open and never populate any data:

The ConfigMgr console could not connect to the ConfigMgr site database. Verify that this computer has network connectivity to the SMS Provider computer and that your user account has Remote Activation permissions on both the ConfigMgr site server and SMS Provider computers. For more information, see "How to Configure DCOM Permissions for Configuration Manager Console. The ConfigMgr Provider reported an error.

Additionally, you may find this or something similar to this in the %ProgramFiles%\Microsoft Configuration Manager\Logs\Smsdbmon.log:

This can occur if the SQL Server Service on the cluster is configured to use the "NT AUTHORITY\LOCAL SYSTEM" account and not a domain-based service account. Due to this, even though there are SPN’s registered for the SQL virtual name they are not registered to any account.

Resolution

To resolve this issue, change the SQL Server Service to use a domain-based service account, then use SETSPN.exe to register the proper SPN’s. In a case where the instance is set to use dynamic TCP ports, take the additional step to register two additional SPN’s without an associated port number: