Best method to connect IIS 7.5 Web Forms to SQL Server

Question

I'm upgrading an ASP.NET 4.0 app from:

Windows Server 2003 and IIS 6

to:

Windows Server 2008 and IIS 7.5

This app is based on ASP.NET Web Forms and not MVC. I currently use SQL authentication, but I would like to follow best practices in the new environment.

Both the IIS 7.5 machine and the SQL Server 2008 machine will reside in a DMZ with its own domain controller. It would be nice if we could use similar connection strings for Dev, Test and Prod environments. What's the best practice for this situation? I've read about three options.

1. ApplicationPoolIdentity
2. Create your own service account on the domain
3. SQL authentication

Here are links to questions that discussed related issues, but nothing seemed to answer my specific question.

User ASP.NET Runs Under

Assign Permissions to ApplicationPoolIdentity Account

Answer 1

I recommend AD account for running the app pool. Then, permissions can be created at SQL server for that same account. The conn string used by the app will then not have to contain account info at all (trusted connection), and you will have one less thing to worry about related to security. As additional precaution, remove that AD account from all user groups, and don't use it for anything else but for this one thing (the app pool). Give that user read access to website files, and write access only to folders that it needs to write to (e.g. to dump log files).

Answer 2

As far as best practices is concerned, I don't think any of the 3 options you listed is better over the other one; all of them can do the job securely and efficiently if used correctly. Your decision should be based on which of those offers advantages to you considering your particular environment, company policies, etc., but again, none of them are bad practice.