Connecting to Sql Server using Impersonation from Asp.Net

So you have an Asp.Net application that needs to authenticate its users to Active Directory, and you also want to use their credentials for connecting to a database server. It's pretty logical thing to do in an enterprise environment where you would normally control all your user privileges using Active Directory. This is especially nice since you also don't have to put sensitive credentials in your web.config file.

Before I begin, here are a few links worth mentioning:

How To: Use Impersonation and Delegation in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998351.aspx

How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998355.aspx

How To: Use Windows Authentication in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998358.aspx

This turns out to be a very easy thing to do; it's just hard to find any simple information about it. Authenticating to Asp.Net using Windows and using impersonation gives us the ability to let the worker process inherit privileges of the authenticated user. All it takes is a few web.config changes.

First, you need to change authentication mode to Windows. under <system.web> set <authentication mode="Windows"/>

It's also a good idea to force anonymous users to authentication. Do that by changing the <authorization> config.

Now we have users authenticating to the Windows network. Now lets enable impersonation so the worker process will adopt privileges for their requests. Set <identity impersonation="true"/>

When you’re done, your web.config file should resemble this.

<system.web>

    <authentication mode="Windows"/>

    <identity impersonate="true"/>

    <authorization>
        <deny users="?"/>
    </authorization>

</system.web>

All that’s left is putting the proper delegation rules in place for your web server to hand out Windows tokens (at least that’s my understanding). In Active Directory Users and Computers from a domain controller or accessible machine, right click and go to the properties of the web server that will be using impersonation.  Click the Delegation tab and select the third option. Trust this computer for delegation to specified services only.  Select Use any authentication protocol.

imageThen browse for a machine account for the database server you want to access with the impersonated accounts. Choose the MSSQLSvc with the port number next to it. (Ours had two services).  Then click OK to apply the changes.

You may need to reset IIS or wait a few minutes for the AD changes to propagate. We reset ours from a command line with “iisreset”.

That should be it!  You should now be able to browse your web app and access the database using your AD Windows Account.