View source for ASP.NET 1.1 - Intranet Web to Database

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


== Applies To ==
* ASP.NET 1.1


== Scenario ==
In this scenario, a human resources database serves per-user data securely on a homogenous intranet.

[[image:IntranetWebToDatabase.gif]]


== Key Characteristics ==
This scenario has the following characteristics: 
* Clients have Internet Explorer. 
* User accounts are in Microsoft Active Directory® directory service. 
* The application provides sensitive, per-user data. 
* Only authenticated clients should access the application. 
* The database trusts the application to authenticate users properly (that is, the application makes calls to the database on behalf of the users). 
* Microsoft SQL Server is using a single database user role for authorization.


== Solution ==
The application uses a trusted subsystem model and executes calls on behalf of the original callers. The application authenticates callers by using Integrated Windows authentication and makes calls to the database using the ASP.NET process identity. Due to the sensitive nature of the data, SSL is used between the Web server and clients.
[[image:IntranetWebToDatabaseSolution.gif]]

== Web Server ==
IIS
* Disable Anonymous access for your Web application's virtual root directory
* Enable Integrated Windows Authentication


ASP.NET
* Change the ASPNET password to a known strong password value.  ASPNET is a least privileged local account used by default to run ASP.NET Web applications.  Set the ASPNET account's password to a known value by using Local Users and Groups.  Edit Machine.config located in %windir%\Microsoft.NET\Framework\ v1.0.3705\CONFIG and reconfigure the password attribute on the <processModel> element.  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 your ASP.NET Web application to use Windows authentication Edit Web.config in your application's virtual directory root.  Set the <authentication> element to:
 <authentication mode="Windows" />
* Make sure impersonation is off  Impersonation is off by default; however, double check to ensure that it's turned off in Web.config, as follows:
 <identity impersonate="false" />

== Database Server ==
* Create a Windows account on your SQL Server computer that matches the ASP.NET process account (ASPNET) The user name and password must match the ASPNET account.  Give the account the following privileges: 
** Access this computer from the network 
** Deny logon locally 
** Log on as a batch job 
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 the newly created local 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 and add the database user to the role
* Establish database permissions for the database role

== Communications Security ==
* Configure the Web site for SSL
* If your database server is not in a protected datacenter, configure IPSec between the Web server and database server

== Analysis ==
* Integrated Windows authentication in IIS is ideal in this scenario because all users have Windows accounts and are using Microsoft Internet Explorer. The benefit of Integrated Windows authentication is that the user's password is never sent over the network. Additionally, the logon is transparent for the user because Windows uses the current interactive user's logon session. 
* ASP.NET is running as least privileged account, so potential damage from compromise is mitigated. 
* You don't need to impersonate in ASP.NET to perform .NET role checks or to secure resources within Windows ACLs against the original caller. To perform .NET role checks against the original caller, the WindowsPrincipal object that represents the original caller is retrieved from the HTTP context as follows: 
 WindowsPrincipal wp = (HttpContext.Current.User as 
  WindowsPrincipal);
 if ( wp.IsInRole("Manager") )
 {
  // User is authorized to perform manager-specific functionality
 }

Note   Additionally, if you are running ASP.NET 2.0 and have enabled the Role Manager feature, you can use the Roles APIs for role checks. For more information on using the Role Manager feature in ASP.NET 2.0, see "How To: Use Role Manager in ASP.NET 2.0."
* The ASP.NET FileAuthorizationModule provides ACL checks against the original caller for ASP.NET file types that are mapped within IIS to the aspnet_isapi.dll. For static file types such as .jpg, .gif and .htm files, IIS acts as the gatekeeper and performs access checks using the original caller's identity, based on the NTFS permissions associated with the file. 
* Using Windows authentication to SQL Server means that you avoid storing credentials in files and passing credentials over the network to the database server. 
* The use of a duplicated Windows account on the database server (one that matches the ASPNET process identity account) results in increased administration. If a password is changed on one computer, it must be synchronized and updated on the other. In some scenarios, you may be able to use a least-privileged domain account for easier administration. This is not an issue when using a custom Windows domain account for running an ASP.NET process, or when using IIS 6.0 on Windows Server 2003, because the default process identity can be given direct access to the database. 
* The duplicated local account approach also works in the presence of a firewall where the ports required for Windows authentication may not be open. The use of Windows authentication and domain accounts may not work in this scenario. 
* You'll need to ensure that your Windows groups are as granular as your security needs. Because .NET role-based security is based on Windows group membership this solution relies on Windows groups being set up at the correct level of granularity to match the categories of users (sharing the same security privileges) who access the application. The Windows groups that you use here to manage roles could be local to that computer or domain groups 
* SQL Server database user roles are preferred to SQL server application roles to avoid the associated password management and connection pooling issues associated with the use of SQL application roles. 
* Applications activate SQL application roles by calling a built-in stored procedure with a role name and a password. Therefore, the password must be stored securely. Database connection pooling must also be disabled when you use SQL application roles, which severely impacts application scalability. 
* The database user is added to a database user role and permissions are assigned for the role so that if the database account changes; you don't have to change the permissions on all database objects. 

