How To: Use Forms Authentication with SQL Server in ASP.NET 2.0

patterns & practices Developer Center

J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Andy Wigley, Kishore Gopalan

Microsoft Corporation

August 2005

Applies To

• ASP.NET version 2.0
• SQL Server 2000

Summary

This How To shows you how you can use forms authentication with the SQL Server membership provider. Forms authentication with SQL Server is most applicable in situations where users of your application are not part of your Windows domain, and as a result, they do not have Active Directory accounts. This How To explains how to create a login page using the new membership Login control, configure your Web application to use forms authentication, create the user store database, grant database access to your Web application account, configure ASP.NET membership settings, and set password complexity rules.

Contents

Objectives
Overview
Summary of Steps
Step 1. Create a Web Application with a Login Page
Step 2. Configure the Web Application for Forms Authentication
Step 3. Configure ASP.NET for Membership
Step 4. Test the Forms Authentication
Deployment Considerations
Security Considerations
Additional Considerations
Additional Resources

Objectives

• Use the SqlMembershipProvider with forms authentication.
• Set up the SQL Server membership database.
• Restrict the forms authentication cookie to HTTPS connections.
• Use the membership feature to enforce password complexity rules.
• Learn key production considerations when using membership and forms authentication.
• Learn key security considerations when using membership and forms authentication.

Overview

ASP.NET version 2.0 introduces a membership feature that you can use with forms authentication. The membership feature provides an abstraction for the underlying data store used to maintain user credentials such as user names and passwords, and it provides an API that allows you to easily validate user credentials and manage the user database. ASP.NET version 2.0 supports SQL Server and Active Directory providers, and you can also create your own providers for custom stores.

This How To shows you how to develop a simple Web site that uses forms authentication with the SQL Server membership provider. This provider uses an underlying SQL Server database as its user store.

Summary of Steps

To develop a simple application that uses forms authentication with the SQL Server membership provider, perform the following steps:

• Step 1. Create a Web application with a login page.
• Step 2. Configure the Web application for forms authentication.
• Step 3. Configure ASP.NET for membership.
• Step 4. Test the forms authentication.

Step 1. Create a Web Application with a Login Page

In this step, you create a simple Web application with a default page and a login page. The login page allows existing users to login and new users to register by creating new accounts.

To create a Web application with a login page

1. Start Visual Studio .NET and create a new ASP.NET Web site named FormsAuthSQL.

2. Use Solution Explorer to add a new Web form named Login.aspx to the site.

3. Add a Login control to Login.aspx.

   By default, this control displays user name and password fields and a Remember me next time check box. If the user selects this check box, a persistent authentication cookie is created and the user's browser stores it on the user's hard disk.

   To prevent an attacker from stealing an authentication cookie from the client's computer, you should generally not create persistent authentication cookies. To disable this feature, set the DisplayRememberMe property of the Login control to false.

   Note that when a user clicks login on the Login control, it automatically validates the user by calling the configured membership provider, creates a forms authentication ticket, and then redirects the user back to the originally requested page. It executes code similar to the following example.

   if (Membership.ValidateUser(username, password))
   {
     // User has supplied valid credentials
   
     // In the following method call, the second Boolean parameter 
     // determines whether a persistent authentication cookie
     // is created.
   
     FormsAuthentication.RedirectFromLoginPage(username,
                                               rememberMeIsChecked);
   }
   
   

4. Add a CreateUserWizard control beneath the Login control to allow new users to register with your site and create new accounts.

Step 2. Configure the Web Application for Forms Authentication

In this step, you configure the ASP.NET application to use forms authentication.

To configure the Web application for forms authentication

1. Use Solution Explorer to add a Web.config file to your project.

2. Locate the <authentication> element, and then change the mode attribute to "Forms".

