ASP.Net exposes a handy Windows feature called Impersonation. This lets an application access local resources using the credentials of the current user. Local resources is important – if you want to access some files, then that’s fine, but if you want to access a remote database, you’re going to need Delegation as well.
It’s fairly simple to set this up once you know what needs doing. Getting to that stage wasn’t the most fun I’ve ever had – the MSDN articles on the subject are useful, but they seem to want to cover at least 3 topics at a time, which can be a little confusing.
The scenario was this: an intranet web application would authenticate users using Windows Authentication, granting access based on their Active Directory roles. The application would access the back-end SQL Server database using Integrated Security, with the credentials of the current user. This gives two main advantages – you don’t have to setup or administer a separate account or accounts for the database, and auditing via SQL triggers is easy. However, this approach is only really suited to an intranet scenario, as database connection pools are used on a per-user basis. Too many users and the database will not be happy.
The setup is in four parts.
<system.web> section of your
web.config, add the following:
<authentication mode="Windows"/> <identity impersonate="true"/>
Also, make sure you’re using a connection string that uses Integrated Security.
I’m using IIS7, which has some more detailed settings than IIS6. In the website or virtual directory, ensure that Anonymous Authentication and Forms Authentication are disabled, and ASP.Net Impersonation and Windows Authentication are enabled. IIS should pick this up from
IIS7 lets you specify “providers” for Windows Authentication. You must use the Negotiate:Kerberos provider. Choosing this provider will mean that “Kernel-mode authentication” cannot be used. This is disabled under the Advanced Settings within the Authentication section.
The web server’s Active Directory properties must be edited to enable Delegation of credentials onto the SQL server. There’s a great explanation with a screen grab of the Active Directory property page in a post from Ken Schaefer.
Windows Server 2003 domains and later support Constrained Delegation, which can constrain the delegation to a specific service on the target machine. This is obviously more secure. To delegate to SQL Server, MSSQLSvc is the service you need.
All you need to do at the database level is grant the relevant permissions for the users or roles you want accessing the database.
Now it should all magically work!
UPDATE: That might not be all! See Part 2.