IIS and SQL Server ASP.Net C# Entity Framework Windows authentication

Question

I'm currently working on an ASP.Net / C# / Entity Framework project using Windows Authentication.

I would like my users to reach both IIS Server and SQL Server with their own Active Directory account, allowing administrators to see who is connected to the database.

But I have an issue: while connecting to the database, the program uses the application pool's account set up in the IIS Server, but not his user account.

This is my web.config file :

<configuration>
    <system.web>
        <compilation debug="true" strict="false" explicit="true" targetFramework="4.5.2"/>
        <httpRuntime targetFramework="4.5.2"/>
        <customErrors mode="Off"/>
        <authentication mode="Windows"/>
        <identity impersonate="true"/>
    </system.web>
    <connectionStrings>
        <add name="BOMBuilderEntities"
         connectionString="metadata=
         res://*/Model.BOMBuilderModel.csdl|
         res://*/Model.BOMBuilderModel.ssdl|
         res://*/Model.BOMBuilderModel.msl;
         provider=System.Data.SqlClient;
         provider connection string=
             'data source=FRSDSQ01;
              initial catalog=BOMBuilder;
              integrated security=SSPI;
              MultipleActiveResultSets=True;
              App=EntityFramework'" 
              providerName="System.Data.EntityClient"/>
    </connectionStrings>
    <system.webServer>
        <defaultDocument>
            <files>
                <add value="multipages.aspx" />
            </files>
        </defaultDocument>
    </system.webServer>
</configuration>

Also, I tried to modify this ApplicationPoolIdentity setting to see if it was this connection used for SQL Server authentication and it is. If I set a domain account, this account will be listed in the activity monitor. So how can I set this setting in order to use the user account logged on my asp.net application?

EDIT 1: I've added the impersonation setting.

Answer 1

This may be a case of Double-Hopping. This is when the IIS server and the SQL Server are on different machines, it is a security measure that refuses to continue to provide the credentials to servers further down the chain unless proper delegation is in place.

A quote from the second article:

Apparently, you can get around the problem and use proper delegation if you set up your network to use Kerberos and set up the web server in question as trusted for delegation.

Further information can be found in the following articles: http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx http://blogs.msdn.com/b/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx

Answer 2

In case someone else faces this problem :
- With a Basic Authentication + Asp.Net Impersonation (check both Web.config file and IIS Server configuration that they are enabled), everything works fine. Users are asked to connect to IIS Server during application loading. Then the Entity Framework connection to SQL Server is created with the user login so we can monitor correctly who is connected.
- With Windows Authentication, I still have an issue that Anonymous LOGON can't log on the SQL server. I can't understand why it's not using user login... If someone has an idea, I'd need it!