Preferred DB Connection for ASP.Net

Question

What's the preferred (best practice) means of connecting an ASP.Net Website to a database? I doubt it's as simple as using Trusted-Connection and giving the NT-Authority accounts access.

What do y'all do? Assuming a clean install of SQL Server (2008), what do you do to configure access to the database for a website?

Answer 1

I usually run ASP.NET app pool as a separate account (not NT AUTHORITY\NETWORK SERVICE) and use Windows authentication to access the SQL Server. This method has the advantage of not storing the password in config files.

Steps:

1. Create a user account to run your ASP.NET application on.
2. Create an application pool in IIS and run it on the created account.
3. Assign NTFS permissions that your application needs to the account.
4. Grant permission to login on SQL Server.
5. Assign the appropriate database roles to the created login.

This will work for many apps. For more complex security environments, you might need more sophisticated strategies.

Answer 2

I used to use trusted connections, but ended up feeling that that sometimes I ended up having to grant too many privileges to the service account used for the connection/app pool. Now I use SQL Server accounts and set up the application to encrypt the connection strings during Application_Start if they aren't already encrypted. In fact I encrypt any section that may contain user credentials. I use an appSetting to determine whether the encryption code runs so I don't encrypt my settings in the development environment.

Answer 3

I also use SQL Server accounts, just find it simpler to do and to troubleshoot.