Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication

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

J.D. Meier, Alex Mackman, Michael Dunner, and Srinath Vasireddy
Microsoft Corporation

Published: November 2002

Last Revised: January 2006

Applies to:

• ASP.NET 1.1

See the "patterns & practices Security Guidance for Applications Index" for links to additional security resources.

See the Landing Page for a starting point and complete overview of Building Secure ASP.NET Applications.

Summary: This How To shows you how to implement Forms authentication against a SQL Server credential store. It also shows you how to store password digests in the database. (12 printed pages)

Contents

Summary of Steps Step 1. Create a Web Application with a Logon Page
Step 2. Configure the Web Application for Forms Authentication Step 3. Develop Functions to Generate a Hash and Salt value Step 4. Create a User Account Database Step 5. Use ADO.NET to Store Account Details in the Database Step 6. Authenticate User Credentials against the Database Step 7. Test the ApplicationAdditional Resources

Web applications that use Forms authentication often store user credentials (user names and passwords) together with associated role or group lists in MicrosoftSQL Server.

This How To describes how to securely look up user names and validate passwords against SQL Server. There are two key concepts for storing user credentials securely:

• Storing password digests. For security reasons, passwords should not be stored in clear text or encrypted format in the database. This How To describes how to create and store a one-way hash of a user's password rather than the password itself. This approach is preferred to storing a clear text or encrypted version of the user's password, for two reasons. First, it helps to prevent an attacker who gains access to your user store from obtaining the user passwords. In addition, this approach helps you to avoid the key-management issues associated with encryption techniques.

• Using a salt value when creating the hash helps to slow an attacker who is attempting to perform a dictionary attack (where an attacker attempts to decipher the key used for hashing). This approach gives you additional time to detect and react to the compromise.

  Important: The one drawback of not storing passwords in the database is that if a user forgets a password, it cannot be recovered. As a result, your application should use password hints and store them alongside the password digest within the database.

• Validating user input. Where user input is passed to SQL commands, for example as string literals in comparison or pattern matching statements, great care should be taken to validate the input, to ensure that the resulting commands do not contain syntax errors and also to ensure that a hacker cannot cause your application to run arbitrary SQL commands. Validating the supplied user name during a logon process is particularly vital as your application's security model is entirely dependent on being able to correctly and securely authenticate users.

  For more information about validating user input for SQL commands and for validation functions, see "SQL Injection Attacks" in Chapter 12, "Data Access Security."

Summary of Steps

This How To includes the following steps:

• Step 1. Create a Web Application with a Logon Page
• Step 2. Configure the Web Application for Forms Authentication
• Step 3. Develop Functions to Generate a Hash and Salt value
• Step 4. Create a User Account Database
• Step 5. Use ADO.NET to Store Account Details in the Database
• Step 6. Authenticate User Credentials against the Database
• Step 7. Test the Application

Step 1. Create a Web Application with a Logon Page

This procedure creates a simple C# Web application that contains a logon page that allows a user to enter a username and password.

To create a Web application with a logon page

1. Start Visual Studio .NET and create a new C# ASP.NET Web application called FormsAuthSQL.

2. Use Solution Explorer to rename WebForm1.aspx to Logon.aspx

3. Add the controls listed in Table 1 to Logon.aspx to create a simple logon form.

   Table 1: Logon.aspx controls

   Control Type	Text	ID
   Label	User Name:	-
   Label	Password	-
   Text Box	-	txtUserName
   Text Box	-	txtPassword
   Button	Register	btnRegister
   Button	Logon	btnLogon
   Label	-	lblMessage

   Your Web page should resemble the one illustrated in Figure 1.

   

   Figure 1. Logon page Web form

4. Set the TextMode property of the txtPassword to Password.

Step 2. Configure the Web Application for Forms Authentication

This procedure edits the application's Web.config file to configure the application for Forms authentication.

To configure the Web application for Forms authentication

1. Use Solution Explorer to open Web.config.

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

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

   <authentication mode="Forms">
     <forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60" 
       path="/">
     </forms>
   </authentication>
   

4. Add the following <authorization> element beneath the <authentication> element. This will allow only authenticated users to access the application. The previously established loginUrl attribute of the <authentication> element will redirect unauthenticated requests to the logon.aspx page.

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

Step 3. Develop Functions to Generate a Hash and Salt value

This procedure adds two utility methods to your Web application; one to generate a random salt value, and one to create a hash based on a supplied password and salt value.

To develop functions to generate a hash and salt value

1. Open Logon.aspx.cs and add the following using statements to the top of the file beneath the existing using statements.

   using System.Security.Cryptography;
   using System.Web.Security;
   

