SQL Table Providers

The provider object model, as present in ASP.NET 2.0, is a great step forward from hand-coded behavior in ASP.NET 1.x. But the provider model is only as useful as are available providers. Microsoft .NET itself contains set of providers which can store their data in Microsoft SQL Server database: SqlMembershipProvider, SqlRoleProvider and SqlProfileProvider. However, database structure used by these providers is pretty complicated and almost impossible to interconnect with your own tables etc.

So I created set of my own providers, using very simple table structure. Some functionality of the original providers was lost, especially the ability to store data for several applications in single database. But the simple and straightforward database allows you to easily plug it into your existing infrastructure.

Features

Simplicity, ability to connect to rest of database infrastructure

The main goal of these providers is to use simple table structure, which can be easily connected to other database architecture using foreign keys etc. The tables used are also extendable, additional columns may be created, as long as they are nullable or have default values.

Database independence

The providers are written to be database agnostic, as long as the database has ADO.NET provider and understands SQL and named parameters. Microsoft SQL Server and SQL Compact are the main target databases and the providers are tested to work with them. See the following links for setup instructions:

Account lockdown not supported

The ASP.NET Membership infrastructure supports the account lockdown feature. After several failed login attempts, the account is disabled. In my eyes this feature offers great opportunity for denial of service attack directed to certain users - I can write a program which will repeatedly login with random passwords and deny access to legitimate users. Therefore the functionality is intentionally not implemented in the providers. use other way to protect your application from dictionary attacks.

Differences from Simple SQL Providers

The first generation of providers was quite successful, so I retained the good bits, but the providers are basically rewritten from scratch, in new .NET 4.0.

The new SQL Table providers have several additional features:
  • For hashing, the standard HMACSHA512 class and algo are supported, instead of homebrew salting.
  • Password hash and salt (or key, in HMAC terminology) are stored as binary database fields, not Base64-encoded strings.
  • The int UserId column was dropped and UserName is now used as primary key instead.
  • Implemented better tracking of user activity and now supporting the GetNumberOfUsersOnline method.
  • Names of all tables are now configurable.

The downside of the new password hashing and storage is that there is no upgrade path from the Simple SQL Providers. You can upgrade easily, but the passwords are lost. So in case of upgrading, you need to notify your users and issue new passwords or use some kind of password reset mechanisms.

Configuring the providers in web.config

To use the SQL Table providers, include the following settings in your web.config file:

<configuration>
    <appSettings/>
    <connectionStrings>
        <add name="TableAuthDB" providerName="System.Data.SqlClient" connectionString="..."/>
    </connectionStrings>
    <system.web>
        <membership defaultProvider="MyMembershipProvider">
            <providers>
                <clear/>
                <add name="MyMembershipProvider"
                     type="Altairis.Web.Security.TableMembershipProvider, Altairis.Web.Security"
                     connectionStringName="TableAuthDB" />
            </providers>
        </membership>
        <roleManager enabled="true" defaultProvider="MyRoleProvider">
            <providers>
                <clear/>
                <add name="MyRoleProvider" 
                     type="Altairis.Web.Security.TableRoleProvider, Altairis.Web.Security"
                     connectionStringName="TableAuthDB"/>
            </providers>
        </roleManager>
    </system.web>
</configuration>

Configuring membership provider

The following configuration attributes are supported:
  • Common for all membership providers:
    • applicationName (can be set, but is ignored)
    • minRequiredNonAlphanumericCharacters
    • minRequiredPasswordLength
    • passwordStrengthRegularExpression
    • requiresUniqueEmail
  • Specific for this provider:
    • connectionStringName - name of connection string defined in the connectionStrings section to use for database connectivity.
    • tableName - name of database table to store user information. Default is Users.

Configuring role provider

The following configuration attributes are supported:
  • Common for all role providers:
    • applicationName (can be set, but is ignored)
  • Specific for this provider:
    • connectionStringName - name of connection string defined in the connectionStrings section to use for database connectivity.
    • rolesTableName - name of database table to store roles. Default is Roles.
    • roleMembershipsTableName - name of database table to store information about role members. Default is RoleMemberships.

Last edited Jan 23, 2011 at 9:43 PM by altair, version 3

Comments

IslandTalker Feb 21, 2011 at 6:58 AM 
Nice work, and thanks.

SelArom Jan 11, 2011 at 5:05 PM 
never mind I see it is in the "SimpleSqlSample" of the download, thanks

SelArom Jan 11, 2011 at 5:02 PM 
What about setting up profile providers? how is this done?