ASP.NET 1.1 - Internet Web to SQL Server

- J.D. Meier, Alex Mackman, Michael Dunner, Srinath Vasireddy, and Prashant Bansode

Applies To

ASP.NET 1.1

Scenario

In this scenario with two physical tiers, registered users securely log in to the Web-based application using a Web browser. The ASP.NET-based Web application makes secure connections to a Microsoft® SQL Server™ database to manage data retrieval tasks. An example is a portal application that provides news content to registered subscribers.

Characteristics

This scenario has the following characteristics:

Users have a number of different browser types.
Anonymous users can browse the application's unrestricted pages.
Users must register or log on (through an HTML form) before being allowed to view restricted pages.
User credentials are validated against a SQL Server database.
All user input (such as user credentials) that is used in database queries is validated to mitigate the threat of SQL injection attacks.
The front-end Web application is located within a perimeter network (also known as DMZ, demilitarized zone, and screened subnet), with firewalls separating it from the Internet and the internal corporate network (and the SQL Server database).
The application requires strong security, high levels of scalability, and detailed auditing.
The database trusts the application to authenticate users properly (that is, the application makes calls to the database on behalf of the users).
The Web application connects to the database by using the ASP.NET process account.
A single user-defined database role is used within SQL Server for database authorization.

Solution

Solution Summary

Authentication	IIS is configured to allow anonymous access The ASP.NET Web application authenticates users with Forms authentication to acquire credentials. Validation is against a SQL Server database. Users passwords are not stored in the database. Instead password hashes with salt values are stored. The salt mitigates the threat associated with dictionary attacks. Microsoft® Windows® authentication is used to connect to the database using the least privileged Windows account used to run the ASP.NET Web application.
Authorization	The ASP.NET process account is authorized to access system resources on the Web server. Resources are protected with Windows ACLs. Access to the database is authorized using the ASP.NET application identity.
Communications Security	Secure sensitive data sent between the users and the Web application by using SSL. Secure sensitive data sent between the Web server and the database server by using IPSec.

Web Server

IIS

Enable Anonymous access for your Web application's virtual root directory. Note - In IIS 6.0, the Edit button is located within the Authentication and access control group.

ASP.NET

Reset the password of the ASPNET account (used to run ASP.NET) to a known strong password. This allows you to create a duplicate local account (with the same user name and password) on the database server. This is required to allow the ASPNET account to respond to network authentication challenges from the database server when it connects using Windows authentication. Note - If you are running IIS 6.0 on Windows Server 2003, the default ASP.NET Process account is identified on the network as DomainName\WebServerName$. You can use this account to give the ASP.NET process identity access to the database. Therefore this step can be skipped. An alternative here is to use a least privileged domain account (if Windows authentication is permitted through the firewall). Set your custom account user name and password attributes on the <processModel> element. Note - With IIS 6.0 running on Windows Server 2003 in process isolation mode, the processModel setting is not used.

Configure your ASP.NET Web application to use Forms authentication (with SSL). Edit Web.config in your application's virtual directory root. Set the <authentication> element to:

 <authentication mode="Forms" >
  <forms name="MyAppFormsAuth" 
         loginUrl="login.aspx" 
         protection="All"
         timeout="20" 
         path="/" >
  </forms>
 </authentication>

Database Server

Create a Windows account on your SQL Server computer that matches the ASP.NET process account The user name and password must match your custom ASP.NET application account or must be ASPNET if you are using the default account. Note - If you are running IIS 6.0 on Windows Server 2003, the default ASP.NET Process account is identified on the network as DomainName\WebServerName$. You can use this account to give the ASP.NET process identity access to the database. Therefore this step can be skipped.
Configure SQL Server for Windows authentication.
Create a SQL Server Login for newly created account. Note - If you are running IIS 6.0 on Windows Server 2003, create a SQL Server login for the ASP.NET process identity directly by using the DomainName\WebServerName$ identifier.
Create a new database user and map the login name to the database user.
Create a new user-defined database role within the database and place the database user into the role.
Establish database permissions for the database role Grant minimum permissions.

Communications Security

Configure the Web site for SSL.
Configure IPSec between application server and database server.

Analysis

