I just had an email exchange with the famous Kimberly Tripp (who is rumored to be blogging soon, look out!) about best practices for setting up security architecture for distributed Web applications and services. My eyebrows were raised at some demo code I saw that used a hard-coded account for ASP.NET to impersonate:

<identity impersonate=true userName=dbaccess password=whatever />
The names of the accounts have been hidden to protect the source of the demo ;)

I know, it's only a demo, but people use this stuff so I gave some thought to what I would do, and checked in with my pal Kimberly to see what she would do on the SQL side.

First of all, the ASP.NET worker process runs under the identity as configured in the <processModel> setting by default. (BTW this is a low privilege account that should probably be renamed since all potential attackers will know the default account name for default ASP.NET system deployments.) This account has access to the temporary ASP.NET file folder, so if you use impersonation, you may be required to give impersonated accounts access to the same folder, depending on what resources the Web application accesses. IMHO, database access accounts have no business being related to the ASP.NET worker process account, in terms of permissions, in general. Besides, there is likely to be a separate application server that handles all business logic including database access. Also, attack prevention is about adding layers of protection, and isolating resource ownership, so it makes more sense to let the ASP.NET worker process run under its account, and create appropriate accounts for database access (admin, readonly, readwrite, writeonly) that are configured in SQL server with appropriate permissions. Another further consideration is scalability with impersonation when invoking database resources. Connection pooling is isolated by security context.

So, if we don't want to impersonate, how do we access the database resource through a Windows account? Also, should we use Windows accounts? Kimberly says: In general the best way to protect access is to allow Windows authentication only, making sure that the sa account has a strong password (regardless of it being disabled). Also be sure to keep the sa account as a back door in the event that Admin accounts are removed from the Windows ACL list (inadvertently, but possible) so that you still have access to DB resources.

So, your Windows ACLs will include perhaps a few limited privilege accounts that are specifically configured in SQL server for read and/or write access to specific tables. These accounts can also be used as the identity under which various components in your DALC layer (probably serviced components) run, thus they are able to propogate credentials to SQL when invoking ADO.NET calls. If the serviced component (living on the application server tier) is securely invoked by the ASP.NET application front end (living on the Web server tier) because the application has verified the user's credentials (custom role-based security for typical non-Intranet application) and verified they (or the application) has the right privilege to invoke the DALC component...we are good to go.

Of course, every application deserves its own architecture review, so the real point of this short note is:

• Impersonation adds configuration overhead,
• Design for distribution of your Web application components, thus expect to require a secure invocation across tiers (Enterprise Services, registered components),
• Have a trusted component run with appropriate identity to talk to the database, so that credentials are propogated through integrated Windows security between components and SQL Server. One way to do this is to use a registered COM+ component (Enterprise Services) as the DALC layer,
• Configure limited access Windows accounts for this communication to reduce the affect of any potential hacker exploits (if they can even get this far down the pipeline, past your DMZ to the application tier presumably)
• Limit the number of Windows accounts configured in SQL to control access to resources, to optimize connection pooling

Nuff said.