3. Add the following <forms> element as a child of the <authentication> element, and then set the name and timeout attributes as follows.

   <authentication mode="Forms">
     <forms
         name="SqlAuthCookie"       
         timeout="10" />
   </authentication>
   
   

   If you just set the mode attribute of the <authentication> element and omit the <forms> element, default settings are used for the <forms> configuration. You should configure only those attributes that you need to overwrite. The default settings for forms authentication as defined in the Machine.config.comments file are shown here.

   <forms name=".ASPXAUTH" loginUrl="login.aspx" 
          defaultUrl="default.aspx" protection="All" timeout="30" path="/" 
          requireSSL="false" slidingExpiration="true"
          cookieless="UseDeviceProfile" domain="" 
          enableCrossAppRedirects="false">
     <credentials passwordFormat="SHA1" />
   </forms>
   
   

4. Add the following <authorization> element under the <authentication> element in the Web.config file. This allows all authenticated users to access your Web site.

   <authorization> 
     <deny users="?" />
     <allow users="*" />
   </authorization>
   
   

   The preceding configuration allows only authenticated users to access the application. The "?" indicates unauthenticated users and the "*" indicates all users. By denying unauthenticated users, any requests made by unauthenticated users are redirected to the login page. The loginUrl attribute of the <forms> element determines the name of the login page. The default setting of this attribute in the Machine.config.comments file is Login.aspx.

Step 3. Configure ASP.NET for Membership

In this step, you configure the SQL Server membership provider by performing the following actions:

• Create a user store database.
• Grantdatabase access to your Web application account.
• Configure ASP.NET membership settings.

Create a User Store Database

The SQL Server membership provider stores user information in a SQL Server database. You can create your SQL Server user store manually by using Aspnet_regsql.exe from the command line. Alternatively, you can run Aspnet_regsql.exe in Wizard mode or use the ASP.NET Web Site Configuration tool available on the Website menu in Visual Studio .NET 2005.

To create the user store database

Use Aspnet_regsql.exe to create the membership database. From a Visual Studio 2005 command prompt, run the following command.

aspnet_regsql -S (local) -E -A m

-S specifies the server, which is (local) in this example.

-E specifies to use Windows authentication to connect to SQL Server.

-A m specifies to add only the membership feature. For simple authentication against a SQL Server user store, only the membership feature is required.

For a complete list of the commands, run Aspnet_regsql /?.

Expected Results

If the Aspnetdb database does not exist, two database files are created:

• Aspnetdb.mdf
• Aspnetdb_log.LDF

Within the Aspnetdb database, the following tables are created if they do not exist:

• aspnet_Applications
• aspnet_Membership
• aspnet_SchemaVersions
• aspnet_Users

Grant Database Access to Your Web Application Account

Your Web application process account requires access to the Aspnetdb database. If you run your application under Microsoft Internet Information Services (IIS) 6.0 on Windows Server 2003, the NT AUTHORITY\Network Service account is used by default to run Web applications.

To grant database access

1. Create a SQL Server login for NT AUTHORITY\Network Service.
2. Grant the login access to the Aspnetdb database by creating a database user.
3. Add the user to the aspnet_Membership_FullAccess database role.

You can perform these steps by using Enterprise Manager or you can run the following script in SQL Query Analyzer.

-- Create a SQL Server login for the Network Service account
sp_grantlogin 'NT AUTHORITY\Network Service'

-- Grant the login access to the membership database
USE aspnetdb
GO
sp_grantdbaccess 'NT AUTHORITY\Network Service', 'Network Service'

-- Add user to database role
USE aspnetdb
GO
sp_addrolemember 'aspnet_Membership_FullAccess', 'Network Service'
  

Configure ASP.NET Membership Settings

In this step, you specify membership settings for the Web site.

To configure the Membership settings

1. In the Web.config file, add a connection string similar to the following to point to your membership database.

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

   In this case, MyLocalSQLServer is the name you will use for this connection. Set the connectionString attribute to the membership database you created earlier.

