Ways to connect my asp.net application on my web server to a sql database on another server?

Question

Best practice says to keep my web app and database on separate machines, but when doing that it seems I can't get a clear explanation on how best to make this connection. Whil windows auth is the recommended authentication, I don't see how to make a trusted connection . Must I use impersonation to have my application connect to a db server?

Are most people simply using SQL authentication in this case? Both machines are on a virtual cloud network.

Answer 1

If both computers are in the same domain, I'd use Windows Authentication for the SQL connection. To set it up:

1. Create a domain account to use for the app.
2. Give the id the absolute minimum priveleges necessary to host the site on the web server. For example, it must have read access to the web site itself, write access only to folders updated by the web site, etc.
3. Change IIS so that the domain account is used to run the app. In IIS6 & IIS7, you do this through the application pool. In IIS5, you have to change the settings in the machine.config or in the web.config for the ProcessModel.

All calls to the database will be done through this domain account; you won't have to setup impersonation. In fact, if you are using SQL authentication today, the only change you need to make is to the database connection string; no code changes are needed.