Introduction

As long as programmers have been developing applications there has been a need to specify who can access an application and what role a given user has. Today's web based applications require us to track additional information, such as shipping addresses for a shopping cart or content preferences for a portal. The .NET 2.0 framework provides new tools to help: the membership manager, role manager, and personalization engine (profile service), all using the aspnetdb database. Visual Studio 2005 and Visual Web Developer Express even provide the Web Site Administration Tool (WAT) for setting up members and assigning roles to them.

You may be responsible for several applications, each one with its own primary data store. The main database for a human resources application will be separate from the main database for a shipping department application. However, you may want to use a shared user account database for managing login information across all applications. This approach will simplify administration and make it easier to implement a single cross-application logon. But if you use a shared user account database you probably don't want all users of any application to have automatic access to all applications. You also don't want each user to have exactly the same roles in each application. Most likely the roles themselves will vary. If you look closely at the WAT you realize that there is no way to specify which application the roles and users belong to. If you set the configuration files in two different applications to use the same aspnetdb database, add users and roles with the WAT while working on one application, and then open the second application in Visual Studio and again run the WAT, you will find that all the same users and roles are visible. The tool makes no distinction between your applications.

In fact the SQLMembershipProvider and the SQLRoleProvider, and the underlying database they use, do support separate applications with distinct users and roles. In this article I will show how they support separate applications and how you can programmatically tell the providers which application to use. I will also discuss a potential pitfall when you have multiple applications running and how to deal with it.

I will start by setting up a project and assigning users and roles to it using WAT. Next I will set up a second project to illustrate how WAT fails to separate the users and roles by application when using a shared user account database (aspnetdb). Moving the discussion to the aspnetdb database, I will show that the SQLMembershipProvider is capable of supporting this separation. I will describe how you can specify different applications in this database, both programmatically and by modifying the configuration files. I will present a simple page, ApplicationMgr.aspx, which can be extended to overcome the limits of the WAT. I will conclude by discussing a limitation in the SQLMembershipProvider's object model and will suggest strategies for overcoming these issues.

System Requirements

To run the code for this sample you should have the following:

• The .NET Framework version 2.0
• Visual Studio 2005 or Visual Web Developer Express
• SQL Server 2000, or SQL Server 2005. The examples will not run as expected on SQL Server Express because each application will have its own copy of the database, and the intent here is to show how one database can be shared by multiple applications.
• The aspnetdb database must be installed on your database server (see instructions below)

Installing and Compiling the Sample Code

The instructions below describe how to setup the environment for the sample application. In machine.config, set requiresQuestionAndAnswer="false" in the <membership><providers> section or else there will be an error in my btnSaveUser_Click subroutine. This subroutine can be coded differently, which will be described later in the article, so that this change will not be necessary in your production code.

<membership>
  <providers>
    <add name="AspNetSqlMembershipProvider"    
    type="System.Web.Security.SqlMembershipProvider, System.Web, 
    Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
    connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" 
    enablePasswordReset="true" requiresQuestionAndAnswer="false" 
      applicationName="/" requiresUniqueEmail="false" 
      passwordFormat="Hashed" maxInvalidPasswordAttempts="5" 
      minRequiredPasswordLength="7" 
      minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"   
      passwordStrengthRegularExpression="" />
  </providers>
</membership>

Start by installing the aspnetdb on your database by running aspnet_regsql script from the command line if this has not already been done. Aspnet_regsql can be found in the Windows\Micrsoft.Net\Framework\v2.0.50727 directory on the boot drive of your web server. v2.0.50727 is the version number of the initial production release of .NET 2.0. Your version may be different if you are using a beta release or a revision to the initial release. When you run aspnet_reqsql without any command line arguments, it will run a wizard that walks you through specifying connection information for the computer running SQL Server.

Next you need to setup two projects, Scheduler and Workflow. Their main purpose is to illustrate the use of the Website Application Tool. The ApplicationMgr.aspx page from the sample code download should be copied into one of these projects. No other .NET code will be required.

A copy of my web.config is also included in the download and you also need to make changes to these web.config files of both projects as follows. First, you need to make a connection string entry to point to the aspnetdb database you have set up. Note the line <remove name="LocalSqlServer" /> removes the LocalSqlServer connection in the machine.config otherwise you will receive a runtime error. Here is the connection string I used:

<connectionStrings>
  <remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=HOME- 
  MADE;Initial Catalog=aspnetdb;Integrated Security=True" 
  providerName="System.Data.SqlClient" />
</connectionStrings >

You will need to adjust this string to match your environment.