2. Add the following static method to the WebForm1 class to generate a random salt value and return it as a Base 64 encoded string.

   private static string CreateSalt(int size)
   {
     // Generate a cryptographic random number using the cryptographic
     // service provider
     RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
     byte[] buff = new byte[size];
     rng.GetBytes(buff);
     // Return a Base64 string representation of the random number
     return Convert.ToBase64String(buff);
   }
   

3. Add the following static method to generate a hash value based on a supplied password and salt value.

   private static string CreatePasswordHash(string pwd, string salt)
   {
     string saltAndPwd = String.Concat(pwd, salt);
     string hashedPwd = 
           FormsAuthentication.HashPasswordForStoringInConfigFile(
                                                saltAndPwd, "SHA1");
     hashedPwd = String.Concat(hashedPwd, salt);
     return hashedPwd;
   }
   

Step 4. Create a User Account Database

This procedure creates a new user account database in SQL Server that contains a single users table and a stored procedure used to query the user database.

To create a user account database

1. On the Microsoft SQL Server programs menu, click Query Analyzer, and then connect to your local SQL Server.

2. Enter the following SQL script. Note that you must replace "LocalMachine" with your own computer name towards the end of the script.

   USE master
   GO
   -- create a database for the security information
   IF EXISTS (SELECT * FROM   master..sysdatabases WHERE  name =
   'UserAccounts')
     DROP DATABASE UserAccounts
   GO
   CREATE DATABASE UserAccounts
   GO
   USE UserAccounts
   GO
   CREATE TABLE [Users] (
     [UserName] [varchar] (20) NOT NULL ,
     [PasswordHash] [varchar] (50) NOT NULL ,
     CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED
     (
       [UserName]
     )  ON [PRIMARY] 
   ) ON [PRIMARY]
   GO
   -- create stored procedure to register user details
   CREATE PROCEDURE RegisterUser
   @userName varchar(20),
   @passwordHash varchar(50)
   AS
   INSERT INTO Users VALUES(@userName, @passwordHash)
   GO
   -- create stored procedure to retrieve user details
   CREATE PROCEDURE LookupUser
   @userName varchar(20)
   AS
   SELECT PasswordHash 
   FROM Users
   WHERE UserName = @userName
   GO
   -- Add a login for the local ASPNET account
   -- In the following statements, replace LocalMachine with your
   -- local machine name
   exec sp_grantlogin [LocalMachine\ASPNET]
   -- Add a database login for the UserAccounts database for the ASPNET
     account
   exec sp_grantdbaccess [LocalMachine\ASPNET]
   -- Grant execute permissions to the LookupUser and RegisterUser
   -- stored procs
   grant execute on LookupUser to [LocalMachine\ASPNET]
   grant execute on RegisterUser to [LocalMachine\ASPNET]
   

3. Run the query to create the UserAccounts database.

4. Exit Query Manager.

Step 5. Use ADO.NET to Store Account Details in the Database

This procedure modifies the Web application code to store the supplied user name, generated password hash and salt value in the database.

To use ADO.NET to store account details in the database

1. Return to Visual Studio .NET and double-click the Register button on the Web form to create a button click event handler.

2. Add the following code to the method.

   int saltSize = 5;
   string salt = CreateSalt(saltSize);
   string passwordHash = CreatePasswordHash(txtPassword.Text,salt);
   try
   {
     StoreAccountDetails( txtUserName.Text, passwordHash);
   }
   catch(Exception ex)
   {
     lblMessage.Text = ex.Message;
   }
   

3. Add the following using statement at the top of the file, beneath the existing using statements.

   using System.Data.SqlClient;
   

4. Add the StoreAccountDetails utility method using the following code. This code uses ADO.NET to connect to the UserAccounts database and stores the supplied username, password hash and salt value in the Users table.

   private void StoreAccountDetails( string userName,
                                     string passwordHash )
   {
     // See "How To Use DPAPI (Machine Store) from ASP.NET" for
       information 
     // about securely storing connection strings.
     SqlConnection conn = new SqlConnection( "Server=(local);" +
                                             "Integrated 
                                               Security=SSPI;" +
                                             "database=UserAccounts");
   
     SqlCommand cmd = new SqlCommand("RegisterUser", conn );
     cmd.CommandType = CommandType.StoredProcedure;
     SqlParameter sqlParam = null;
     //Usage of Sql parameters also helps avoid SQL Injection attacks.
     sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar,
       20);
     sqlParam.Value = userName;
   
     sqlParam = cmd.Parameters.Add("@passwordHash ", SqlDbType.VarChar,
       50);
     sqlParam.Value = passwordHash;
   
     try
     {
       conn.Open();
       cmd.ExecuteNonQuery();
     }
     catch( Exception ex )
     {
       // Code to check for primary key violation (duplicate account
         name)
       // or other database errors omitted for clarity
       throw new Exception("Exception adding account. " + ex.Message);
     }
     finally
     {
       conn.Close();
     } 
   }
   

