I'm curious to how many people are simply adding ASPNET to their logins in SQL Server instead of using impersonation. Most books I've read simply don't mention it. My 2310 course (Developing an ASP.NET Web App) only faintly mentions it as an option in configuring your app.
I've been preaching that you should be doing impersonation, mainly on the basis that if you have two web applications running on the same machine they both may have drastically different security requirements and you can easily give your "What's for Lunch Today?" app way more authorization than it needs because it is running on the same account as the "Patient History Application." And I think we all know the "What's for Lunch Today?" app which we pawned off on the interns will not be written using the greatest of security guidelines.
But now with Windows 2003 Server, apps will no longer try to use the ASPNET when using SSPI, it will be using the Network Service account. Eeek. And the suggestions from people (including MVPs) out there haven't looked so great either. I've seen two suggestions so far:
1. Run SQL Server in mixed mode. Specify username and password hardcoded.
2. Add the IIS_WPG group to your SQL Server login.
Neither of these are good answers. Running SQL Server in mixed mode definitely lowers the security of your applications. Not only will you have to specifiy your username/password someplace in your application, probably in plain text, but many attacks on SQL Server focus on slamming into your database using username/password combos. Now hopefully your server is behind a firewall, but as we saw the slammer worm that might not be the case. Heck, firewalls may even be illegal in your state soon. Just kidding, maybe.
As for the second option, that's just as nasty as adding ASPNET to your logins I think. Opening up the same can of worms that we saw in the lunch application situation. Plus it is just plain icky.
Create a windows username/password for each of your applications. Give them the bare minimum of security needed to work. Use the impersonate option, and use Windows authentication for SQL Server. This will make your current apps more secure, and easier to move to Windows 2003 Server when the time comes.
Oh, and if you are the person out there hard coding SA w/ a blank password into your applications, I can get you a heck of a deal on some SQL Server training. I ran into a trainer last year who was hired as a consultant to secure a companies database servers, and the first thing he noticed was the sa account had a blank password. Better fix that. Within 15 seconds the tech support lights lit up. Every app this company had was written with the sa account hard coded. Not only that, but they had hardcoded the connection string each and everytime they connected to the database, and some apps the source code wasn't even available. His eye actually exploded.
Edit: Now with a headline!
Edit Two!: KB article INFO: Implementing Impersonation in an ASP.NET Application