Linq to SQL in ASP.Net MVC Runs as Network Service when using Impersonation

Question

I have an ASP.Net MVC controller action that instantiates a DataContext object and I am currently passing the connection string directly into the constructor. I am using Impersonation and I have verified the current user in the controller action is the current Windows Auth. user of the web app, however when running a SQL Trace the query always runs as Network Service. The data context object is referenced in another project from the web application but I am passing the connection string directly into the constructor so this should not be an issue. Here is what is currently in the controller action:

        // verified the user is the current Windows Auth. user of the web app
        var user = this.User;

        var connectionString = "Data Source=serverName;Initial Catalog=dbName;Integrated Security=true";
        var context = new CustomDataContext(connectionString);
        var test = context.Customers.Select(i => i.fullname).ToList();

Everything is getting to the database fine except for the fact that the query always runs as Network Service instead of the current user. Any ideas on why this is the case and how to resolve?

Answer 1

You are most likely seeing Network Service because that is the default account that Application Pools run under in IIS 6 and 7.

In general there are two ways to impersonate in asp.net applications:

1. The < identity /> tag in web.config
2. Change the identity of the application pool in IIS

Both of these options are global and will affect every request.

However, it sounds like you want to impersonate the windows auth user for each logged in user. If so, see This MS Knowledgebase article for sample code - Specifically the section titled Impersonate a Specific User in Code

Answer 2

So what you're seeing is a delegation problem. Identity doesn't flow automatically outside of IIS.

If you've configured everything for Windows authentication then you need to wrap your database open calls to use the Windows Identity temporarily for contexts outside your web application. The code looks as follows

using (((WindowsIdentity)HttpContext.Current.User.Identity).Impersonate())
{
    // Perform database or network access here
}

You'll probably want to wrap this in a check which looks at the current identity and makes sure it's a Windows identity