Web applications that use Forms authentication often store user credentials (user names and passwords) together with associated role or group lists in Microsoft® SQL Server™ 2000.
This How To describes how to securely look up user names and validate passwords against SQL Server 2000. There are two key concepts for storing user credentials securely:
Storing password digests. For security reasons, passwords should not be stored in clear text 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 an encrypted version of the user’s password in order to avoid the key management issues associated with encryption techniques.
For added security and to mitigate the threat associated with dictionary attacks, the approach described in this How To combines a salt (a cryptographically generated random number) with the password, prior to creating the password hash.
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.
The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs you will need.
Microsoft Windows® 2000 operating system
Microsoft Visual Studio® .NET development system
Microsoft SQL Server 2000
The procedures in this How To also require that you have knowledge of Web development with the Microsoft Visual C#™ development tool.
This How To includes the following procedures:
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
Start Visual Studio .NET and create a new C# ASP.NET Web application called FormsAuthSQL.
Use Solution Explorer to rename WebForm1.aspx to Logon.aspx
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.
Set the TextMode property of the txtPassword to Password.
This procedure edits the application’s Web.config file to configure the application for Forms authentication.
▸ To configure the Web application for Forms authentication
Use Solution Explorer to open Web.config.
Locate the <authentication> element and change the mode attribute to Forms.
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>
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>
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
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;
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); }
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"); return hashedPwd; }
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
On the Microsoft SQL Server programs menu, click Query Analyzer, and then connect to your local SQL Server.
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] (255) NOT NULL , [PasswordHash] [varchar] (40) NOT NULL , [salt] [varchar] (10) 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(255), @passwordHash varchar(40), @salt varchar(10) AS INSERT INTO Users VALUES(@userName, @passwordHash, @salt) GO -- create stored procedure to retrieve user details CREATE PROCEDURE LookupUser @userName varchar(255) AS SELECT PasswordHash, salt 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 [LocalMachineASPNET] -- Add a database login for the UserAccounts database for the ASPNET account exec sp_grantdbaccess [LocalMachineASPNET] -- Grant execute permissions to the LookupUser and RegisterUser stored procs grant execute on LookupUser to [LocalMachineASPNET] grant execute on RegisterUser to [LocalMachineASPNET]
Exit Query Manager.
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
Return to Visual Studio .NET and double-click the Register button on the Web form to create a button click event handler.
Add the following code to the method.
string salt = CreateSalt(5); string passwordHash = CreatePasswordHash(txtPassword.Text,salt); try { StoreAccountDetails( txtUserName.Text, passwordHash, salt); } catch(Exception ex) { lblMessage.Text = ex.Message; }
Add the following using statement at the top of the file, beneath the existing using statements.
using System.Data.SqlClient;
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, string salt ) { // 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; sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar, 255); sqlParam.Value = userName; sqlParam = cmd.Parameters.Add("@passwordHash ", SqlDbType.VarChar, 40); sqlParam.Value = passwordHash; sqlParam = cmd.Parameters.Add("@salt", SqlDbType.VarChar, 10); sqlParam.Value = salt; 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(); } }
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.
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: Use Forms Authentication with GenericPrincipal Objects" in the Reference section of this book.
▸ To authenticate user credentials against the database
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; SqlParameter sqlParam = cmd.Parameters.Add("@userName", SqlDbType.VarChar, 255); 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); string salt = reader.GetString(1); reader.Close(); // Now take the salt and the password entered by the user // and concatenate them together. string passwordAndSalt = String.Concat(suppliedPassword, salt); // Now hash them string hashedPasswordAndSalt = FormsAuthentication.HashPasswordForStoringInConfigFile( passwordAndSalt, "SHA1"); // Now verify them. passwordMatch = hashedPasswordAndSalt.Equals(dbPasswordHash); } catch (Exception ex) { throw new Exception("Execption verifying password. " + ex.Message); } finally { conn.Close(); } return passwordMatch; }
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
Return to the Logon form and double-click the Logon button to create a button click event handler.
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"; }
On the Build menu, click Build Solution.
In Solution Explorer, right-click logon.aspx, and then click View in Browser.
Enter a user name and password, and then click Register.
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.
Return to the Logon Web page, re-enter the password, and then click Logon. You should see the message "Logon successful: User is authenticated."
Now enter an invalid password (leaving the user name the same). You should see the message "Invalid username or password."
Close Internet Explorer.
For more information, see the following:
"Appendix "
"How To: Use Forms Authentication with GenericPrincipal Objects"