IIS with a Web Application using Windows Authentication with Impersonation

Question

Im not using this, but is a interesting question. If i set a Web Application on IIS to use Windows Authentication and Impersonate the Authenticated User and my ConnectionString to a SQLServer database use Integrated Security=true;, my application when try to connect to the database will use the User authenticated by the application?

If yes, this is a good thing? ( The database has LDAP/AD "Domain Users" permissions. Considering an Database that will be auditable where each user will have your actions logged.)

Answer 1

I believe E.K.'s answer is true only under a situation in which kerberos authentication is used. What you are describing is known as the 'double-hop'. Essentially, if the user authenticates to 'Server-A', the code that is running on 'Server-A' cannot turn around and use those credentials to access other network resources, such as a SQL Server on 'Server-B'. This is detailed quite a bit but here is a direct link: Blog Article

If you are running on a kerberos architected network, then you can mark a server as being a trusted delegation server. But for most people, this is not the case. If you'd like to learn more about how to do this, see this link.

Answer 2

Yes, the connections to the SQL Server will be under those users.

In general, it isn't good. But it depends on the situation. The following are main factors to consider :

1. Each user will require its own connection. Connections from different users can't be reused even if connection pooling is used. Creating of connection is relatively expensive operation. And each connection requires a little bit of memory
2. Each user needs to have its log in (or at least Windows group that the user is member of needs to have the log in). This can be additional maintenance to create log ins, etc. On the other hand, each such log in can be secured in a different way. Important to say is that securing objects for different users can be achieved also if a single account is used to connect to the SQL Server

Answer 3

Yes, it would use authenticated user and impersonate the "authenticated user" rights to access the database. For more information do look on this link How To: Use Impersonation and Delegation in ASP.NET 2.0

Answer 4

The other approach is to use a service account a non-interactive windows domain account that has complete access to the database.

This allows connection pooling Eliminates complex permission models to allow selected users to perform a delete on a set of data for example.

This does mean however that audit logic has to be added to the data layer of the application and to stored procedures that access the database to insure that the calling user is logged as part of the database access otherwise the service account would be the only account in the audit table.