Web app running as NETWORK SERVICE can connect to SQL Server but windows service running as LOCAL SYSTEM cannot

Question

I have installed a .net web application on a Windows Server 2003 IIS server, running in an Application Pool as NETWORK SERVICE and connecting to SQL Server on a different machine using Integrated Security. The SQL Server machine is also running Windows Server 2003. The web app therefore connects as identity DOMAIN\COMPUTER$ and that account has a login & user in SQL Server so everything works well.

I also have installed a .net windows service on the same IIS server that connects to that same SQL Server machine. The windows service runs as LOCAL SYSTEM and should therefore also connect as identity DOMAIN\COMPUTER$. I have installed this same product at over a dozen different companies, normally all works as I'd expect, but in one recent case the windows service was unable to connect to the database, getting the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Any ideas why this would be the case for LOCAL SYSTEM and not for NETWORK SERVICE? To work around this in the short term I had to switch to use a SQL Server login but would prefer to use Integrated Security if there's an easy solution to it. There were no error messages in the Security or System event log, although I'd not done anything to enable additional logging.

Normally I'd assume this is some sort of Kerberos/AD-type problem, and following articles like this and this would help. But the fact it works from NETWORK SERVICE suggests that the normal things I'd check are already ok (e.g. SPNs have been set up correctly and the machine domain account is enabled for delegation?). So what setting is it that's going wrong?

I don't have access to the servers without assistance from my client's IT team and there are other applications installed on the server that I need to be mindful to not disrupt, which both make troubleshooting a bit more delicate. Any suggestions for troubleshooting greatly appreciated!

Solution 1

You may want to confirm if the security connection is NTLM or Kerberos. If it is reverting to NTLM, the connection will be anonymous.

There is a group policy that enables using the computer identity when NTLM is used.

Network security: Allow Local System to use computer identity for NTLM
http://technet.microsoft.com/en-us/library/jj852275%28v=ws.10%29.aspx

---

For more information on how to configure the SPN to facilitate Kerberos authentication for your SQL server:

http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

In particular, note the following:

An SPN for SQL Server is composed of the following elements:

• ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
• Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.

• Port: This is the port number that the service is listening on.

  eg: MSSQLSvc/myserver.corp.mycomany.com:1433

Solution 2

I'm still betting SPN problem. Don't just assume that they're there. Check for proper registration of SPNs for SQL Server. Also check for duplicates (setspn -x).

Network Service works because when the SPN isn't there, it can still fall back to NTLM authentication.

Local System doesn't work because it only access network resources as DOMAIN\Computer$ if it's able to use Kerberos. Otherwise, it falls back to a null session, which is why you see Anonymous Logon.

Comments

[Greg Askew]

That restriction is only for LocalSystem. NetworkService always uses the computer identity.