Impersonation

The next hurdle to solve is to connect to the database with the correct user.

Without doing anything, your connection will be made by the application pool account - in the described scenario that would be the mydomain\hrwebact account.

That was not what you wanted. The business requirement were that "all authorization should be managed on the SQL Server" , and that would require that the connection to SQL Server is made with the end-user credentials.

To fix this, you need to ensure that the application is impersonating the end-user when the connection to the SQL Server is made.

If you search for "ASP.NET" and "impersonation" in your favorite search engine, then it is likely that you would find guidance on how to enable ASP.NET impersonation in this way:

<system.web>
  <authentication mode="Windows"/>
  <identity impersonate="true"/>
</system.web>

Before going into explaining why this is not the correct answere, just be warned that this will not work in IIS 7.0 or IIS 7.5 where you you will get a HTTP Error 500.24:

ModuleName ConfigurationValidationModule
Notification 1
HttpStatus 500
HttpReason Internal Server Error
HttpSubStatus 24
ErrorCode 2147942450
ConfigExceptionInfo 
Notification BEGIN_REQUEST
ErrorCode The request is not supported. (0x80070032)

You can solve this in two ways, either by adding the following parameter to the web.config:

<system.webServer>
   <validation validateIntegratedModeConfiguration="false"/>
</system.weSserver>

or by changing the application pool to use the "Clasic" Managed Pipeline Mode.

Howerver, the answer is still not correct. ASP.NET impersonation will only allow your application to access ressources locally on the web server in the context of the end-user. This could be a local instance of a SQL Server or it could be the file system. If the ressource is located on a different server - and we must assume that this is the case, otherwise we had no reason to enable delegation - then the database connection will still be made in the context of the application pool account.

We need to do something else. That is the topic for the next blog post.