I recently recieved this question from a SearchWebServices.com reader:

I am designing a sample app that has 3 tiers - Web browser, .NET application & DB server. I believe (correct me if I am wrong) that given that each individual user will not be connecting to SQL server directly (except maybe for DB Admin's etc) it is desirable to create a login for IIS to connect to the server and a user login to connect to the DB in question with the appropriate permissions. The .NET application will be connecting to the DB using ADO.NET. Is this true, or am I barking up the wrong tree?

Interestingly this is a subject I have been writing about recently. Here's the answer I posted for this question.

Let’s first clarify the physical tiers you describe here. The Web browser on the client tier, but really doesn’t participate in the description of tiers for the server-side application. Users will provide credentials through the browser that must ultimately be authenticated by IIS or passed through to ASP.NET for custom authentication. The .NET application I presume is hosted on the Web server physical tier, along with IIS. The database server physical tier hosting…well…the database application. potentially The server-side then has two physical tiers. If this is an intranet-based application, the Web site is likely configured for Windows authentication in IIS, which means IIS will authenticate the user within the Windows domain. Authorized requests will be forwarded to the ASP.NET runtime for processing, and if the application is configured to impersonate the authenticated user, application code will be governed by what the impersonated account is authorized to do:

<identity impersonate=”true” />

For example, if the logged in user is authorized to access the database (which really means, whichever database objects the account is granted access to, and for whatever type of access like db_datareader, dbdatawriter) then functionality to access the database will execute without exception. But this is not realistic as you mention. That means the code that tries to access the database must first impersonate an account that is granted appropriate access to the database objects. If the intranet application impersonates the logged in user, then this impersonation must be handled on the fly, and must be reverted so that the logged in user is once again the identity under which the remainder of the request thread executes.

If the application does not impersonate the logged in user, ASP.NET application requests will be executed with the ASP.NET identity configured in the <processModel> section of the machine.config. This is usually the NETWORKSERVICE account, which has limited privileges (by design). In theory you could have the application impersonate a higher privilege account for all requests that also has access to the appropriate database objects. BUT - DO NOT DO THIS. This is the lazy man’s solution to gaining access to protected resources, and it seriously compromises the safety of the application. If a hacker were to gain access to an executing thread inside the worker process, they will have access whatever privileges have been granted that thread. By default, we prefer this to be the NETWORKSERVICE account, or the account of the logged in user for intranet applications.

So, the solution?

• Either impersonate the logged in user or run the application under the NETWORKSERVICE account
• For calls to the database, either impersonate a privileged account at runtime, or use EnterpriseServices to invoke a serviced component that runs with the required account with database privileges (better). This decouples the configuration of the required account to access the database from the code, allowing it to be modified as needed through serviced component configuration (COM+). This also has the benefit that later you could distribute the database access component to another tier for scalability and security requirements.

What accounts do you need?

• It is useful to have an account that can only read the database (db_datareader privileges to appropriate objects), and another that can read and write (db_datareader and db_datawriter privileges). This way, during read operations you are not vulnerable to write attacks.

For more information on this subject, see my article on The Server Side.NET referenced here in my blog: http://www.dasblonde.net/PermaLink.aspx?guid=aa616d20-1089-4a24-8f0c-14326f2a731c