ASP.NET 2.0 Internet - Forms Authentication to SQL, Roles in SQL

- 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 Internet ASP.NET application accesses a back-end SQL Server database. The application is used by the general public.

Key Characteristics

• Accounts are in a SQL Server database store
• Clients are mixed Internet Explorer and other browsers
• The application provides sensitive, per-user data.
• Only authenticated clients should access the application.
• Each user only has access to their data.

Solution

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 anonymous access.
• The application's virtual directory is configured for SSL

ASP.NET

Authentication

• ASP.NET is configured for Forms authentication <authentication mode="Forms"/>
• 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.
• Connection string configured to point to the user store in SQL Server.
• SqlMembershipProvider is configured to use with Membership feature for forms authentication
• Set the defaultProvider attribute for the membership element to the configured SqlMembershipProvider.

Authorization

• Application is configured to enable role manager (SqlRoleProvider).
• Connection string pointing to Role Store in SQL Server is configured.
• Role-checks are performed by using Role Manager APIs with SqlRoleProvider.
• The database connection string includes Integrated Security=SSPI or Trusted Connection=Yes for Windows authentication.

Configuration

• 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). The tradeoff here is added deployment complexity vs. keeping the database name and location a secret.

Database Server

Authentication

• SQL Server configured for Windows authentication
• 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.

Authorization

• 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 Server

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

Web Server to Database

• IPSec or SSL can be used between Web server and database server to protect sensitive data on the wire.

Configuration

• Membership

<connectionStrings>
 <add name="MyLocalSQLServer"
      connectionString="Initial Catalog=aspnetdb;data source=localhost;Integrated Security=SSPI;" />
</connectionStrings>

<membership defaultProvider="MySqlMembershipProvider" >
 <providers>
   <clear/>
   <add name="MySqlMembershipProvider"
        connectionStringName="MyLocalSQLServer"
        applicationName="MyAppName"
        type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, 

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

 </providers>
</membership>

• SqlRoleProvider

<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>