Finally, and probably most optimally, we have IIS6 which runs asp.net applications in native mode (you know you wanted to update to Windows 2003, here is your excuse). IIS6 allows you to create Application Pools, a context in which you can run one or more applications under. A pool controls performance, recycling of processes and most importantly for trusted connections the security context an application runs under. Again you must create a mirrored username and password on both the IIS server and the SQL server. To create a new pool start the IIS administration tool, right click on Application Pools and choose New Application Pool. Name your pool and click OK. Right click on your new pool name and choose Properties, then select the identity tab. You can now set the pool to run as your mirrored user. You must then change your application setting to use the new pool. Expand out the Web Sites tree, navigate to your application directory, or the web site containing your application, right click, choose Properties and choose the Home Directory tab. You can then change the Application Pool setting to your new pool and your application will run in the context you specified.

Side effects

There are side effects to using trusted connections. If each connection is opened under a different user account then these connections are not pooled between users. This then increases load on both the IIS server and the SQL server. Trusted connections also take more processing power when authenicating the connection than SQL style logins as the login tokens are now validated outside SQL by the NT authenication process. If you are validating against a domain you may see increased load on your domain controllers as well as the SQL server. Performance testing is pretty much mandatory before you role your web site out.

Summary

There is no real "you do it this way" answer to using trusted connections with asp.net, Microsoft's flexibility gives you multiple choices on how to solve your problem. It is up to you to decide the best way for your application.