View source for ASP.NET 1.1 - Web Services to SQL Server

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


== Applies To ==
* ASP.NET 1.1
* Web Services (ASMX 1.1)



== Scenario ==
In this scenario, a Web server that runs ASP.NET pages connects to a Web service on a remote server. This server in turn connects to a remote database server. As an example, consider a HR Web application that provides sensitive data specific to a user. The application relies on the Web service for data retrieval. 

[[image:IntranetWebServicesToSQLServer.gif]]



== Characteristics ==
* Users have Internet Explorer 5.x or later. 
* All computers run Windows 2000 or later. 
* User accounts are in Active Directory within a single forest. 
* The application flows the original caller's security context all the way to the database. 
* All tiers use Windows authentication. 
* The machine account that identifies the ASP.NET default process, or the custom domain-level accounts used for ASP.NET processes, are configured for delegation. 
* The database does not support delegation.



== Solution ==
The Web service exposes a method that allows an individual employee to retrieve their own personal details. 

[[image:IntranetWebServicesToSQLServerSolution.gif]]


Details must be provided only to authenticated individuals using the Web application. The Web service also provides a method that supports the retrieval of any employee details. This functionality must be available only to members of the HR or payroll department. In this scenario, employees are categorized into three Windows groups: 
* HRDept (members of the HR department).  Members of this group can retrieve details about any employee. 
* PayrollDept (members of the Payroll department).  Members of this group can retrieve details about any employee. 
* Employees (all employees) .  Members of this group can only retrieve their own details. 


Due to the sensitive nature of the data, the traffic between all nodes should be secure.



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


ASP.NET
<ul>
<li>Configure your ASP.NET Web application to use Windows authentication. Edit Web.config in your Web application's virtual directory.  Set the <authentication> element to:
<pre>
 <authentication mode="Windows" />
</pre>
</li>

<li>Configure your ASP.NET Web application for impersonation. Edit Web.config in your Web application's virtual directory.  Set the <identity> element to:
<pre>
 <identity impersonate="true" />
</pre>
</li>
</ul>



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


ASP.NET
<ul>
<li>Change the ASPNET password to a known value  ASPNET is a least privileged local account used by default to run the ASP.NET Web applications.  Set the ASPNET account's password to a know 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.
</li>

<li>Configure your ASP.NET Web service to use Windows authentication.  Edit Web.config in your Web service's virtual directory.  Set the <authentication> element to:
<pre>
 <authentication mode="Windows" />
</pre>
</li>

<li>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: 
<pre>
 <identity impersonate="false" />
</pre>

Note that because impersonation is disabled by default, the same effect can be achieved by removing the <identity> element.
</li>
</ul>



== Database Server ==
<ul>
<li>Create a Windows account on your SQL Server computer that matches the ASP.NET process account used to run the Web service.  The user name and password must match your custom ASP.NET account.  Give the account the following privileges: 
<ul>
<li>Access this computer from the network</li>
<li>Deny logon locally</li>
<li>Log on as a batch job</li>
</ul>

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.
</li>

<li>Configure SQL Server for Windows authentication.</li>

<li>Create a SQL Server Login for the newly created Windows 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.
</li>

<li>Create a new database user and map the login name to the database user.</li>

<li>Create a new database user role and add the database user to the role.</li>

<li>Establish database permissions for the database user role.</li>
</ul>



== Communications Security ==
* Configure the Web site on the Web server for SSL.
* Configure IPSec between Web server and database server.



== Analysis ==
<ul>
<li>Integrated Windows authentication in IIS is ideal in this scenario because all users are using Windows 2000 or later, * * * * Internet Explorer 5.x or later, and have accounts in Active Directory, which makes it possible to use the Kerberos authentication protocol (which supports delegation). This allows you to flow the security context of the user across computer boundaries. 
</li>

<li>End user accounts must be NOT marked as "Sensitive and cannot be delegated" in Active Directory. The Web server computer account must be marked as "Trusted for delegation" in Active Directory.
</li>

