Using Forms Authentication with SQL Server in ASP.NET 1.1

Most of the Web applications we have to provide the authentication to check the use credentials to logon to the secured pages in the site. By default IIS provides the windows authentication which doesn't need to write any single line of code instead of some settings in IIS. We can use Forms authentication in ASP.NET to verify the user credentials in order to access the secured pages. But for Forms authentication, we have to write little bit of code, below i have added the setps to setup the forms
authentication.

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

Step 1. 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 to Logon.aspx to create a simple logon form.

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

Figure 1. Logon page Web form

Step 2. 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>

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

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");
hashedPwd = String.Concat(hashedPwd, salt);
return hashedPwd;
}

Step 4. Create a User Account Database

Run the the table Users script in SQL query analyzer to create the table.

CREATE TABLE [Users] (
[UserName] [varchar] (20) NOT NULL ,
[PasswordHash] [varchar] (40) NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserName]
) ON [PRIMARY]
) ON [PRIMARY]

-- create stored procedure to register user details

CREATE PROCEDURE RegisterUser
@userName varchar(20),
@passwordHash varchar(40)
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 UsersWHERE UserName = @userName
GO

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

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.

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

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 )
{
// 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, 40);
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.

To authenticate user credentials against the database

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

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

posted by Vijay Kumar @ 11/13/2006 12:01:00 AM