Roles will need to be enabled and the authentication mode needs to be set to Forms with these entries in the <system.web> section:

<roleManager enabled="true"/>
<authentication mode="Forms"/>

These entries are necessary for use of the Web Site Administration Tool (WAT) described throughout the rest of the article.

Lastly, run the script in the file spGetApplications.sql to create the stored procedure spGetApplications in the aspnetdb database. The SQL code is presented later in this article.

The Problem

In this section I will illustrate the problem at hand through an example. Start by first creating a project called Scheduler, it has provides the typical scheduling functions that would be used to track resources: perhaps rooms in a hotel, or trainers available to conduct classes. A second project called Workflow should also be created. A common workflow application would move a document through a creation and approval process. Note that we are not going to actually develop these applications, they are used to illustrate the problem I am trying to solve. I will only create the projects and use them to illustrate how the Membership and Role providers handle applications

The table below shows the users and roles for each project I will be creating:

Project	Scheduler	Workflow
Users	Tom Dick Harry	Bob Carol Ted Alice
Roles	Scheduler Administrator Guest	Create Document Review Approve

In Figure 1 you see that I have created the Scheduler project in Visual Studio. To follow along, click on the Website menu option, and then the ASP.Net Configuration submenu item to open the WAT. (The WAT can also be accessed from the toolbar in the Solution Explorer; it is the icon with the tooltip - ASP .Net Configuration.

Figure 1. The Scheduler Project

When the WAT opens (see Figure 2) you see that it shows Scheduler as the current application.

Figure 2. The Web Site Administration Tool (WAT)

Click on either the Security tab or the Security link on the page. (If under the Users and Roles columns you see messages that these features are disabled or not available, you probably did not correctly modify web.config as described in the Installing and Compiling the Sample Code section earlier.) You can see that there are no users or roles defined yet, so you need to create them. Click Create User.

Figure 3. The WAT Security Tab

For our purposes here let's create three users - Tom, Dick, and Harry. You also need three roles - Scheduler, Administrator, and Guest. As with the applications themselves, these specific names and roles are not important; they are only for illustration. Figure 4shows the screen for adding the first user.

Figure 4. Creating a new user

Set up the next two users in similar fashion. Provide whatever information you like for the other fields on the form. Then set up the three roles by clicking Create or Manage Roles. Figure 5 shows the Scheduler role being created.

Figure 5. Adding a Role

Setup the other two roles in similar fashion. When you are done you can see that there are three users and three roles created, as shown in Figure 6

Figure 6. Three roles and three users have been created

Click Manage Users and you'll see the three users. Click Edit Roles in Dick's row and you'll see the three roles. You'll see that Dick doesn't have a role assigned to him yet (Figure 7).

Figure 7. The Users created for the Scheduler project

Now close the WAT and return to Visual Studio.If you haven't already done so, create the second project, Workflow (discussed in the Installing and Compiling the Sample Code section) and open WAT again from the Workflow project. As shown in Figure 8, the application is now Workflow. So far so good.

Figure 8. The Workflow project as seen in the WAT

But here is the problem, you have not entered any users or roles for Workflow, but the screen indicates that there are three of them. Where did they come from?

If you navigate to Manage Users (see Figure 9) you see the same users and the same roles as were entered for the previous application. The information you have entered has not been tied to a specific application.

Figure 9. Workflow shows the same users as did Scheduler

To understand why users and roles are shared you need to understand how they are stored. The SQLMembershipProvider and SQLRoleProvider share the SQL Server database specified in the LocalSqlServer connection string set up in the web.config files.

Figure 10 below is a diagram of the database with only the table names shown. You can see that there are tables for users, membership, roles, plus others that don't concern us now. In the middle of them all is the table aspnet_Applications. From this diagram you can tell that a given application can have many users and many roles, there is a many-to-many relationship between users and roles, and that any given user or role can belong to only one application. And yet, when using the WAT, it appeared that our users and roles belonged to two applications. What is going on?

Figure 10. The database used by the Membership and Roles providers

Below are three of the columns from the aspnet_Users table. They show the three users you entered and all three have the same ApplicationId. Note that the ApplicationId and UserId on your system will be different.

ApplicationId	UserId	UserName
9a29c941-c83e-420e-8b74-5d4016645bf8	f3b902f2-8734-4f21-a57c-95fc2a671b4b	Dick
9a29c941-c83e-420e-8b74-5d4016645bf8	f7d822cc-49d6-47e4-9611-3b23ed9f5263	Harry
9a29c941-c83e-420e-8b74-5d4016645bf8	75049500-67ab-4927-b9f9-7a15afe5eed5	Tom

If you open the aspnet_Applications table you will find this row:

ApplicationName	LoweredApplicationName	ApplicationId
/	/	9a29c941-c83e-420e-8b74-5d4016645bf8

The name of the application is not Scheduler, nor is it Workflow. Where did / come from? The answer lies in the machine.config file, which you can find in the Windows\Micrsoft.Net\Framework\v2.0.50727\Config directory of your web server. v2.0.50727 is the version number of your .NET installation and may be different from what is shown here.

If you look at the <membership /> section in the machine.config file you will see something like this:

<membership>
  <providers>
    <add name="AspNetSqlMembershipProvider" 
      type="System.Web.Security.SqlMembershipProvider, System.Web, 
      Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
      connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" 
      enablePasswordReset="true" requiresQuestionAndAnswer="true" 
      applicationName="/" requiresUniqueEmail="false" asswordFormat="Hashed"
      maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" 
      minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"
      passwordStrengthRegularExpression="" />
  </providers>
</membership>

Notice the applicationName attribute. That is where the / comes from! The same attribute and value appear in the <roleManager /> section:

<roleManager>
  <providers>
    <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer"
      applicationName="/" type="System.Web.Security.SqlRoleProvider, 
      System.Web, Version=2.0.0.0, Culture=neutral, 
      PublicKeyToken=b03f5f7f11d50a3a" />
    <add name="AspNetWindowsTokenRoleProvider" applicationName="/" 
      type="System.Web.Security.WindowsTokenRoleProvider, System.Web, 
      Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  </providers>
</roleManager>

You should now realize two things. First, you can set the application names for Membership and Roles, you don't have to stick with the current / value. Second, the two can be set independently of each other. You may also be thinking that setting the application name in the machine.config is a pretty limited approach. There must be a better way. I'll discuss that next.

Setting Application Name in the Program

Nearly everything in .NET is an object, or a property of an object. You can set the application name by using code similar to the following:

Membership.ApplicationName = "Workflow"
Roles.ApplicationName = "Workflow"

Membership and Roles are static classes that are central to the .NET framework's way of managing users.

Next, I will now create a new page to illustrate the use of the ApplicationName property. This page will be functional and will do some of the things WAT does, but will work with multiple applications. I am creating it initially in the Workflow project. Figure 11 shows the design view.

Figure 11. Design view of the Application Manager Page

Here are the names I assigned to the controls when I created this app. The names will be useful in a moment when I discuss the code:

First Table

• Second Column: lbApps, lbUsers, lbRoles
• Fourth Column: txtNewApp, txtNewUser, txtNewRole
• Fifth Column: btnSaveApp, btnSaveUser, btnSaveRole

Second Table: btnDisplay, lblMemebershipApp, lblRoleApp

And, of course, lblMessage at the bottom.

Be sure to set AutoPostBack=true for lbApps.

This page will display all applications currently in the Membership database in lbApps, all users defined in the currently selected application will be shown in lbUsers, and all roles defined in the current application will be shown in lbRoles. New applications, users, and roles can be entered in the txtNewApp, txtNewUser, and txtNewRole text fields. The corresponding buttons will save the new entries to the database. Clicking btnDisplay will show the current values of Membership.ApplicationName and Roles.ApplicationName. Figure 12 shows how the page will appear with the data already populated.

Figure 12. The Application Manager with data populated

The Application Manager Class

Let's take a look at how the page loads. The first thing the page needs to do is populate the list boxes. I'm taking things a little out of order here and considering lbUsers and lbRoles first.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    Handles Me.Load
  Try
    LoadAppList()
    LoadUserList()
    LoadRoleList()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  End Try
End Sub

Notice that there is no code for setting up a connection or command object, datareader or datatable. All you need is to assign the output of the Membership.GetAllUsers() method to the lbUsers datasource. The statement Membership.ApplicationName = lbApps.SelectedItem.Text tells the Membership class which application you're working with. If you didn't specify an application then you'd get users assigned to the application specified in machine.config, as discussed previously.

Sub LoadUserList()
  Try
    lbUsers.Items.Clear()
    If lbApps.SelectedIndex >= 0 Then
      Membership.ApplicationName = lbApps.SelectedItem.Text
    End If
    lbUsers.DataSource = Membership.GetAllUsers
    lbUsers.DataBind()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  End Try
End Sub

Loading lbRoles is very similar to lbApps. I use the Roles.GetAllRoles() method to provide the roles and after specifying the application.

Sub LoadRoleList()
  Try
    lbRoles.Items.Clear()
    If lbApps.SelectedIndex >= 0 Then
      Roles.ApplicationName = lbApps.SelectedItem.Text
    End If
    lbRoles.DataSource = Roles.GetAllRoles
    lbRoles.DataBind()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  End Try
End Sub

Loading lbApps is different because there is no method to call to get all the applications. If you examine the aspnetdb database you will see that there is not even a stored procedure to do the job. Therefore you need to create your own stored procedure, spGetApplications, and do the work to call it. Here is the stored procedure:

CREATE PROCEDURE dbo.spGetApplications
as
  Select ApplicationName 
    From aspnet_applications
    Order By ApplicationName
Return

And here is the code that uses it.

Sub LoadAppList()
  Dim drList As SqlDataReader
  Dim conn As SqlConnection
  Dim cmd As SqlCommand
  Try
    conn = New
      SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer")
      .ToString)
    cmd = New SqlCommand("spGetApplications", conn)
    cmd.CommandType = CommandType.StoredProcedure
    conn.Open()
    drList = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    lbApps.Items.Clear()
    lbApps.DataSource = drList
    lbApps.DataValueField = "ApplicationName"
    lbApps.DataTextField = "ApplicationName"
    lbApps.DataBind()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  Finally
    If Not drList Is Nothing Then
      drList.Close()
    End If
  End Try