2. Add a <membership> element after the <authorization> element as shown in the following example. Note the use of the <clear/> element. This prevents the default provider from being loaded and then never used.

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

   Make sure you set the connectionStringName attribute to the same name ("MyLocalSQLServer") you specified earlier in the connectionStrings section. Also set the applicationName attribute to a unique value representing the application. User details are organized by application name within the membership database.

   Note   You must set the defaultProvider attribute of the <membership> element to point to the provider definition. The login controls use this attribute to determine which property to use by default. However, these controls also expose a MembershipProvider property that lets you use non-default providers with the controls. While you can also set the MembershipProvider property of the login controls, it is good practice to set the defaultProvider attribute.

Step 4. Test the Forms Authentication

In this step, you test forms authentication.

Add a Page_Load Event Handler

Add the following code to the Page_Load event handler of your Default.aspx page. This page should only be displayed to authenticated users. To prove that this is the case, the code displays information obtained from the forms authentication ticket issued to authenticated users.

protected void Page_Load(object sender, EventArgs e)
{
  Response.Write("Hello, " + Server.HtmlEncode(User.Identity.Name));

  FormsIdentity id = (FormsIdentity)User.Identity;
  FormsAuthenticationTicket ticket = id.Ticket;

  Response.Write("<p/>TicketName: " + ticket.Name );
  Response.Write("<br/>Cookie Path: " + ticket.CookiePath);
  Response.Write("<br/>Ticket Expiration: " +  
                  ticket.Expiration.ToString());
  Response.Write("<br/>Expired: " + ticket.Expired.ToString());
  Response.Write("<br/>Persistent: " + ticket.IsPersistent.ToString());
  Response.Write("<br/>IssueDate: " + ticket.IssueDate.ToString());
  Response.Write("<br/>UserData: " + ticket.UserData);
  Response.Write("<br/>Version: " + ticket.Version.ToString());
}
  

Create a New User

In this step, you create a new user to test the login functionality.

To create a new user

1. Browse to your application's Default.aspx page.

   The earlier configuration of the <authorization> element prevents unauthenticated users from accessing any pages in the application and redirects you to the Login.aspx page.

2. Create a new user with a strong password. The default rules applied by the SQL membership provider for the password are as follows:

   • The password must be at least 7 characters long.
   • The password must contain at least 1 non-alphanumeric character.

3. Log in with your new user account. If successful, you should be redirected to the Default.aspx page that you initially requested, and details from the forms authentication ticket should be displayed.

Deployment Considerations

When using forms authentication, you should consider the following when you deploy your application:

• Connecting to a remote database
• Connecting to a remote database without Windows authentication

Connecting to a Remote Database

If your database is on a remote server, you should use Windows authentication to connect. By using Windows authentication, you avoid storing credentials in connection strings and avoid passing passwords over the network to the database server. To use Windows authentication, you usually use a custom domain service account to run your application. This account's identity is then used to authenticate against the remote server.

To authorize your application's domain account in the database

Use the following T-SQL commands to create a SQL Server login for your domain account and grant it access to the membership database.

-- Create a SQL Server login for your application's domain account
sp_grantlogin 'domain\webservername$'

-- Grant the login access to the membership database
USE aspnetdb
GO
sp_grantdbaccess 'domain\webservername$', 'Web Server Name'

-- Add user to database role
USE aspnetdb
GO
sp_addrolemember 'aspnet_Membership_FullAccess', 'domain\webservername$'
  

You should also encrypt your connection string in the Web.config file to avoid disclosing server and database names. To do this, use either the DPAPI or RSA protected configuration providers. If you deploy your application in a Web farm, use RSA because it is easy to export and import RSA keys across servers.

For more information, see How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI and How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA.

Connecting to a Remote Database Without Windows Authentication

When you use Windows authentication to connect to SQL Server, you use either Kerberos or NTLM authentication, depending on the configuration of your servers and domain. You might not be able to use Windows authentication if:

