Membership Providers


Microsoft ASP.NET 2.0 Providers: Introduction
Membership Providers
Role Providers
Site Map Providers
Session State Providers
Profile Providers
Web Event Providers
Web Parts Personalization Providers


Membership providers provide the interface between Microsoft ASP.NET's membership service and membership data sources. ASP.NET 2.0 ships with two membership providers:

  • SqlMembershipProvider, which stores membership data in Microsoft SQL Server and SQL Server Express databases
  • ActiveDirectoryMembershipProvider, which retrieves membership data from Microsoft Active Directory

The fundamental job of a membership provider is to manage the data regarding a site's registered users, and to provide methods for creating users, deleting users, verifying login credentials, changing passwords, and so on. The Microsoft .NET Framework's System.Web.Security namespace includes a class named MembershipUser that defines the basic attributes of a membership user, and that a membership provider uses to represent individual users. It also includes a base class named MembershipProvider that defines the basic characteristics of a membership provider. MembershipProvider is prototyped as follows:

public abstract class MembershipProvider : ProviderBase
    // Abstract properties
    public abstract bool EnablePasswordRetrieval { get; }
    public abstract bool EnablePasswordReset { get; }
    public abstract bool RequiresQuestionAndAnswer { get; }
    public abstract string ApplicationName { get; set; }
    public abstract int MaxInvalidPasswordAttempts { get; }
    public abstract int PasswordAttemptWindow { get; }
    public abstract bool RequiresUniqueEmail { get; }
    public abstract MembershipPasswordFormat PasswordFormat { get; }
    public abstract int MinRequiredPasswordLength { get; }
    public abstract int MinRequiredNonAlphanumericCharacters { get; }
    public abstract string PasswordStrengthRegularExpression { get; }

    // Abstract methods
    public abstract MembershipUser CreateUser (string username, 
        string password, string email, string passwordQuestion, 
        string passwordAnswer, bool isApproved, object providerUserKey,
        out MembershipCreateStatus status);

    public abstract bool ChangePasswordQuestionAndAnswer
        (string username, string password,
        string newPasswordQuestion, string newPasswordAnswer);

    public abstract string GetPassword (string username,
      string answer);

    public abstract bool ChangePassword (string username,
        string oldPassword, string newPassword);

    public abstract string ResetPassword (string username,
        string answer);

    public abstract void UpdateUser (MembershipUser user);

    public abstract bool ValidateUser (string username,
        string password);

    public abstract bool UnlockUser (string userName);

    public abstract MembershipUser GetUser (object providerUserKey,
        bool userIsOnline);

    public abstract MembershipUser GetUser (string username,
        bool userIsOnline);

    public abstract string GetUserNameByEmail (string email);

    public abstract bool DeleteUser (string username,
        bool deleteAllRelatedData);

    public abstract MembershipUserCollection GetAllUsers
        (int pageIndex, int pageSize, out int totalRecords);

    public abstract int GetNumberOfUsersOnline ();

    public abstract MembershipUserCollection FindUsersByName
        (string usernameToMatch, int pageIndex, int pageSize,
        out int totalRecords);

    public abstract MembershipUserCollection FindUsersByEmail
        (string emailToMatch, int pageIndex, int pageSize,
        out int totalRecords);

    // Virtual methods
    protected virtual byte[] EncryptPassword (byte[] password);
    protected virtual byte[] DecryptPassword (byte[] encodedPassword);
    protected virtual void OnValidatingPassword
        (ValidatePasswordEventArgs e);

    // Events
    public event MembershipValidatePasswordEventHandler

The following sections document the implementation of SqlMembershipProvider, which derives from MembershipProvider.


SqlMembershipProvider is the Microsoft membership provider for SQL Server databases. It stores membership data using the schema documented in "Data Schema," and it uses the stored procedures documented in "Data Access." All knowledge of the database schema is hidden in the stored procedures, so porting SqlMembershipProvider to other database types requires little more than modifying the stored procedures. (Depending on the targeted database type, the ADO.NET code used to call the stored procedures might have to change, too. The Microsoft Oracle .NET provider, for example, uses a different syntax for named parameters.)

The ultimate reference for SqlMembershipProvider is the SqlMembershipProvider source code, which is found in SqlMembershipProvider.cs. The sections that follow highlight key aspects of SqlMembershipProvider's design and operation.

