What is ASPNetSqlProvider? and How to provide your custom Role provider?

March 3, 2009 by MK
Filed under ASP.NET

While developing a ASP.NET web application you might have come across ASPNetSqlProvider a number of times. This provider stores the membership information. In other words if you are building a user login based application, and you are using the built in ASP.NET Configuration Manager, then you are using ASPNetSqlProvider.

The AspNetSqlProvider is what stores the membership (users), roles, provider information. This defaults to an SQL Express database in your applications App_Data directory.

ASP.NET 2.0 includes a number of built-in “building block” application services. They include:

A membership API for managing usernames/passwords and secure credential management.
A roles API that supports mapping users into logical groups.
A profile API for storing arbitrary properties about both authenticated and anonymous users visiting a web site (for example: their zipcode, gender, theme preference, etc)
A personalization API for storing control customization preferences.
A health monitoring API that can track and collect information about the running state and any errors that occur within a web application .
A site navigation API for defining hierarchy within an application and constructing navigation UI (menus, treeviews, bread-crumbs) that can be context specific based on where the current incoming user is in the site.

ASPNetSqlProvider is part of the Roles API. Out of the box, most of the ASP.NET 2.0 application services are configured to use the built-in SQL Express provider. This provider will automatically create and provision a new database for you the first time you use one of these application services, and provides a pretty easy way to get started.

If you want to use a SQL Server 2000 or 2005 database instance instead of SQL Express then There is a free utility that you can use.

Following are the steps to use a custom SQL Server 2000 or 2005 database instance -

Open a command-line window on your system and run the aspnet_regsql.exe utility that is installed with ASP.NET 2.0 in under your C:\WINDOWS\Microsoft.NET\Framework\v2.0.xyz directory.
Just follow the steps on the next screens to extract the schema to your SQL Database.

Once the above mentioned is done you can use the following settings in your web.config to override the default AspNetSqlProvider settings in machine.config

<roleManager enabled=”true” defaultProvider=”AspNetSqlRoleProvider”>
      <providers>
        <remove name=”AspNetSqlRoleProvider”/>
        <add connectionStringName=”myconn”
applicationName=”YourAppName” name=”AspNetSqlRoleProvider”
type=”System.Web.Security.SqlRoleProvider” />
      </providers>
    </roleManager>

[NOTE: Failure to override default AspNetSqlProvider might get you following error:]

Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

Line 148: <providers>

Line 149: <add name=”AspNetSqlRoleProvider” connectionStringName=”LocalSqlServer” applicationName=”/” type=”System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”/>

Line 150: <add name=”AspNetWindowsTokenRoleProvider” applicationName=”/” type=”System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”/>

Line 151: </providers>

If you look at your computer’s Machine.Config file which can be found at following path -

(C:\WINDOWS\Microsoft.Net\Framework\<version>\CONFIG\machine.config)

you’ll see that the default configuration for the membership providers references a connection string labeled “LocalSqlServer”. If you do not override this configuration in your web.config file, your ASP.NET app will try to use these settings to access membership resources.

To fix this, you need to do one of two things:

Update your machine.config to make “LocalSqlServer” point to the correct database where you installed the membership objects
Add settings to your web.config to override your machine.config. Those settings may look something like this:

<membership>
<providers>
       <clear/>
<add name=”AspNetSqlMembershipProvider” type=”System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”                 connectionStringName=”DbConnString” enablePasswordRetrieval=”false” enablePasswordReset=”true” requiresQuestionAndAnswer=”false” applicationName=”/” requiresUniqueEmail=”true” passwordFormat=”Hashed” maxInvalidPasswordAttempts=”15″ minRequiredPasswordLength=”4″ minRequiredNonalphanumericCharacters=”0″ passwordAttemptWindow=”10″                 passwordStrengthRegularExpression=”"/>
      </providers>
</membership>