Unable to connect to site database after moved to a failover cluster

See, the 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. 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:

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

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!

Posted in: ConfigMgr (SCCM)

Leave a Comment and Contact Anoop

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On YoutubeVisit Us On LinkedinCheck Our Feed