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

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

  • Comments 1
  • Likes

imageHi 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:

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/<NetBIOS-of-SQL-cluster-virtual-name> <domain\SQL-Server-Service-Account>
setspn -A MSSQLSvc/<FQDN-of-SQL-cluster-virtual-name> <domain\SQL-Server-Service-Account>
setspn -A MSSQLSvc/<NetBIOS-of-SQL-cluster-virtual-name>:<port-number> <domain\SQL-Server-Service-Account>
setspn -A MSSQLSvc/<FQDN-of-SQL-cluster-virtual-name>:<port-number> <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!

Tyler Franke | Senior Support Escalation Engineer

The App-V Team blog: http://blogs.technet.com/appv/
The WSUS Support Team blog: http://blogs.technet.com/sus/
The SCMDM Support Team blog: http://blogs.technet.com/mdm/
The ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/
The SCOM 2007 Support Team blog: http://blogs.technet.com/operationsmgr/
The SCVMM Team blog: http://blogs.technet.com/scvmm/
The MED-V Team blog: http://blogs.technet.com/medv/
The DPM Team blog: http://blogs.technet.com/dpm/
The OOB Support Team blog: http://blogs.technet.com/oob/
The Opalis Team blog: http://blogs.technet.com/opalis
The Service Manager Team blog: http: http://blogs.technet.com/b/servicemanager
The AVIcode Team blog: http: http://blogs.technet.com/b/avicode
The System Center Essentials Team blog: http: http://blogs.technet.com/b/systemcenteressentials
The Server App-V Team blog: http: http://blogs.technet.com/b/serverappv

clip_image001 clip_image002

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • faced the same errors when the opening the console and the same error in SMSDBMON.log file...

    i applied the SPN commands but still cannot open a connection...

    any suggesstions