Microsoft ASP.NET 2.0 Providers: Introduction

Click here to download ProviderToolkitSamples.msi.

Introduction

Microsoft ASP.NET 2.0 includes a number of services that store state in databases and other storage media. For example, the session state service manages per-user session state by storing it in-process (in memory in the application domain of the host application), in memory in an external process (the "state server process"), or in a Microsoft SQL Server database, whereas the membership service stores user names, passwords, and other membership data in Microsoft SQL Server or Microsoft Active Directory.

These and other state management services in ASP.NET 2.0 use the provider model pictured in Figure 1-1 to maximize storage flexibility. Providers abstract storage media in much the same way that device drivers abstract hardware devices. The membership service is equally at home using SQL Server or Active Directory, because ASP.NET 2.0 includes providers for each. Moreover, ASP.NET 2.0 can be extended with custom providers to add support for Web services, Oracle databases, SQL Server databases with custom schemas, and other media not supported by the built-in providers.

Figure 1-1. The ASP.NET 2.0 provider model

Table 1-1 lists the providers that are included with ASP.NET 2.0.

Table 1-1. ASP.NET 2.0 providers

Provider Type	Built-In Provider(s)
Membership	System.Web.Security.ActiveDirectoryMembershipProvider System.Web.Security.SqlMembershipProvider
Role management	System.Web.Security.AuthorizationStoreRoleProvider System.Web.Security.SqlRoleProvider System.Web.Security.WindowsTokenRoleProvider
Site map	System.Web.XmlSiteMapProvider
Profile	System.Web.Profile.SqlProfileProvider
Session state	System.Web.SessionState.InProcSessionStateStore System.Web.SessionState.OutOfProcSessionStateStore System.Web.SessionState.SqlSessionStateStore
Web events	System.Web.Management.EventLogWebEventProvider System.Web.Management.SimpleMailWebEventProvider System.Web.Management.TemplatedMailWebEventProvider System.Web.Management.SqlWebEventProvider System.Web.Management.TraceWebEventProvider System.Web.Management.WmiWebEventProvider
Web Parts personalization	System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider
Protected configuration	System.Configuration.DPAPIProtectedConfigurationProvider System.Configuration.RSAProtectedConfigurationProvider

This whitepaper documents the design and operation of many of the built-in providers.

It supplements the providers' source code and it contains helpful insights for developers writing custom providers of their own. For more information on the ASP.NET 2.0 provider model, and for guidance on writing custom providers, visit ASP.NET 2.0 Provider Model: Introduction to the Provider Model.

The SQL Provider Database

Many of the Microsoft ASP.NET 2.0 providers are SQL providers—providers that persist state in SQL Server (or SQL Server Express) databases. The SQL providers include SqlMembershipProvider, SqlRoleProvider, SqlProfileProvider, SqlSessionStateStore, SqlWebEventProvider, and SqlPersonalizationProvider. Each stores data using a predefined schema. The Aspnet_regsql.exe tool that comes with ASP.NET 2.0 creates a SQL Server database with a compatible schema. That database, which is named aspnetdb by default, will hereafter be referred to as the SQL provider database or simply the provider database.

Figure 1-2 shows the structure of the SQL provider database. Some of the tables are provider-specific. The aspnet_Membership table, for example, is used exclusively by SqlMembershipProvider, whereas the aspnet_Roles and aspnet_UsersInRoles tables are used exclusively by SqlRoleProvider.

Figure 1-2. The SQL provider database (Click on the image for a larger picture)

Other tables are not provider-specific, but instead exist for the benefit of multiple SQL providers. The aspnet_Applications table is a great example. Many SQL providers support scoping of data through the ApplicationName property, which is initialized from the applicationName configuration attribute supported by many providers . For example, websites that register membership providers with identical applicationName attributes share membership data, whereas websites that register membership providers with unique applicationNames do not. SQL providers that support ApplicationName scoping do so by storing application IDs associated with the records that they create, and by including those application IDs in queries performed on the SQL provider database. Application IDs stored in aspnet_Membership, aspnet_Paths, and other provider-specific tables refer to the aspnet_Applications table, which contains a list of extant application IDs and the corresponding application names. Table 1-2 documents the schema of the aspnet_Applications table. The provider database contains a stored procedure named aspnet_Applications_CreateApplication that providers (or stored procedures) can call to retrieve an application ID from the aspnet_Applications table, or to create a new one if the specified application doesn't exist.

Table 1-2. The aspnet_Applications table

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
ApplicationName	nvarchar(256)	Application name
LoweredApplicationName	nvarchar(256)	Application name (lowercase)
Description	nvarchar(256)	Application description