Provider Initialization

Initialization occurs in SqlMembershipProvider.Initialize, which is called one time—when the provider is loaded—by ASP.NET. SqlMembershipProvider.Initialize's duties include:

  • Initializing the various SqlMembershipProvider properties such as EnablePasswordRetrieval and EnablePasswordReset from the corresponding configuration attributes (enablePasswordRetrieval, enablePasswordReset, and so on).
  • Performing common-sense checks on the property values—for example, throwing an exception if PasswordFormat is "hashed" (MembershipPasswordFormat.Hashed) but EnablePasswordRetrieval is true. (By definition, passwords can't be computed from password hashes.)
  • Throwing an exception if unrecognized configuration attributes remain after all supported configuration attributes are processed.

SqlMembershipProvider.Initialize also reads the connection string identified by the connectionStringName attribute from the <connectionStrings> configuration section, and caches it in a private field. It throws a ProviderException if the attribute is empty or nonexistent, or if the attribute references a nonexistent connection string.

Data Schema

SqlMembershipProvider stores membership data in the aspnet_Membership table of the provider database. Each record in aspnet_Membership corresponds to one membership user. Table 2-1 documents the aspnet_Membership table's schema.

Table 2-1. The aspnet_Membership table

Column Name Column Type Description
ApplicationId uniqueidentifier Application ID
UserId uniqueidentifier User ID
Password nvarchar(128) Password (plaintext, hashed, or encrypted; base-64-encoded if hashed or encrypted)
PasswordFormat int Password format (0=Plaintext, 1=Hashed, 2=Encrypted)
PasswordSalt nvarchar(128) Randomly generated 128-bit value used to salt password hashes; stored in base-64-encoded form
MobilePIN nvarchar(16) User's mobile PIN (currently not used)
Email nvarchar(256) User's e-mail address
LoweredEmail nvarchar(256) User's e-mail address (lowercase)
PasswordQuestion nvarchar(256) Password question
PasswordAnswer nvarchar(128) Answer to password question
IsApproved bit 1=Approved, 0=Not approved
IsLockedOut bit 1=Locked out, 0=Not locked out
CreateDate datetime Date and time this account was created
LastLoginDate datetime Date and time of this user's last login
LastPasswordChangedDate datetime Date and time this user's password was last changed
LastLockoutDate datetime Date and time this user was last locked out
FailedPasswordAttemptCount int Number of consecutive failed login attempts
FailedPasswordAttempt-WindowStart datetime Date and time of first failed login if FailedPasswordAttemptCount is nonzero
FailedPasswordAnswer-AttemptCount int Number of consecutive failed password answer attempts
FailedPasswordAnswer-AttemptWindowStart datetime Date and time of first failed password answer if FailedPasswordAnswerAttemptCount is nonzero
Comment ntext Additional text

The aspnet_Membership table has foreign-key relationships with two other provider database tables: aspnet_Applications (see Table 1-2) and aspnet_Users (see Table 1-3). The aspnet_Membership table's ApplicationId column references the column of the same name in the aspnet_Applications table. (Although this column is not strictly necessary, because the UserId can be used to derive the ApplicationId, the ApplicationId column was added to the aspnet_Membership table to speed up queries and reduce the need to join through to the aspnet_Users table.) aspnet_Membership's UserId column references the column of the same name in the aspnet_Users table. A complete record for a given membership user consists of data corresponding to that user's user ID in the aspnet_Users table, and data corresponding to the same user ID in the aspnet_Membership table. Stored procedures such as aspnet_Membership_GetUserByName pull data from both tables to create MembershipUser objects representing individual users.

Scoping of Membership Data

Websites that register membership providers with identical applicationName attributes share membership data, whereas websites that register membership providers with unique applicationNames do not. To that end, SqlMembershipProvider records an application ID in the ApplicationId field of each record in the aspnet_Membership table. aspnet_Membership's ApplicationId field refers to the field of the same name in the aspnet_Applications table, and each unique applicationName has a corresponding ApplicationId in that table.

Data Access

SqlMembershipProvider performs all database accesses through stored procedures. Table 2-2 lists the stored procedures that it uses.

Table 2-2. Stored procedures used by SqlMembershipProvider

Stored Procedure Description
aspnet_Membership_ChangePassword-QuestionAndAnswer Changes the specified user's password question and answer.
aspnet_Membership_CreateUser Adds a new membership user to the membership database. Records the user in the aspnet_Users and aspnet_Membership tables and, if necessary, adds a new application to the aspnet_Applications table.
aspnet_Membership_FindUsersByEmail Retrieves records from aspnet_Membership table with e-mail addresses matching the specified pattern and with the specified application ID.
aspnet_Membership_FindUsersByName Retrieves records from aspnet_Membership table with user names matching the specified pattern and with the specified application ID.
aspnet_Membership_GetAllUsers Retrieves all users from the aspnet_Membership table with the specified application ID.
aspnet_Membership_GetNumberOfUsersOnline Gets the number of users currently online (those whose last activity dates.
aspnet_Membership_GetPassword Gets the specified user's password data from the database. Used for retrieving passwords with a user-supplied password answer.
aspnet_Membership_GetPasswordWithFormat Gets the specified user's password from the database. Used by the provider to retrieve passwords for performing password comparisons (for example, when ValidateUser needs to validate a password).
aspnet_Membership_GetUserByEmail Given an e-mail address and application ID, retrieves the corresponding record from the aspnet_Membership table.
aspnet_Membership_GetUserByName Given a user name and application ID, retrieves the corresponding record from the aspnet_Membership table.
aspnet_Membership_GetUserByUserId Given a user ID and application ID, retrieves the corresponding record from the aspnet_Membership table.
aspnet_Membership_ResetPassword Resets the specified user's password based on a password answer.
aspnet_Membership_SetPassword Sets the specified user's password to the password input to the stored procedure.
aspnet_Membership_UnlockUser Restores login privileges for the specified user by setting the user's IsLockedOut bit to 0.
aspnet_Membership_UpdateUser Updates the user's last activity date in the aspnet_Users table and e-mail address, comment, is-approved status, and last login date in the aspnet_Membership table.
aspnet_Membership_UpdateUserInfo Updates account locking data for the specified user in the aspnet_Users and aspnet_Membership tables. Used in conjunction with provider methods that track bad password and bad password-answer attempts.
aspnet_Users_CreateUser Adds a user to the aspnet_Users table. Called by aspnet_Membership_CreateUser.
aspnet_Users_DeleteUser Deletes a user from the aspnet_Membership table and optionally from other SQL provider tables, including aspnet_Users.

Stored procedure names are generally indicative of the SqlMembershipProvider methods that call them. For example, applications call the membership service's Membership.CreateUser method to register new users. Membership.CreateUser, in turn, delegates to the CreateUser method of the default membership provider, which, in the case of SqlMembershipProvider, validates the input parameters and calls aspnet_Membership_CreateUser to register a new user.

Creating Membership Users

SqlMembershipProvider.CreateUser calls the stored procedure aspnet_Membership_CreateUser to create new membership users. Before calling the stored procedure, SqlMembershipProvider.CreateUser validates the input parameters, encodes the password (and, if present, the password answer) provided to it, and fires an OnValidatingPassword event. Then aspnet_Membership_CreateUser performs the following tasks:

  1. Calls the stored procedure aspnet_Applications_CreateApplication to convert the application name passed to it (which comes from the provider's ApplicationName property) into an application ID. If the application name already appears in the aspnet_Applications table, aspnet_Applications_CreateApplication returns the existing application ID. If the application name is not already present in the aspnet_Applications table, aspnet_Applications_CreateApplication adds a new application to aspnet_Applications and returns the application ID.
  2. Calls aspnet_Users_CreateUser to insert a record representing the new user into the aspnet_Users table.
  3. Performs an optional check to ensure that the new user's e-mail address is unique with respect to other registered e-mail addresses.
  4. Updates the LastActivityDate field in the aspnet_Users table with the current time and date.
  5. Inserts a record representing the new user into the aspnet_Membership table.

aspnet_Membership_CreateUser performs all these steps within a transaction to ensure that changes are committed as a group or not at all.

Deleting Membership Users

Applications call the membership service's Membership.DeleteUser method to delete membership users. Membership.DeleteUser calls the default membership provider's DeleteUser method, which takes a user name as input and also accepts a bool named deleteAllRelatedData that specifies whether other data associated with the specified user should be deleted in addition to membership data. "Other data" includes role data, profile data (including anonymous profile data—more on this later), and Web Parts personalization data.

SqlMembershipProvider.DeleteUser calls the stored procedure aspnet_Users_DeleteUser to delete membership users. In addition to accepting a user name, aspnet_Users_DeleteUser accepts a bit mask named @TablesToDeleteFrom that specifies which provider database tables the user should be deleted from. If deleteAllRelatedData is false, SqlMembershipProvider.DeleteUser passes a bit mask of 1, prompting aspnet_Users_DeleteUser to delete the user only from the aspnet_Membership table. However, if deleteAllRelatedData is true, SqlMembershipProvider.DeleteUser passes a bit mask of 15 (binary 1111), prompting aspnet_Users_DeleteUser to delete the specified user from the aspnet_Membership, aspnet_UsersInRoles, aspnet_Profile, aspnet_PersonalizationPerUser, and aspnet_Users tables. aspnet_Users_DeleteUser uses a database transaction to ensure that the deletions are performed in whole or not at all.

Another little known fact is that Membership.DeleteUser can be used to clean up the records that accrue in the aspnet_Users and aspnet_Profile tables when using the anonymous identification feature to store profile data on behalf of anonymous users. Simply call Membership.DeleteUser with deleteAllRelatedData set to true, and username set to Request.AnonymousID. This deletes the anonymous user's data from the aspnet_Profile table, and it deletes the base user record from aspnet_Users.

Validating Membership Users

Applications call the membership service's Membership.ValidateUser method to validate membership users—that is, to verify that a given user name and password corresponds to a registered membership user. Membership.ValidateUser calls the default membership provider's ValidateUser method, which returns true or false, indicating whether the user name and password are valid.

SqlMembershipProvider.ValidateUser performs the following tasks:

  1. Calls the stored procedure aspnet_Membership_GetPasswordWithFormat to retrieve the user's password from the database. If the password is hashed or encrypted, it is returned in encoded ("formatted") form; otherwise, it's returned as plaintext.
  2. Encodes the password input to ValidateUser using the same encoding, if any, used to encode the password retrieved in the previous step.
  3. Compares the password retrieved from the database to the encoded input password.
  4. If the passwords match, ValidateUser raises an AuditMembershipAuthenticationSuccess Web event, increments a performance counter that tracks successful logins, and returns true.
  5. If the passwords don't match, ValidateUser raises an AuditMembershipAuthenticationFailure Web event, increments a performance counter that tracks failed logins, and returns false. It also calls aspnet_Membership_UpdateUserInfo to update the aspnet_Membership table with information about the failed login, so that the account can be locked if too many failed logins occur within the time span indicated by the provider's PasswordAttemptWindow property.

Account locking is a feature of SqlMembershipProvider that provides a safeguard against password guessing. It is described in "Account Locking."

Password Protection

Applications that store user names, passwords, and other authentication information in a database should never store passwords in plaintext, lest the database be stolen or compromised. To that end, SqlMembershipProvider supports three storage formats ("encodings") for passwords and password answers. The provider's PasswordFormat property, which is initialized from the passwordFormat configuration attribute, determines which format is used:

  • MembershipPasswordFormat.Clear, which stores passwords and password answers in plaintext.
  • MembershipPasswordFormat.Hashed (the default), which stores salted hashes generated from passwords and password answers. The salt is a random 128-bit value generated by the .NET Framework's RNGCryptoServiceProvider class. Each password/password answer pair is salted with this unique value, and the salt is stored in the aspnet_Membership table's PasswordSalt field. The result of hashing the password and the salt is stored in the Password field. Similarly, the result of hashing the password answer and the salt is stored in the PasswordAnswer field.
  • MembershipPasswordFormat.Encrypted, which stores encrypted passwords and password answers. SqlMembershipProvider encrypts passwords and password answers using the symmetric encryption/decryption key specified in the <machineKey> configuration section's decryptionKey attribute, and the encryption algorithm specified in the <machineKey> configuration section's decryption attribute. SqlMembershipProvider throws an exception if it is asked to encrypt passwords and password answers, and if decryptionKey is set to Autogenerate. This prevents a membership database containing encrypted passwords and password answers from becoming invalid if moved to another server or another application.

Note   Storing unsalted password hashes leaves password databases vulnerable to dictionary attacks. SqlMembershipProvider salts password hashes as a hedge against such attacks. However, the fact that SqlMembershipProvider stores salts in the database alongside the password hashes means that the salt's effective key space is a function not of the length of the salt, but of the number of salts in the database. In other words, the more records the aspnet_Membership table contains, the more secure the password hashes.

If desired, a custom membership provider could use an altogether different strategy for storing salts—one whose security did not depend on the volume of membership data. It could, for example, use the same randomly generated salt for every hash, but protect the salt by storing it outside the database—perhaps in an ACLed registry key.

To provide additional protection against password hacking, SqlMembershipProvider also supports user-configurable password strengths. CreateUser and other methods that modify passwords (ChangePassword and ResetPassword) validate the passwords against the provider's MinRequiredPasswordLength and MinRequiredNonAlphanumericCharacters properties. SqlMembershipProvider defaults these properties to 7 and 1, respectively. In addition, SqlMembershipProvider validates passwords against the regular expression, if any, stored in the PasswordStrengthRegularExpression property, as follows:

if( PasswordStrengthRegularExpression.Length > 0 )
    if( !Regex.IsMatch( password, PasswordStrengthRegularExpression ) )
        status = MembershipCreateStatus.InvalidPassword;
        return null;

The combination of non-plaintext password storage formats and user-configurable password strengths enables SqlMembershipProvider to store passwords as securely as is practically possible.

Account Locking

To guard against password guessing, SqlMembershipProvider supports the automatic locking of accounts that incur suspicious activity. If, for a given user, the number of consecutive invalid passwords or password answers submitted to methods such as ValidateUser and GetPassword exceeds the value stored in the provider's MaxInvalidPasswordAttempts property, and if the consecutive attempts occur within the time period specified by the PasswordAttemptWindow property, SqlMembershipProvider sets the corresponding IsLockedOut field in the aspnet_Membership table to 1. Further logins by the affected user are disallowed until IsLockedOut is reset to 0 by calling the provider's UnlockUser method. SqlMembershipProvider defaults MaxInvalidPasswordAttempts to 5 and PasswordAttemptWindow to 10 (that is, 10 minutes).

As an example of how account locking is implemented, suppose a user submits a password from a login page that uses a Login control to validate logins. Login controls call Membership.ValidateUser. Membership.ValidateUser calls SqlMembershipProvider.ValidateUser (assuming SqlMembershipProvider is the default membership provider), which calls the private SqlMembershipProvider.CheckPassword method to validate passwords. CheckPassword uses aspnet_Membership_GetPasswordWithFormat to retrieve an encoded password. The stored procedure checks the IsLockedOut bit of the record it retrieves from the database, and returns an error code of 99 if IsLockedOut is set. The error code causes CheckPassword to return false. That causes ValidateUser to return false, which in turn prevents the user from logging in—even if the password he or she typed was valid.

How does an account become locked in the first place? Suppose the user types an incorrect password into the login page. After ascertaining that the password is invalid, CheckPassword calls the stored procedure aspnet_Membership_UpdateUserInfo to update the corresponding record in the aspnet_Membership table. It passes in a bit flag indicating an invalid password was submitted. Seeing the flag, the stored procedure increments the failed password attempt count. If the count exceeds the maximum specified by MaxInvalidPasswordAttempts, and if all the password failures occurred within the time window specified by PasswordAttemptWindow, the stored procedure sets IsLockedOut to 1, effectively locking the account until further notice. Thus, locking is handled primarily at the database level, and it is largely opaque to the provider itself.

Differences Between the Published Source Code and the .NET Framework's SqlMembershipProvider

The published source code for SqlMembershipProvider differs from the .NET Framework version in the following respects:

  • Declarative and imperative CAS demands were commented out. Because the source code can be compiled standalone, and thus will run as user code rather than trusted code in the global assembly cache, the CAS demands are not strictly necessary. For reference, however, the original demands from the .NET Framework version of the provider have been retained as comments.
  • The performance counter and Web event code in ValidateUser has been commented out, because the .NET Framework provider relies on internal helper classes to manipulate these counters. For reference, the original code has been retained as comments.
  • The internal helper methods EncodePassword and UnEncodePassword have been included in the accompanying source code. In the .NET Framework, these are actually internal helper methods implemented by the base MembershipProvider type.

Return to the Introduction.

Go on to part 3, Role Providers.

© Microsoft Corporation. All rights reserved.