Which user is used in trusted connection to SQL Server - IIS/ASP.NET

A bit of background first. I have an ASP.NET application which lives in the DMZ on our network. It needs to access SQL Server instances on machines which live within our trusted network. I will call the web server WebServer1 and an example SQL Server SqlServer1.

Currently, our web server is using connection strings which contain a user ID and password. This is not ideal, and we'd like to use Windows authentication to connect to the DB instead.

To do this, I set up a user account & password on our domain, I'll call it MyDomain\WebApp. I set the identity for our site's application pool to this new account, and set the anonymous authentication for the site to use the application pool's identity.

However, when I try to change our connection strings, replacing the UserID=fakeuser, Password=fakepass with Trusted_Connection=Yes, initiating a database call from the web application results in an error message

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Edit: here is the full error message from the SQL Server error log:

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: ]

Error: 18452, Severity: 14, State: 1.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: ]

To see what was going on, I thought I'd set up a simple page that would tell me what the current user is on the worker process. This is where I ran into very strange results. Here are the results and their server variables that gave them (with real names replaced with names mentioned above)

1. WebServer1\WebApp = WindowsIdentity.GetCurrent().Name
2. WebApp = Environment.UserName
3. MyDomain\WebApp = pool.ProcessModel.UserName
4. WebServer1\WebApp = Request.LogonUserIdentity.Name

1 and 4 are very strange, and do not exist as user accounts on the web server. Only #3 is returning the correct value seen in the IIS application pool for the site. This was gotten using the method in the comment on the following SO answer: https://stackoverflow.com/a/10101417/711674.

My assumption is that is not the account that is actually being used when the server is attempting to authenticate with the SQL Server instance, and one of the other values is being used, resulting in the error message mentioned above.

There is a one good article about that matter here. In few words:

You need to answer on two main questions:

1. What was the authentication mode for IIS? (Anonymous authentication? Windows authentication? Both?)

2. Was impersonation for the FastCGI module on or off? (i.e. Was the fastcgi.impersonate setting in my php.ini file set to 0 or 1?)

A. IIS Anonymous Authentication enabled and fastcgi.impersonate = 1:

Because I was connecting to IIS anonymously, the built-in anonymous account (which is NT AUTHORITY\IUSERby default in IIS 7.0+) was impersonated. So, my connection attempt failed because this identity does not map to a valid login on my server.

B. IIS Anonymous Authentication enabled and fastcgi.impersonate = 0:

Because impersonation was off, my identity was not used as the identity of the PHP process. Instead, the actual identity of the PHP process was used in the connection attempt. In IIS 7.5, the identity of the PHP process depends on the identity of the application pool it is running in. In this case, PHP was in the default application pool and the identity used in the connection attempt was IIS APPPOOL\DefaultAppPool (so my connection attempt failed).

C. IIS Windows Authentication enabled and fastcgi.impersonate = 1:

With Windows authentication enabled (and Anonymous Authentication disabled), I connected to IIS with the identity that my Web browser was running under (Microsoft\brian.swan, the identity I logged in with). And, with impersonation on, the PHP process ran under the Microsoft\brian.swan identity. So, since that identity mapped to a valid login on my server, my connection attempt succeeded.

D. IIS Windows Authentication enabled and fastcgi.impersonate = 0:

The results here were the same as with Anonymous authentication enabled and fastcgi.impersonate =0 (the connection attempt failed). The only difference occurred when I requested the page from the Web server: a pop-up window asked for my identity when I requested the page.

E. Both Anonymous and Windows Authentication enabled:

Web browsers will try to access a Web server by using anonymous authentication first. So, if both Anonymous and Windows Authentication are both enabled, the results will be the same as those above where Anonymous Authentication is enabled (A and B).