• Your database client and database server are separated by a firewall that prevents Kerberos or NTLM authentication.
• Your application server and database server are in separate domains with no trust.

In these situations, you can use either mirrored local accounts or SQL Authentication. With mirrored local accounts, you configure two accounts on each server with identical user names and passwords. You must ensure that the passwords remain the same.

If you use SQL Authentication, you must:

• Manage the credentials yourself.
• Protect the credentials in the connection string, for example by encrypting the connection string in the Web.config file.
• (Potentially) protect the credentials passed over the network from the Web server to the database.

For more information see How To: Connect to SQL Server Using SQL Authentication in ASP.NET 2.0.

Security Considerations

Failing to protect authentication tickets is a common vulnerability that can lead to unauthorized spoofing and impersonation, session hijacking, and elevation of privilege. When you use forms authentication, consider the following recommendations to help ensure a secure authentication approach:

• Restrict the authentication cookie to HTTPS connections. To prevent forms authentication cookies from being captured and tampered with while crossing the network, ensure that you use Secure Sockets Layer (SSL) with all pages that require authenticated access and restrict forms authentication tickets to SSL channels.
• Partition the site for SSL. This allows you to avoid using SSL for the entire site.
• Do not persist forms authentication cookies. Do not persist authentication cookies because they are stored in the user's profile on the client computer and can be stolen if an attacker gets physical access to the user's computer
• Consider reducing ticket lifetime. Consider reducing the cookie lifetime to reduce the time window in which an attacker can use a captured cookie to gain access to your application with a spoofed identity.
• Consider using a fixed expiration. In scenarios where you cannot use SSL, consider setting slidingExpiration="false".
• Enforce strong user management policies. Use and enforce strong passwords for all user accounts to ensure that people cannot guess one another's passwords and to mitigate the risk posed by dictionary attacks.
• Enforce password complexity rules. Validate passwords entered through the CreateUserWizard control, by setting its PasswordRegularExpression property to an appropriate regular expression. Also configure the membership provider on the server to use the same regular expression.
• Perform effective data validation on all requests. Perform strict data validation to minimize the possibilities of SQL injection and cross-site scripting.
• Use distinct cookie names and paths. By ensuring unique cookie names and paths, you prevent possible problems that can occur when hosting multiple applications on the same server.
• Keep authentication and personalization cookies separate. Keep personalization cookies that contain user-specific preferences and non-sensitive data separate from authentication cookies.
• Use absolute URLs for navigation. This is to avoid potential issues caused by redirecting from HTTP to HTTPS pages.

For more information about these additional security considerations, see How To: Protect Forms Authentication in ASP.NET 2.0.

Additional Considerations

In addition to the preceding guidance, consider the following additional items to offer further protection:

• Password complexity
• Account lockout

Password Complexity

The membership provider that the CreateUserWizard and Login controls use determines password complexity requirements. For example, by default, the SQL Membership provider requires passwords of at least seven characters in length with at least one non-alphanumeric character.

Configuring Provider Enforced Strong Passwords

To configure the precise password complexity rules enforced by the SqlMembershipProvider, you can set the following additional attributes:

• passwordStrengthRegularExpression. The default is "".
• minRequiredPasswordLength. The default is seven.
• minRequiredNonalphanumericCharacters. The default is one.

The following configuration supplies a custom regular expression to constrain the passwords used by the membership provider.

<membership defaultProvider="MySqlMembershipProvider">
  <providers>
    <add name="MySqlMembershipProvider" 
         connectionStringName="MyLocalSQLServer" 
         applicationName="MyAppName"
         passwordStrengthRegularExpression=
                    "^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" 
         type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</membership>
  

The regular expression in the preceding fragment constrains the password to between 8 and 10 characters. It must also contain a combination of uppercase, lowercase, and numeric digits, with no special characters. The (.*\d) refers to the digits, the (.*[a-z]) refers to the lowercase characters, the *[A-Z] refers to the uppercase characters and the {8,10} constrains the range to be between 8 to 10 characters.