aspnet_Users is another example of a table that's shared by SQL providers. It stores core provider-agnostic information regarding users, including user names and user IDs. SqlMembershipProvider stores membership-user data in the aspnet_Membership table, but that table contains a UserId column that refers to the column of the same name in aspnet_Users. Similarly, SqlRoleProvider stores data mapping users to roles in the aspnet_UsersInRoles table, and that table contains both a UserId column referring to the column of the same name in aspnet_Users, and a RoleId column referring to the column of the same name in aspnet_Roles. Table 1-3 documents the schema of the aspnet_Users table.

Table 1-3. The aspnet_Users table

Column Name	Column Type	Description
ApplicationId	uniqueidentifier	Application ID
UserId	uniqueidentifier	User ID
UserName	nvarchar(256)	User name
LoweredUserName	nvarchar(256)	User name (lowercase)
MobileAlias	nvarchar(16)	User's mobile alias (currently not used)
IsAnonymous	bit	1=Anonymous user, 0=Not an anonymous user
LastActivityDate	datetime	Date and time of last activity by this user

Note   Developers are sometimes surprised to find that the aspnet_Users table's UserName column contains alphanumeric identifiers (GUIDs) as well as string user names. Records containing GUIDs for user names are created when SqlProfileProvider or SqlPersonalizationProvider persists data on behalf of anonymous users.

The SQL providers never access tables in the provider database directly. Instead, they use stored procedures. When SqlMembershipProvider's CreateUser method is called, for example, it calls a stored procedure named aspnet_Membership_CreateUser to add a new membership user to the provider database. aspnet_Membership_CreateUser adds a record representing that user to the aspnet_Membership table, another record representing that user to the aspnet_Users table, and, if necessary, a record denoting a new application to the aspnet_Applications table. The use of stored procedures hides the database schema from the provider, which simplifies porting SQL providers to other database types (for example, Oracle databases), and to SQL Server databases that utilize custom schemas. Stored procedures that perform multistep updates typically use database transactions to roll back changes if an error occurs before the last step is completed. (There are a few cases in which providers manage transactions themselves in order to support batch deletes.)

SQL Server Express

Rather than use a pre-existing SQL provider database, the Microsoft SQL providers are equally happy to use a database managed by SQL Server Express, herafter referred to as the express database.

Internally, the express database has the same schema as the SQL provider database. The difference between the databases lies in how they're created. The SQL provider database is created externally when you run Aspnet_regsql.exe or an equivalent tool. The express database is created automatically the first time it's needed.

Each Microsoft SQL provider (with the exception of SqlSessionStateStore, which doesn't support express databases) has logic built in to automatically create the express database. The logic lives in a helper class named SqlConnectionHelper. Rather than create SqlConnections from raw connection strings, Microsoft SQL providers pass connection strings to SqlConnectionHelper.GetConnection, as follows:

Copy Code

SqlConnectionHolder holder = SqlConnectionHelper.GetConnection( _sqlConnectionString, true );

SqlConnectionHelper.GetConnection parses the connection string and automatically creates the express database if the connection string meets certain criteria, and if the database doesn't already exist.

Note   When a Microsoft SQL provider needs an actual SqlConnection, it extracts it from the Connection property of the SqlConnectionHolder, as follows:

Copy Code

SqlCommand cmd = new SqlCommand("dbo.aspnet_Membership_CreateUser", holder.Connection);

Similarly, it closes the connection by calling SqlConnectionHolder.Close.

The main purpose of the SqlConnectionHolder class is to simplify the security model when SQL providers are used in a website with client impersonation enabled. SqlConnectionHolder encapsulates logic that temporarily reverts the thread identity to that of the current process identity or application impersonation identity when connecting to SQL Server. As a result, SQL providers run with a trusted subsystem model that doesn't require individual users to have access rights to the provider database.

The default LocalSqlServer connection string in Machine.config is an excellent example of a connection string that results in automatic creation of the express database:

Copy Code

data source=./SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

The presence of User Instance=true and AttachDBFilename=|DataDirectory| cause SqlConnectionHelper to conclude that the connection string targets SQL Server Express and triggers the database's creation. (The presence of data source=./SQLEXPRESS in the connection string does not factor into the decision, because SqlConnectionHelper supports non-default as well as default instances of SQL Server Express.) The |DataDirectory| portion of the connection string specifies that the MDF file is located inthe App_Data directory. SqlConnectionHelper derives the database name from the MDF file name. It also creates an App_Data folder to hold the MDF if the folder doesn't already exist.

When SqlConnectionHelper creates the express database, it sets SIZE to 10 (10 MB) and FILEGROWTH to 50%. The database's sort order, case sensitivity, accent sensitivity, and other locale-dependent settings are inherited from the default SQL Server Express instance, which ensures that the database locale is consistent with that of the host computer.

Note   The extra overhead incurred by checking for the existence of the express database before using it means that Microsoft SQL providers run marginally slower against SQL Server Express than SQL Server. Hopefully, the small performance loss is offset by the added convenience of automatically created express databases.