How to Impersonate the logged in user on SQL server from a Web App.

Recently , We have developed an OData service which is On premise and using Windows authentication

When I have tested my web application using IIS Express, it worked fine for me. my web app able to connect to the SQL server using my windows credentials.

But When I have deployed my solution on IIS, then the Web App is failing with the error that is saying that Domain\MachineName failed to connect to the SQL server.

I tried to Dig into the Solution.

First I have found that When your application pool runs under ApplicationPoolIdentity, it will try to authenticate the remote system (Domain\Machine) against the SQL server and fails.

I have enabled Windows Authentication and Asp.Net Impersonation on the Web Application and changed the Application Pool Identity of my web application to Local System. Then it is working fine.

These are the changes you have to make to your web.config

<system.web>

    <authentication mode="Windows"  ></authentication>

    <identity impersonate="true"/>

  </system.web>
And you need to enable Windows Authentication and Asp.Net Impersonation on your Web application On IIS



You might see the below exception, once you have changed the web.config

HTTP Error 500.24 - Internal Server Error

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

Most likely causes:

• system.web/identity@impersonate is set to true.

then add the below line of code which is in bold to your web config

<system.web>

    <compilation debug="true" targetFramework="4.5" />

    <httpRuntime targetFramework="4.5" />

    <authentication mode="Windows"  ></authentication>

    <identity impersonate="true"/>

  </system.web>

  <system.webServer>

    <validation validateIntegratedModeConfiguration="false" />

 </system.webServer>

Question to the readers: Why does it worked for me when I am using IIS express.