Step 6. Authenticate User Credentials Against the Database

This procedure develops ADO.NET code to look up the supplied user name in the database and validate the supplied password, by matching password hashes.

Note   In many Forms authentication scenarios where you are using .NET role-based authorization, you may also retrieve the roles that the user belongs to from the database at this point. These can subsequently be used to generate a GenericPrincipal object that can be associated with authenticated Web requests, for .NET authorization purposes.

For more information about constructing a Forms authentication ticket incorporating a user's role details, see "How To: Create GenericPrincipal Objects with Forms Authentication in ASP.NET 1.1" in the Reference section of this guide.

To authenticate user credentials against the database

1. Return to the Logon.aspx.cs and add the VerifyPassword private helper method as shown in the following code.

   private bool VerifyPassword(string suppliedUserName,
                               string suppliedPassword )
   { 
     bool passwordMatch = false;
     // Get the salt and pwd from the database based on the user name.
     // See "How To: Use DPAPI (Machine Store) from ASP.NET," "How To:
     // Use DPAPI (User Store) from Enterprise Services," and "How To:
     // Create a DPAPI Library" for more information about how to use
     // DPAPI to securely store connection strings.
     SqlConnection conn = new SqlConnection( "Server=(local);" +
                                             "Integrated
                                               Security=SSPI;" +
                                             "database=UserAccounts");
     SqlCommand cmd = new SqlCommand( "LookupUser", conn );
     cmd.CommandType = CommandType.StoredProcedure;
     //Usage of Sql parameters also helps avoid SQL Injection attacks.
     SqlParameter sqlParam = cmd.Parameters.Add("@userName",
                                                SqlDbType.VarChar,
                                                  20);
     sqlParam.Value = suppliedUserName;
     try
     {
       conn.Open();
       SqlDataReader reader = cmd.ExecuteReader();
       reader.Read(); // Advance to the one and only row
       // Return output parameters from returned data stream
       string dbPasswordHash = reader.GetString(0);
       int saltSize = 5;
       string salt = 
         dbPasswordHash.Substring(dbPasswordHash.Length - saltSize);
       reader.Close();
       // Now take the password supplied by the user
       // and generate the hash.
       string hashedPasswordAndSalt =
         CreatePasswordHash(suppliedPassword, salt);
       // Now verify them.
       passwordMatch = hashedPasswordAndSalt.Equals(dbPasswordHash);
     }
     catch (Exception ex)
     {
       throw new Exception("Execption verifying password. " +
         ex.Message);
     }
     finally
     {
       conn.Close();
     }
     return passwordMatch;
   }
   

Step 7. Test the Application

This procedure tests the application. You will register a user, which results in the user name, password hash and salt value being added to the Users table in the UserAccounts database. You will then log on the same user to ensure the correct operation of the password verification routines.

To test the application

1. Return to the Logon form and double-click the Logon button to create a button click event handler.

2. Add the following code to the Logon button click event handler to call the VerifyPassword method and display a message based on whether or not the supplied user name and password are valid.

   bool passwordVerified = false;
   try
   {
      passwordVerified = 
        VerifyPassword(txtUserName.Text,txtPassword.Text);
   }
   catch(Exception ex)
   {
     lblMessage.Text = ex.Message;
     return;
   }
   if (passwordVerified == true )
   {
     // The user is authenticated
     // At this point, an authentication ticket is normally created
     // This can subsequently be used to generate a GenericPrincipal
     // object for .NET authorization purposes
     // For details, see "How To: Use Forms authentication with 
     // GenericPrincipal objects
     lblMessage.Text = "Logon successful: User is authenticated";
   }
   else
   {
     lblMessage.Text = "Invalid username or password";
   }
   

3. On the Build menu, click BuildSolution.

4. In Solution Explorer, right-click logon.aspx, and then click View in Browser.

5. Enter a user name and password, and then click Register.

6. Use SQL Server Enterprise Manager to view the contents of the Users table. You should see a new row for the new user name together with a generated password hash.

7. Return to the Logon Web page, re-enter the password, and then click Logon. You should see the message "Logon successful: User is authenticated."

8. Now enter an invalid password (leaving the user name the same). You should see the message "Invalid username or password."

9. Close Internet Explorer.

Additional Resources

For more information, see the following:

• "How To: Use DPAPI (Machine Store) from ASP.NET 1.1"
• How To: Create GenericPrincipal Objects with Forms Authentication in ASP.NET 1.1"
• "SQL Injection Attacks" in Chapter 12, "Data Access Security"

© Microsoft Corporation. All rights reserved.