For more information about regular expressions, see How To: Use Regular Expressions to Constrain Input in ASP.NET.

Note that the SqlMembershipProvider always first evaluates the password against the minRequiredPasswordLength and minRequiredNonalphanumericCharacters attributes. If the regular expression is intended to be the authoritative match, you should set the other two attributes to weaker values; for example, a minimum length of 1 and zero non-alphanumeric characters.

The following configuration uses the minRequiredPasswordLength and minRequiredNonalphanumericCharacters attributes to constrain the password.

<membership defaultProvider="MySqlMembershipProvider">
  <providers>
    <add name="MySqlMembershipProvider" 
         connectionStringName="MyLocalSQLServer" 
         applicationName="MyAppName"
         minRequiredPasswordLength="8"
         minRequiredNonalphanumericCharacters="2"
         type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</membership>
  

Validating Strong Passwords

You can also use a regular expression with the CreateUserWizard control to enforce passwords complexity rules. By doing this, you benefit from both client-side validation and server-side validation.

To validate a password entered through the CreateUserWizard control, set its PasswordRegularExpression property to an appropriate regular expression, such as the that follows. However, note that you should then also configure a similar regular expression on the server with the membership provider. You should not rely on the client-side checks.

^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$
  

Account Lockout

You can configure the account lockout policy. To do this, use the following two provider attributes:

• maxInvalidPasswordAttempts. This defines the number of failed password attempts or failed password answer attempts that are allowed before locking out a user's account. When the number of failed attempts equals the value set in this attribute, the user's account is locked out. The default value is five.
• passwordAttemptWindow. This defines the time window, in minutes, during which failed password attempts and failed password answer attempts are tracked. The default value is ten.

With these defaults, if there are five failed login attempts within ten minutes, the account will be locked out.

Additional Resources

• How To: Protect Forms Authentication in ASP.NET 2.0
• How To: Use Forms Authentication with Active Directory in ASP.NET 2.0

Feedback

Provide feedback by using either a Wiki or e-mail:

• Wiki. Security Guidance Feedback page: https://channel9.msdn.com/wiki/securityguidancefeedback/
• E-mail. Send e-mail to mailto:secguide@microsoft.com.

We are particularly interested in feedback regarding the following:

• Technical issues specific to recommendations
• Usefulness and usability issues

Technical Support

Technical support for the Microsoft products and technologies referenced in this guidance is provided by Microsoft Support Services. For product support information, please visit the Microsoft Product Support Web site at https://support.microsoft.com.

Community and Newsgroups

Community support is provided in the forums and newsgroups:

• MSDN Newsgroups:https://www.microsoft.com/communities/newsgroups/default.mspx
• ASP.NET Forums:http://forums.asp.net

To get the most benefit, find the newsgroup that corresponds to your technology or problem. For example, if you have a problem with ASP.NET security features, you would use the ASP.NET Security forum.

Contributors and Reviewers

• External Contributors and Reviewers: Andy Eunson; Jason Taylor, Security Innovation; Rudolph Araujo, Foundstone Professional Services
• Microsoft Consulting Services and PSS Contributors and Reviewers: Adam Semel, Tom Christian, Wade Mascia
• Microsoft Product Group Contributors and Reviewers: Stefan Schackow, Vikas Malhotra
• MSDN Contributors and Reviewers: Brian Johnson, Kent Sharkey
• Test team: Larry Brader, Microsoft Corporation; Nadupalli Venkata Surya Sateesh, Sivanthapatham Shanmugasundaram, Infosys Technologies Ltd
• Edit team: Nelly Delgado, Microsoft Corporation; Tina Burden McGrayne, TinaTech Inc.
• Release Management: Sanjeev Garg, Microsoft Corporation