locked
SYSTEM suddenly changed to ANONYMOUS LOGON after update RRS feed

  • Question

  • After installing recent windows updates on a server, a script that accessed SQL server from the SYSTEM account suddenly gets rejected with "Msg 18456 ... ANONYMOUS LOGON".  This is a much simpler scenario than what I have seen in other threads.

    There is no domain, this is a standalone server outside the firewall.

    This is not really a network access, everything happens on one server.

    Both server OS and SQL Server are version 2008 R2.

    This access is from a scheduled task (Windows Scheduler, AT command) running as Local System .

    A similar machine which hasn't received the same Windows updates doesn't fail.

    Here is a simplified proof-of-concept batch file demonstrating the problem:

    ------- Typed this in because IE refuses to let me paste in this forum -------
    SET COMPUTER >%TMP%\testout.txt 2>&1
    SET USER >>%TMP%\testout.txt 2>&1
    whoami >>%TMP%\testout.txt 2>&1
    sqlcmd -Q "USE master;" >>%TMP%\testout.txt 2>&1
    ------- End of file -------

    Ran this with the command

    AT hh:mm "C:\Windows\Temp\test.bat"

    and then waited until hh:mm to grab testout.txt.

    Output on computer where this fails (I replaced computer name with BAD and workgroup name with CONTOSO):

    ------- Typed this in because IE refuses to let me paste in this forum -------
    COMPUTERNAME=BAD
    USERDOMAIN=CONTOSO
    USERNAME=BAD$
    USERPROFILE=C:\Windows\system32\config\systemprofile
    nt authority\system
    Msg 18456, Level 14, State 1, Server BAD, Line 1
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    ------- End of file -------

    Output on very similar computer with fewer windows updates applied (I replaced computer name with GOOD and workgroup name with CONTOSO):

    ------- Typed this in because IE refuses to let me paste in this forum -------
    COMPUTERNAME=GOOD
    USERDOMAIN=CONTOSO
    USERNAME=GOOD$
    USERPROFILE=C:\Windows\system32\config\systemprofile
    nt authority\system
    Changed database context to 'master'.
    ------- End of file -------

    
    Thursday, January 14, 2016 8:27 PM

Answers

  • I finally found the cause myself:

    If the hosts file maps one or more "DNS" names to the IP address of the machine itself, and the *first* of those is not the computer name ("BAD" in the examples), SQL server arbitrarily fails "network" authentication with Windows Accounts.

    For example, the following line in %SystemRoot%\System32\Drivers\etc\hosts caused the problem:

    10.10.10.10 BAD-lan1 BAD BAD-alias

    While the following line made everything work again

    10.10.10.10 BAD BAD-lan1 BAD-alias

    Presumably some part of the SQL server code incorrectly makes a reverse name lookup of the server IP address and uses this in an API call that must use the Win32 computer name and not the network name of one of the computer's IP addresses, and that incorrect name lookup returns the first name from the line in the hosts file as the "canonical" name of the IP address.

    I would consider this a SQL server 2008 R2 product bug.

    • Marked as answer by 74JBlw Friday, January 29, 2016 6:26 AM
    Friday, January 29, 2016 6:26 AM

All replies

  • Have you granted access to SQL Server for the machine accounts BAD$ and GOOD$?

    Thursday, January 14, 2016 10:05 PM
  • Those machine accounts are a figment of Windows imagination in setting the environment variables.  There is no domain, these are standalone servers.

    sysadmin Server role has been granted to the "BUILTIN\Administrators" group, and directly to the "NT AUTHORITY\SYSTEM" account.

    It used to work on both servers until server "BAD" received a bunch of Windows Updates and was rebooted, naturally, the updates are being held back from other servers until the cause has been isolated and fixed.

    Thursday, January 14, 2016 10:51 PM
  • Yes, I know that they are machine accounts. That's why I asked if you had granted access to them.

    Friday, January 15, 2016 10:18 PM
  • When a machine is not joined to a domain (there is no domain only a workgroup), there are no machine accounts, only the builtin accounts such as SYSTEM and ANONYMOUS LOGIN, and the actual user accounts such as <computername>\john .

    Because the computer's workgroup name is stored in the same registry locations as a computer domain name membership, some part of the Windows code is setting the USERDOMAIN environment variable for the SYSTEM account as if the machine was part of a domain by that name.  That same code also sets the USERNAME environment variable to the name that a machine account on that domain would have had, if the machine was actually a member of any domain (which it isn't).

    That is why I am saying that those machine accounts are a figment of Windows imagination, because there are not and cannot be any machine accounts in this scenario.

    Monday, January 18, 2016 1:44 PM
  • I finally found the cause myself:

    If the hosts file maps one or more "DNS" names to the IP address of the machine itself, and the *first* of those is not the computer name ("BAD" in the examples), SQL server arbitrarily fails "network" authentication with Windows Accounts.

    For example, the following line in %SystemRoot%\System32\Drivers\etc\hosts caused the problem:

    10.10.10.10 BAD-lan1 BAD BAD-alias

    While the following line made everything work again

    10.10.10.10 BAD BAD-lan1 BAD-alias

    Presumably some part of the SQL server code incorrectly makes a reverse name lookup of the server IP address and uses this in an API call that must use the Win32 computer name and not the network name of one of the computer's IP addresses, and that incorrect name lookup returns the first name from the line in the hosts file as the "canonical" name of the IP address.

    I would consider this a SQL server 2008 R2 product bug.

    • Marked as answer by 74JBlw Friday, January 29, 2016 6:26 AM
    Friday, January 29, 2016 6:26 AM
  • I was not able to repro this. But I tested on a machine running Windows 10 and SQL 2016.

    In any case, I don't think this is bug in SQL 2008 R2, as it supposedly relies OS routines for the lookup. Furthermore, it takes the login token from Windows.

    It also sounds a little funny that this matters at all, since a local connection should be over shared memory - or is the shared memory protocol disabled on this server?

    Friday, January 29, 2016 10:33 PM
  • Just saw your question as I revisit old threads.

    Shared memory protocol is(was) enabled.

    I am not saying that the IP lookup returning the first name from the line is hosts is a bug (this is probably by design).

    I am saying that using an IP lookup call to fill in a string used with an SSPI authentication call is a probably a bug.

    Of cause it might be the SSPI API or the SSPI provider doing the wrong lookup, in which case it is a Windows Server 2008 R2 bug and not a SQL Server 2008 R2 bug.

    Friday, March 11, 2016 5:55 AM