SQL Trusted Connections with ASP.NET

First let's cover impersonation. Impersonation allows you to tell asp.net to run as a particular user. Create a matching username and password on both machines, making sure that you use a strong password. On the IIS server the new user name must have appropriate permissions to run as an asp.net user, detailed in MSDN. On the SQL server it must have access to the database, stored procedures, views and, if you really must, tables, appropriate to your application.

You must now configure asp.net to run as this user. There are two methods of doing this, you can either hard code the password into web.config (running the risk of password "leakage" should your web.config file become exposed) or by using the IIS Administration tool, in conjunction with an edit to your web.config.

To hard code your new context edit web.config and change the authentication mode settings in system.web to something like

<system.web>
  <authentication>
    ...
    <identity impersonate="true"
      userName="yourNewUsername"
      password="yourStrongPassword" 
    />
  ...
  </authentication>
</system.web>

This, of course, defeats the object of not hard coding passwords in your application.

If you don't want to hard code the password (and you shouldn't) you can leave out the username and password from web.config and instead configure IIS to provide these details. Edit your web.config so it looks something like

<system.web>
  ...
  <authentication mode = "windows" />
  ...
  <identity impersonate="true" />
  ...
</system.web>

Now open up the IIS Administration tool on the web server and right click on the directory your application is running in. Open the Properties window, choose the Directory Security tab click the edit button beside Anonymous access and authentication control and clear the check box beside Allow IIS to Control Password. You can then change the context for this application and enter your new username and password.