ASP.NET 2.0 Intranet - Windows Authentication, SQL Roles

- J.D. Meier, Alex Mackman, and Prashant Bansode

Applies To

• ASP.NET 2.0
• SQL Server 2000
• Windows Server 2003

Scenario

In this scenario, an intranet ASP.NET application accesses a back-end SQL Server database. The application is used by corporate employees who have accounts within Active Directory.

Key Characteristics

• Windows authentication
• AD user store
• SQL Server role store

Solution

The Web application uses a trusted subsystem model and executes calls to the database on behalf of the original callers.

Web Server

IIS

• A dedicated application pool is used and configured to run under a custom domain service account with access to the database.
• The application's virtual directory is configured in IIS for Windows authentication. Anonymous access is disabled.

ASP.NET

Authentication

• ASP.NET is configured for Windows authentication <authentication mode="Windows"/>

Authorization

• Application is configured to use SqlRoleProvider
• The solution configures the provider to use a SQL Server role store for which the connection string is contained in the <connectionStrings> section of Web.config.
• Role-checks (user's Windows group membership) are performed by using role manager APIs with WindowsTokenRoleProvider
• If you have role segmentation in your application then you use URL authorization. e.g. You might have pages that only members of the "Sales" role should be able to access and others that only members of "HR" should be able to access.

Configuration

• The database connection string includes Integrated Security=SSPI or Trusted Connection=Yes for Windows authentication.
• The database connection string is held in the <connectionString> section of the application's Web.config. This can be encrypted by using a protected configuration provider (DPAPI on a single machine, RSA if in a Web farm). Tradeoff here is added deployment complexity vs. keeping the database name and location a secret

Database Server

Authentication

• SQL Server configured for Windows authentication

Authorization

• A SQL Server login is created for the application's application pool identity.
• The login is mapped to a database user for the Web application.
• The database user is placed in a database role for the Web application.
• Database permissions are granted to to the database role. Ideally, role only grants execute permissions on necessary stored procedures.

Secure Communication

Browser to Web App

• SSL is used between browser and Web server to protect sensitive data on the wire.

Web App to Database

• If you're not in a secure data center, then IPSec or SSL can be used between the Web server and database server to protect sensitive data on the wire. Choose IPSec to encrypt all traffic between servers or SSL to encrypt per application or service.

Configuration

	<configuration>

<connectionStrings>

   	<add name="SqlRoleManagerConnection" 

connectionString="Data Source=sqlinstance;

        		                 Initial Catalog=aspnetdb;Integrated Security=SSPI;">
   	</add>
 	</connectionStrings>
	</configuration>

	<roleManager enabled="true" defaultProvider="SqlRoleManager">
 	<providers>
   	<add name="SqlRoleManager" 

type="System.Web.Security.SqlRoleProvider" connectionStringName="SqlRoleManagerConnection" applicationName="MyApplication" />

 	</providers>
	</roleManager>