I recently came across a problem that drove me crazy for several hours.  I've installed secondary site servers many times and have the setup down pat to work with my usual secure configuration.  The secondary site server installed ok, but then the mpcontrol.log showed entries similar to the following:

Attempting to connect to the configured SQL database.
*** [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
*** [28000][18456][Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
*** Failed to connect to the SQL Server.
Failed to get connection to the configured SQL database.
Failed to connect to the configured SQL database.

The primary site server Security event log had successful Anonymous Logon events using NTLM, but SQL audited failed connection attempts.  The SQL errorlog showed State 11, "Valid login but server access failure."  Why is the secondary site server trying to authenticate anonymously via NTLM instead with its domain computer account via Kerberos?

This problem is almost always related to a bad Service Principal Name (SPN) on the domain service account under which SQL is running.  From the Windows Server 2003 site server, I re-checked the SPN multiple times, for example,

c:\>setspn -L service-cmsql
CN=ConfigMgr SQL Service Acccount,OU=Service,OU=Accounts,DC=domain,DC=local
        MSSQLSvc/sccm01.domain.local:1234
        MSSQLSvc/sccm01:1234

I could not find anything wrong with them each time I looked.  It wasn't until I was working on another issue in the same environment that I happened to run setspn on a Windows Server 2008 system, and noticed new options: -Q to query for an SPN and -X to search for duplicate SPNs.  So now searching for the SPN across the entire domain yielded, for example:

c:\>setspn -Q MSSQLSvc/sccm01:1234
CN=ConfigMgr Installation Account,OU=Service,OU=Accounts,DC=domain,DC=local
        MSSQLSvc/sccm01:1234
CN=ConfigMgr SQL Service Acccount,OU=Service,OU=Accounts,DC=domain,DC=local
        MSSQLSvc/sccm01.domain.local:1234
        MSSQLSvc/sccm01:1234

Ah-ha!  During the installation, the SPN was accidentally added to the installation account as well as the SQL service account.  Since the SPN could not be validated, the secondary site server couldn't use Kerberos to authenticate so fell back to NTLM.  I whacked the SPN on the installation account, forced AD replication, and now the secondary site server is working and authenticating as expected.

In researching this issue, I discovered that setspn for Windows Server 2003 was updated in November 2009 to include the additional command line options that are also present on Server 2008 and beyond.  See KB970536 to download the update package.

Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of included script samples are subject to the terms specified in the Terms of Use.