End Sub

There is no method to store a new application in aspnetdb but there is a stored procedure, aspnet_Applications_CreateApplication, which is called by the stored procedures that store new users and roles. This stored procedure needs to know the application the users and roles should be associated with. If the application is not already stored in the database, you can call aspnet_Applications_CreateApplication to add it before storing the user or role. You can call it just like any other stored procedure, as I do here, in order to store an application when you're not ready to store a user or role. Notice that there is an output parameter which you have to include even though the subroutine does not do anything with it.

There is some risk in calling the procedure yourself rather than through a method built into .NET. You have no guarantee that Microsoft won't modify some aspect of it, perhaps the parameters used, in a future release. Some of the stored procedures were changed in this way between the beta and release versions of .NET 2.0.

Protected Sub btnSaveApp_Click(ByVal sender As Object, 
    ByVal e As System.EventArgs) Handles btnSaveApp.Click
  Dim conn As SqlConnection
  Dim cmd As SqlCommand
  Dim OutputParm As SqlParameter
  Try
    conn = New 
      SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer")
      .ToString)
    cmd = New SqlCommand("aspnet_Applications_CreateApplication", conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SqlParameter("@ApplicationName", 
      SqlDbType.NVarChar)).Value = txtNewApp.Text
      OutputParm = New SqlParameter("@ApplicationId", 
      SqlDbType.UniqueIdentifier)
    OutputParm.Direction = ParameterDirection.Output
    cmd.Parameters.Add(OutputParm)
    conn.Open()
    cmd.ExecuteNonQuery()
    LoadAppList()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  Finally
    conn.Close()
  End Try
