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

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.