FIX Unable to Connect to SCCM ConfigMgr Site database after moved to a failover cluster

FIX Unable to Connect to SCCM ConfigMgr Site database after moved to a failover cluster. See, Tyler Franke’s post on 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.

Latest Post – Fix Report Server Cannot Open A Connection Error ConfigMgr | SCCM HTMD Blog (anoopcnair.com)

FIX Unable to Connect to SCCM ConfigMgr Site database after moved to a failover cluster

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.

FIX Unable to Connect to SCCM ConfigMgr Site database after being moved to a failover cluster. Additionally, you may find this or something similar to this in the %ProgramFiles%\Microsoft Configuration Manager\Logs\Smsdbmon.log:

CTriggerManager::Init – unable to get SQL connection
*** [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
*** Failed to connect to the SQL Server.

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 – FIX Unable to Connect to SCCM ConfigMgr Site database after moved to a failover cluster

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:

setspn -A MSSQLSvc/ <domain\sql-server-service-account>
setspn -A MSSQLSvc/ <domain\sql-server-service-account>
setspn -A MSSQLSvc/: <domain\sql-server-service-account>
setspn -A MSSQLSvc/: <domain\sql-server-service-account>

References:

How to Move the Site Database: http://technet.microsoft.com/en-us/library/bb680707.aspx

ConfigMgr 2007: How to move the Site Database: http://blogs.technet.com/b/configurationmgr/archive/2010/01/28/configmgr-2007-how-to-move-the-site-database.aspx

How to Configure an SPN for SQL Server Site Database Servers: http://technet.microsoft.com/en-us/library/bb735885.aspx

Hope this helps!

Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…