End Sub

Storing a new user and a new role is easier because the methods are provided for us. There are stored procedures behind the scenes, namely aspnet_Membership_CreateUser and aspnet_Roles_CreateRole, that provide the required functionality. You might find it interesting and worthwhile to take a look at some of the stored procedures that are part of the aspnetdb database. Note that there are other overloads of Membership.CreateUser() that include a password question. If you use one of these overloads in your code, you will not need to set requiresQuestionAndAnswer="false" as described earlier in the Installing and Compiling the Sample Code section of this article.

Protected Sub btnSaveUser_Click(ByVal sender As Object, ByVal e As 
    System.EventArgs) Handles btnSaveUser.Click
  Try
    Membership.CreateUser(txtNewUser.Text, "Password1!")
    LoadUserList()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  End Try
End Sub
Protected Sub btnSaveRole_Click(ByVal sender As Object, ByVal e As 
    System.EventArgs) Handles btnSaveRole.Click
  Try
    Roles.CreateRole(txtNewRole.Text)
    LoadRoleList()
  Catch ex As Exception
    lblMessage.Text = ex.Message.ToString
  End Try
End Sub

All that is left in the code is to reset the user and roles lists when the selection has changed, and the code to display the current Membership and Role applications. This display feature will be used shortly when I show a pitfall when using different applications.

Protected Sub lbApps_SelectedIndexChanged(ByVal sender As Object, ByVal e As 
    System.EventArgs) Handles lbApps.SelectedIndexChanged
  LoadUserList()
  LoadRoleList()
End Sub
Protected Sub btnDisplay_Click(ByVal sender As Object, ByVal e As 
    System.EventArgs) Handles btnDisplay.Click
  lblMembershipApp.Text = Membership.ApplicationName
  lblRoleApp.Text = Roles.ApplicationName
