SQL Server and windows authentication in IIS7
Impersonation on/off makes all the difference, when properly configured. What you want is caled 'constrained delegation' and you need to configure IIS and ASP for it:
By default, the IIS server is not allowed to impersonate you towards the SQL Server. There's an MSDN article on how to configure it. The configuration process is complex and error prone.
If your production ISS and SQL Server run on different servers, you'll need a domain admin to configure impersonation trust between the two servers. This is typically a no-go in a big organization.
In addition to not being deployed by normal admins, impersonation also prevents users from sharing their connections in the SQL connection pool. This results in a very noticeable performance penalty for even small (5+ users) websites.
Did you try to create a new user account for your application, grant it the appropriate rights on the Sql Server, and then set the application pool to run under this new account?This is what I usually do, and it works. I'm not running my application under NetworkService, LocalSystem or other builtin accounts.