<li>Because ASP.NET on the Web server and application server runs with a least privileged local account, potential damage from compromise is mitigated. 
</li>

<li>The Web service and Web application are both configured for Windows authentication. IIS on both computers is configured for Integrated Windows authentication. 
</li>

<li>When making a call to the Web service from the Web application, no credentials are passed by default. They are required in order to respond to the network authentication challenge issued by IIS on the downstream Web server. You must specify this explicitly by setting the Credentials property of the Web service proxy as shown in the following: 
<pre>
 wsproxy.Credentials = CredentialCache.DefaultCredentials;
</pre>
</li>

<li>The Web application is configured for impersonation. As a result, calls from the Web application to the Web service flow the original caller's security context and allow the Web service to authenticate (and authorize) the original caller. 
</li>

<li>.NET roles are used within the Web service to authorize the users based on the Windows group to which they belong (HRDept, PayrollDept and Employees). Members of HRDept and PayrollDept can retrieve employee details for any employee, while members of the Employees group are authorized to retrieve only their own details. 
</li>

<li>Web methods can be annotated with the PrincipalPermissionAttribute class to demand specific role membership, as shown in the following code sample. Notice that PrincipalPermission can be used instead of PrincipalPermissionAttribute. This is a common feature of all .NET attribute types. 
<pre>
 [WebMethod]
 [PrincipalPermission(SecurityAction.Demand, 
                  Role=@"DomainName\HRDept)]
 public DataSet RetrieveEmployeeDetails()
 {
 }
</pre>

The attribute shown in the preceding code means that only members of the DomainName\HRDept Windows group are allowed to call the RetrieveEmployeeDetails method. If any nonmember attempts to call the method, a security exception is thrown. 
</li>

<li>ASP.NET File Authorization (within the Web application and Web service) performs ACL checks against the caller for any file type for which a mapping exists in the IIS Metabase that maps the file type to Aspnet_isapi.dll. Static file types (such as .jpg, .gif, .htm, and so on), for which an ISAPI mapping does not exist are checked by IIS (again using the ACL attached to the file). 
</li>

<li>Because the Web application is configured for impersonation, resources accessed by the application itself must be configured with an ACL that grants at least read access to the original caller. 
</li>

<li>The Web service does not impersonate or delegate; therefore, it accesses local system resources and the database using the ASP.NET process identity. As a result, all calls are made using the single process account. This enables database connection pooling to be used. If the database doesn't support delegations (such as SQL Server 7.0 or earlier), this scenario is a good option. 
</li>

<li>Windows authentication to SQL Server means you avoid storing credentials on the Web server and it also means that credentials are not sent across the network to the SQL Server computer. 
</li>

<li>SSL between the original caller and Web server protects the data passed to and from the Web application. 
</li>

<li>IPSec between the downstream Web server and database protects the data passed to and from the database.
</li>
</ul>



== Pitfalls ==
* The use of a duplicated Windows account on the database server (one that matches the ASP.NET process account) results in increased administration. Passwords should be manually updated and synchronized on a periodic basis.  As an alternative, consider using least-privileged domain accounts.  Note - 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.
* 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 will access the application. 
* Because Kerberos delegation is unrestricted on Windows 2000 Server, you must carefully control which applications identities run on the Web server. To raise the bar on security, limit the scope of the domain account's reach by removing the account from Domain Users group and provide access only from appropriate computers.  Note - However, Windows Server 2003 introduces a feature known as constrained delegation. This feature allows administrators to specify exactly which services can be accessed on a downstream server or a domain account when using an impersonated user's security context.



== Q&A ==
* 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 service or pass the identity of the user explicitly as a parameter of the data access call.



== Related Scenarios ==
If you need to connect to non-SQL Server databases, or you currently use SQL authentication, you must pass database account credentials explicitly using the connection string. If you do so, make sure that you securely store the connection string.



[[Category: Application Scenarios]]



__NOTOC__