Forms authentication is ideal in this scenario because the users do not have Windows accounts. The Forms login page is used to acquire user credentials. Credential validation must be performed by application code. Any data store can be used. A SQL Server database is the most common solution, although Active Directory provides an alternate credential store.
With Forms authentication, you must protect the initial logon credentials with SSL. The Forms authentication ticket (passed as a cookie on subsequent Web requests from the authenticated client) must also be protected. You could use SSL for all secured pages in order to protect the ticket. You can also encrypt the Forms authentication ticket by configuring the protection attribute of the <forms> element (to All or Encrypt), and by using the Encrypt method of the FormsAuthentication class to encrypt the ticket.

The protection="All" attribute specifies that when the application calls FormsAuthentication.Encrypt, the ticket should be validated (integrity checked) and encrypted. Call this method when you create the authentication ticket, typically within the application's Login button event handler.
```
 string encryptedTicket = FormsAuthentication.Encrypt(authTicket);
```
Note - If you are running ASP.NET 2.0, it is recommended that you use the Membership feature which simplifies Forms Authentication implementation.
ASP.NET runs as the least privileged local account, so potential damage from compromise is mitigated.
URL authorization on the Web server allows unauthenticated users to browse unrestricted Web pages and forces authentication for restricted pages.
Because impersonation is not enabled, any local or remote resource access performed by the Web-based application is performed using the ASPNET process identity's security context. Windows ACLs on secure resources should be set accordingly.
User credentials are validated against a custom SQL Server database. Password hashes (with salt) are stored within the database. For more information, see Authenticating Users against a Database in Chapter 12, "Data Access Security."
By using Windows authentication to SQL Server, you avoid storing credentials in files on the Web server and also passing them over the network.
If your application currently uses SQL authentication, you must securely store the database connection string as it contains user names and passwords. Consider using DPAPI.
The use of a duplicated Windows account on the database server (one that matches the ASP.NET process account) results in increased administration. If a password is changed on one computer, it must be synchronized and updated on all computers. In some scenarios, you may be able to use a least-privileged domain account for easier administration.
IPSec between the Web server and database server ensures the privacy of the data sent to and from the database.
SSL between browser and Web server protects credentials and any other security sensitive data such as credit card numbers.
If you use a Web farm, ensure that the encryption and validation keys —for example, those used to encrypt and validate the Forms authentication ticket (and specified by the <machineKey> element in Machine.config) —are consistent across all servers in the farm.

Pitfalls

The application must flow the original caller's identity to the database to support auditing requirements. Caller identity may be passed using stored procedure parameters.

Related Scenarios

Forms authentication against Active Directory

The user credentials that are accepted from the Forms login page can be authenticated against various stores. Active Directory is an alternate to using a SQL Server database.

.NET roles for authorization

The preceding scenario doesn't take into consideration the different types of users accessing the application. For example, a portal server could have different subscription levels such as Standard, Premier, and Enterprise.

If role information is maintained in the user store (SQL Server database), the application can create a GenericPrincipal object in which role and identity information can be stored. After the GenericPrincipal is created and added to the Web request context (using HttpContext.User), you can add programmatic role checks to method code or you can decorate methods and pages with PrincipalPermission attributes to demand role membership.

Note - For ASP.NET 2.0, you can use the Role Manager feature to check the role membership using Roles APIs.

Using a domain anonymous account at the Web server

In this scenario variation, the default anonymous Internet user account (a local account called IUSR_MACHINE) is replaced by a domain account. The domain account is configured with the minimum privileges necessary to run the application (you can start with no privilege and incrementally add privileges). If you have multiple Web-based applications, you can use different domain accounts (one for each Web-based application or virtual directory).

In order to flow the security context of the anonymous domain account from IIS to ASP.NET, turn on impersonation for the Web-based application by using the following web.config file setting:

<identity impersonate="true" />

If the Web-based application communicates with a remote resource such as a database, the domain account must be granted the necessary permissions to the resource. For example, if the application accesses a remote file system, ACLs must be configured appropriately to give (at minimum) read access to the domain account. If the application accesses a SQL Server database, the domain account must be mapped using a SQL login to a database login.

As the security context that flows through the application is that of the anonymous account, the original caller's identity (captured through Forms authentication) must be passed at the application level from tier to tier; for example, through method and stored procedure parameters.