== Q&A ==
* Q. ''Why can't I enable impersonation for the Web application, so that I can secure the resources accessed by my Web application using ACLs configured against the original caller?'' 
* A. If you enable impersonation, the impersonated security context will not have network credentials (assuming delegation is not enabled and you are using Integrated Windows authentication). Therefore, the remote call to SQL Server will use a NULL session, which will result in a failed call. With impersonation disabled, the remote request will use the ASP.NET process identity. 

The preceding scenario uses the ASP.NET FileAuthorizationModule, which performs authorization using Windows ACLs against the original caller identity and does not require impersonation. 

If you use Basic authentication instead of Integrated Windows authentication (NTLM) and you do enable impersonation, each call to the database would use the original caller's security context. Each user account (or the Windows groups to which the user belongs) would require SQL Server logins. Permissions on database objects would need to be secured against the Windows group (or original caller). 

* Q. ''The database doesn't know who the original caller is. How can I create an audit trail? ''
* A. Audit end user activity within the Web application or pass the identity of the user explicitly as a parameter of the data access call. 

== Related Scenarios ==
=== Non-Internet Explorer Browsers ===
Integrated Windows authentication to IIS requires Internet Explorer. In a mixed browser environment, your typical options would include: 
* ''Basic authentication and SSL''. Basic authentication is supported by most browsers. Since the user's credentials are passed over the network, you must use SSL to secure the scenario. 
* ''Client certificates''. Individual client certificates can either be mapped to a unique Windows account or a single Windows account can be used to represent all clients. The use of client certificates also requires SSL. 
* ''Forms authentication''. Forms authentication can validate credentials against a custom data store such as a database or against Active Directory.
 
If you authenticate against Active Directory, make sure that you retrieve only the necessary groups that are pertinent to your application. Just like you shouldn't issue queries against a database using SELECT * clauses, you shouldn't blindly retrieve all groups from Active Directory. 

Note   ASP.NET 2.0 provides a Membership feature along with Login controls that help you to easily implement Forms authentication. With ASP.NET, you do not need to write any custom code because the Membership feature does the validation for you.
If you authenticate against a database, you need to carefully parse the input used in SQL commands to protect against SQL injection attacks, and you should store password hashes (with salt) in the database instead of clear text or encrypted passwords. 

Note   Although the built-in membership providers in ASP.NET 2.0 do this automatically, you will need to perform this step when developing the custom membership feature.

Notice that in all cases, if you don't use Integrated Windows authentication, where the platform manages credentials for you, you end up using SSL. However, this benefit pertains strictly to the authentication process. If you are passing security sensitive data over the network, you must still use IPSec or SSL.

=== SQL authentication to the database ===
In some scenarios you may be forced to use SQL authentication instead of the preferred Windows authentication. For example, there may be a firewall between the Web application and database, or the Web server may not be a member of your domain for security reasons. This also prevents Windows authentication. In this case, you might use SQL authentication between the database and Web server. To secure this scenario, you should: 
* Use the Data Protection API (DPAPI) to secure database connection strings that contain usernames and passwords.
* Use IPSec or SSL between the Web server and database server to protect the clear text credentials passed over the network. 

=== Flowing the original caller to the database ===
In this scenario, calls are made from the Web application to the database using the security context of the original caller. With this approach, it's important to note the following: 
* If you choose this approach, you need to use either Kerberos authentication (with accounts configured for delegation) or Basic authentication.
* You must also enable impersonation in ASP.NET. This means that local system resource access is performed using the original caller's security context and as a result, ACLs on local resources such as the registry and event log require appropriate configuration. An alternative to enabling impersonation in ASP.NET is to use programmatic impersonation. 
* Database connection pooling is limited because original callers won't be able to share connections. Each connection is associated with the caller's security context. 
* An alternate approach to flowing the user's security context is to flow the original caller's identity at the application level (for example, by using method and stored procedure parameters). 


[[Category: Application Scenarios]]

__NOTOC__