After an extensive search for ASP-based help desk applications with a SQL backend, my company (about 200 users) decided on JitBit for the following reasons (SpiceWorks was a close second but the interface was a little more complex than we had desired):
- Web-based interface that's clean and easy to use
- Supports Active Directory integration
- Supports email ticket submissions
- Email notifications to users and techs
- Allows file attachments to tickets
- Supports asset tracking
- Generates reports
- Includes knowledgebase and newsfeed features for user self service and announcements
- Most fields are customizable (status, priority, categories, etc)
- Private messaging for techs
- Inexpensive ($349 for software, $699 for software + source code), plus a hosting option
- Phenomenal support and regular software updates based on customer feedback
http://www.jitbit.com/helpdesk.aspx