ASP.NET Windows Authentication Impersonate Problem

Question

Hello,

In my previous questions I was asking how to use windows authentication within my application. That is now working, users can login with there account but I have one database access scenario I can't find anything on.

Basically I will have several servers and here is the problem.

On some servers they will have Windows Authentication accounts for the SQL Server database server, so using impersonate their credentials should be used. But I notice its a global setting in the web.config (not per connection) and it one case I want to use the applications (IIS or ASP) Windows Authentication account rather than the users. (Access to my configuration database)

How could I achieve this?

Web Application is ASP.NET MVC hosted on Server 2003/2008 IIS 6/7/7.5 with clients being Windows XP and above. Using SQL Server Express/Standard 2005/2008 mixed.

Answer 1

Use domain controller - so it could propagate same credentials for single user across entire domain.

The second trick for workgroups - create same account of impersonation (with exactly same login and password) on both servers (ASP and SQLServer). Don't forget to grant this permission on SQLServer.

Answer 2

Impersonation is on a site wide basis, or you can manually turn it on. What you can't do is manually turn it off I'm afraid, nor can it be done via the connection strings.

So basically turn impersonation off, then wrap the database calls when impersonation is needed like so:

using System.Security.Principal;

WindowsIdentity winId = (WindowsIdentity)HttpContext.Current.User.Identity;
WindowsImpersonationContext ctx = null;
try
{
    ctx = winId.Impersonate();
    // Do your thing
}
catch
{
}
finally
{
    if (ctx != null)
        ctx.Undo();
}

The MSDN P&P guide to asp.net impersonation has more.

Answer 3

You'll have to set up delegation on your network so that the ASP.NET servers can impersonate users on the Sql server machines. This assumes your servers are on an Active Directory controlled network (not workgroups) and that the sql servers are on different machines than your web servers.

You would configure delegation for those database server machines where you want the users to be impersonated and don't configure it for those server machines that you want the ASP.NET worker process account to be the account accessing the server.

If you can't do this, you can turn off windows/mixed authentication on the Sql Server instances you wish to prevent delegation on, and then manually configure the Sql Server account to connect with in the connection string within web.config.