Which user is used in trusted connection to SQL Server - IIS/ASP.NET
<p>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 <code>WebServer1</code> and an example SQL Server <code>SqlServer1</code>.</p>

<p>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.</p>

<p>To do this, I set up a user account &amp; password on our domain, I'll call it <code>MyDomain\WebApp</code>. 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.  </p>

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

<blockquote>
  <p>Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.</p>
</blockquote>

<p>Edit: here is the full error message from the SQL Server error log:</p>

<blockquote>
  <p>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: ]</p>
  
  <p>Error: 18452, Severity: 14, State: 1.</p>
  
  <p>Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: ]</p>
</blockquote>

<p>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)</p>

<ol>
<li>WebServer1\WebApp = <code>WindowsIdentity.GetCurrent().Name</code></li>
<li>WebApp = <code>Environment.UserName</code></li>
<li>MyDomain\WebApp = <code>pool.ProcessModel.UserName</code></li>
<li>WebServer1\WebApp = <code>Request.LogonUserIdentity.Name</code></li>
</ol>

<p>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: <a href="https://stackoverflow.com/a/10101417/711674">https://stackoverflow.com/a/10101417/711674</a>.  </p>

<p>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.</p>