End Sub

When you run this page it will display the default / application with the users and roles previously entered. Go ahead and enter an application called Scheduler with users Tom, Dick, and Harry. Then add the roles Scheduler, Administrator, and Guest. Enter an application called Workflow with users Bob, Carol, Ted, and Alice. Now add the roles Create Document, Review, and Approve. When you change the selection in the application list box, the users and roles shown in the other list boxes will change also. If you create an application name element in web.config, you can use it to set Membership.ApplicationName and Roles.ApplicationName in your application and each application will only know about its own users and roles. But wait, it isn't that simple.

Contention and the Singleton Model

Try this: Open an instance of the page and select the Scheduler application from the list box. Click the Display Current Applications button and you will see that Scheduler is the current application for Membership and Roles, just like you would expect. Now, open a second instance of the page without closing the first and select the Workflow application and again click the Display Current Applications button. Now both application names are Workflow. Looks good, but now go back to the first instance and click Display Current Applications again. Now the current application names are Workflow, the same as in the other window, even though this window still shows Scheduler in the application list box (see Figure 13). What happened?

Figure 13. Scheduler is selected, but Workflow is the current application

The Membership and Role classes use the singleton design pattern. I did not instantiate either one in my code to access their ApplicationName properties, I just used the instance that is automatically created for me. All applications running on the same web server share the same instances of these classes, and when one application changes a property, all applications are affected. How can different applications use separate roles and users without stepping on each other then?

If your applications are big enough to each have their own web server - or web farm - there is no problem. You can set the application names in machine.config and not worry about it. They can share a centralized membership services database without interfering with each other. You could set up separate databases to support membership services for each application, at the loss of some centralization. You could also just use the stored procedures directly, rather than accessing them through the Membership and Role methods. This approach lets you set the application name directly, without reference to the properties.

The best way is to specify membership and role providers through web.config, either in addition to or replacing the ones in machine.config. In machine.config I have the following membership provider specified:

<membership>
  <providers>
    <add name="AspNetSqlMembershipProvider" 
      type="System.Web.Security.SqlMembershipProvider, System.Web,   
      Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
      connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" 
      enablePasswordReset="true" requiresQuestionAndAnswer="false" 
      applicationName="/" requiresUniqueEmail="false"
      passwordFormat="Hashed" maxInvalidPasswordAttempts="5" 
      minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" 
      passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
  </providers>
</membership>

I mentioned earlier that we could change the applicationName if I needed to, but of course I prefer not to change the machine.config to suit an application. That is what web.config is for. So instead of changing machine.config, add the following to web.config, in the <system.web> section:

<membership defaultProvider="AspNetSqlMembershipProviderLocal">
  <providers>
    <add name="AspNetSqlMembershipProviderLocal" 
      type="System.Web.Security.SqlMembershipProvider, System.Web, 
      Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
      connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" 
      enablePasswordReset="true" requiresQuestionAndAnswer="false" 
      applicationName="Workflow" requiresUniqueEmail="false" 
      passwordFormat="Hashed" maxInvalidPasswordAttempts="5"
      minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" 
      passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
  </providers>
</membership>

I have specified a separate provider for the application, which I have named AspNetSqlMembershipProviderLocal. Now, instead of using the ApplicationName property, I can switch application names by changing the provider, like this:

Dim mp As MembershipProvider
'Point to the membership provider in machine.config
mp = Membership.Providers("AspNetSqlMembershipProvider")
' ...do some processing, such as mp.CreateUser
'Switch back to the membership provider specified in web.config
mp = Membership.Providers("AspNetSqlMembershipProviderLocal")
' ...continue processing

I can specify as many Membership and Role providers as needed. Note that I made AspNetSqlMembershipProviderLocal the default provider. The provider specified in machine.config is still present, but it is not used unless I specify it, as shown above. I could have removed AspNetSqlMembershipProvider by making this entry in web.config:

<membership> 
  <remove name="AspNetSqlMembershipProvider" />
    <providers>
        <add name="AspNetSqlMembershipProviderLocal" …

And that is certainly an option, but leaving it in place means that the original provider is still available.

Conclusion

In this article I explained how the SqlMembership provider permits you to have separate users and roles for different applications. I described how the users, roles, and application names are stored in the aspnetdb database used by the provider. I showed the limitations of the Website Administration Tool (WAT) provided with Visual Studio and showed a simple page that could be used as the basis for an improved tool. I also pointed out the issues with multiple applications and suggested ways of getting around them.