Migrating to ASP.NET 2 - Part 4 - Implementing Custom Providers for Membership, Roles and Profiles

Read Part 1 - Introduction
Read Part 2 - Setup and Master Pages
Read Part 3 - Business Logic, Content Pages and User Controls
Read Part 4 - Implementing Custom Providers for Membership, Role Management and Profiles
Read Part 5 - It's the little things...

Upgrading my Custom Security Solution to ASP.NET 2.0

One of the most interesting design decisions I had to make in upgrading my web site to the new ASP.NET 2.0 platform was the way I wanted to handle security. I am sure you're aware of the many new features of ASP.NET 2.0, with one of my favorites being the whole Provider Model with providers like Membership and Role management built on top of it. For a new web site, all you need to do is enable these providers, optionally prepare a database so the providers can use it, and you're done. Instant security!!

My current web site (and many other ASP.NET 1.x web sites) already had its own security mechanism. Loosely modeled after early Whidbey builds, my solution had some resemblance with the way ASP.NET 2.0 handles security. That is, I had a SQL Server database with a Member table, a Role table, an Application table and a table to link members to roles. So far not much new. However, the biggest challenge I had to face is the way my members are stored in the database. I have a couple of hundred members in my database that can access protected parts of my site. For example, friends and family can log in to access my photo albums, while others can log in to manage content and members. These members have at least a user name and a password, and it's the password that made things difficult. As a good security practice, my passwords are not stored as clear text, nor are they encrypted. The database only stores a hash of the password, and since hashing is an irreversible process, there is no way to find out what the old password was.

I had locked myself in in my own solution. Since I couldn't convert the hashes of the passwords anymore, I also couldn't migrate my members from my table structure to the new structure that ASP.NET 2.0 expects. So, I had to come up with a solution. Probably the easiest solution was to write a short news article on my homepage, explaining my users that their account had been deleted, and that they would need to resubscribe to access the site. But obviously, that isn't a very good solution. I wanted to find a way to keep my own users, while still making use of the new and very powerful features of ASP.NET 2.0 security. Custom Providers to the rescue!!

Introduction to the Provider Model

On the Microsoft MSDN site you'll find a good definition of a Provider:

What's important in this quote is the fact that a provider provides a "uniform interface" and that it abstracts physical storage. In simple terms, this means that you can program against a known interface, without knowing the actual implementation.

For example, in ASP.NET 2.0, a call to Membership.FindUsersByEmail is actually a call to the configured provider. This usually means a call to the SqlMembershipProvider provider, but it is also possible to use a different provider. For that to work, all you need to do is configure the relevant nodes in the web.config file and point it to a different (custom) provider. Once the settings in the web.config are applied, calls like the above are now forwarded to the provider you configured. For example, the following web.config code tells .NET to use a provider for the Membership feature called CustomSqlServerMembershipProvider that is present in an assembly called Spaanjaars.Imar.CustomSqlServerProvider:

<membership defaultProvider="CustomSqlServerMembershipProvider">
  <providers>
  <clear/>
  <add
    name="CustomSqlServerMembershipProvider"
    type="Spaanjaars.Imar.Providers.CustomSqlServerMembershipProvider, 
             Spaanjaars.Imar.CustomSqlServerProvider"
    connectionStringName="DefaultConnectionString"
    enablePasswordRetrieval="false"
    enablePasswordReset="true"
    requiresUniqueEmail="false"
    requiresQuestionAndAnswer="false"
    minRequiredPasswordLength="1"
    minRequiredNonalphanumericCharacters="0"
    applicationName="SampleSite"
    hashAlgorithmType="SHA1"
    passwordFormat="Hashed"
  />
  </providers>
</membership>

As you can see, the class CustomSqlServerMembershipProvider is a custom class that I wrote that handles all the Membership tasks. Similar to the configuration for the MembershipProvider, you can configure a custom Role, Profile or Personalization provider.

But how can this class handle these tasks, and how is the CustomSqlServerMembershipProvider class able to service the requests made by the ASP.NET 2.0 Framework?. This is where the terms "uniform interface" come in to play. I'll take the Membership feature as an example in this article, but the same principle applies to the other providers. In ASP.NET 2.0 there is an abstract base class called MembershipProvider that defines all the methods of the Membership framework, like CreateUser, DeleteUser, ValidateUser and so on. Because the class is marked as abstract, it cannot be used directly, but it must be inherited from. Classes that inherit from MembershipProvider must implement all of its required methods. So, in the example above, the custom class CustomSqlServerMembershipProvider plays by the rules and has an implementation for all of the required methods.

Since a lot has been written about the Provider model, I won't dig into the inner workings of the provider model any deeper. Instead, this article focuses on the way I wrote my custom providers, and the issues I ran into. For a thorough introduction in the provider model and the different providers that ship with ASP,NET 2.0, I strongly suggest the article ASP.NET 2.0 Provider Model: Introduction to the Provider Model and all of the articles that are linked from that page. Additionally, you should check out the Provider Toolkit homepage, also at the MSDN web site. This section of the MSDN site provides invaluable information about the Provider model in general and ASP.NET 2.0 specific provider implementations.

Writing a Custom Provider for Imar.Spaanjaars.Com

As I mentioned before, the database design for my 1.x version of my web site is pretty similar to the model that ASP.NET 2.0 expects. So, it shouldn't be hard to make the two work together. However, before I even tried, I had a few decisions to make, the biggest being the way I was going to implement my provider. Basically, I had at least three options:

1. Inherit from one of the available providers, like SqlMembershipProvider or SqlRoleProvider and only override methods whose behavior I wanted to change.
2. Start from scratch, by inheriting from the base classes like MembershipProvider or RoleProvider.
3. Be smart and reuse some of the work that Microsoft has already done by implementing a provider that works with an Access database.

Option 1 wasn't really an option. Although the differences between my database model and that of the Sql*Providers weren't that large, there was a fair chance I had to override almost all of the methods anyway. In such a scenario, it's better to choose a class a bit higher up and in the hierarchy and inherit from that.

Option 2 meant a lot of work, as you basically have to write everything from scratch. While this gives you a lot of flexibility, it also means an awful lot of work.

In the end - who would have guessed - option 3 was the best choice. On the Provider Toolkit homepage I linked to earlier, you can download an almost full implementation of the 4 important providers (Membership, Roles, Profiles and Personalization) as a Starter Kit in C#, targeting a Microsoft Access database. (You'll find the link to the C# project under Downloads on the Toolkit page). My idea was to use the full source for the Access implementation and then modify it to work with SQL Server.

When you download and install the start kit, you get a new project template under Visual C# | Starter Kits in the New Project dialog of Visual Web Developer (provided you're running the Standard, Professional or Team System edition). When you create a new project based on that template, you end up with a solution as depicted in the following figure:

At this stage, you have 4 fully working providers that talk with a Microsoft Access database. All you need to do is compile the application, and then configure your web site to use the new provider. The web.config file that is supplied with the download shows you the exact configuration settings you need.

Since I needed to change the app to have it work with SQL Server, simply compiling wasn't an option for me. So, I had to change the code. In fact, I had to change *a lot* of code. The seven *.cs files you see in the Solution Explorer contain over 5,700 lines of code! Fortunately, when you base your own provider on this one, you don't have to change all of it.

Here's what I did to change the Access project into a collection of Providers that works against my own custom database scheme.

1. Housekeeping

I started with some housekeeping. I am not going to use the PersonalizationProvider, so I dropped the entire class from the project. That also meant I could drop some of the methods that works with Paths from the ConnectionHelper class, as Paths are used exclusively by the PersonalizationProvider.

With the unneeded code removed, I changed the namespace of the project, renamed the assembly and moved some code around (for example, the using statements were placed inside the namespace, while I prefer to keep them outside the namespace definition).

2. Changing Code For the Data Provider

As you may have guessed, the Access implementation uses the OleDb data provider. To make it work with SQL Server, I replaced all occurrences of OleDb with Sql. So, I changed an OleDbConnection to a SqlConnection, an OleDbParameter with a SqlParameter (there are a few helper methods that return these kind of objects) and so on. I also had to change some of the DbTypes used, like OleDbType.BSTR.

3. Changing Code Implementation

The next thing I had to change was the Initialize method. Since my provider is not going to support all of the features (for example, I won't allow clear text or encrypted passwords), I changed the behavior of Initialize so it throws exceptions when an unsupported value is set.
I also removed the method GetFileNameFromConnectionName and replaced it with GetConnectionFromConnectionName. The Access implementation allows you to set a file path as a connection string. Some code in the AccessConnectionHelper.cs class then builds up a valid connection string based on the file path. In my case, I wanted to be able to set a connectionStringName attribute on the provider configuration, and have it point to a valid connection string in the <connectionStrings> section.

4. Changing the Way the Database is Accessed

At this stage, my application compiled successfully. However, it didn't run of course because the internal implementation of T-SQL statements differed from the way my database is designed. Also, the Access implementation uses embedded T-SQL statements, so you'll find a lot of SELECT, INSERT, UPDATE and DELETE statements all over the code. I prefer to use stored procedures, so I modified the code accordingly. To see what I did, take a look at the Access implementation of the GetUserId method:

internal static int GetUserID(OleDbConnection connection, 
     int applicationID, string userName, bool createIfNeeded, 
     bool newUserIsAnonymous, DateTime lastActivityDate)
{
  if (applicationID == 0 || userName == null || userName.Length < 1)
    return 0;

  if (connection == null)
    return 0; // Wrong params!

  OleDbCommand lookupCommand = new OleDbCommand(
       @"SELECT UserId FROM aspnet_Users WHERE ApplicationId = @AppId 
            AND UserName = @UserName", connection);
  lookupCommand.Parameters.Add(new OleDbParameter("@AppId", applicationID));
  lookupCommand.Parameters.Add(new OleDbParameter("@UserName", userName));

  object lookupResult = lookupCommand.ExecuteScalar();

  if ((lookupResult != null) && (lookupResult is int))
  {
    return (int)lookupResult;
  }

  if (createIfNeeded)
  {
    OleDbCommand createCommand = new OleDbCommand(@"INSERT INTO aspnet_Users " +
    @"(ApplicationId, UserName, IsAnonymous, LastActivityDate) " +
    @"VALUES (@AppID, @UserName, @IsAnonymous, @LastActivityDate)",
    connection);

    createCommand.Parameters.Add(new OleDbParameter("@AppID", applicationID));
    createCommand.Parameters.Add(new OleDbParameter("@UserName", userName));
    createCommand.Parameters.Add(new OleDbParameter("@IsAnonymous", 
            newUserIsAnonymous));
    createCommand.Parameters.Add(new OleDbParameter("@LastActivityDate", 
             new DateTime(lastActivityDate.Year, lastActivityDate.Month, 
                     lastActivityDate.Day, lastActivityDate.Hour, 
                     lastActivityDate.Minute, lastActivityDate.Second)));

    if (createCommand.ExecuteNonQuery() != 0)
    {
      lookupResult = lookupCommand.ExecuteScalar();
      if ((lookupResult != null) && (lookupResult is int))
      {
        return (int)lookupResult;
      }
    }
  }
  return 0;
}

And here's how my version ended up, using stored procedures instead of in-line T-SQL:

internal static Guid GetUserId(SqlConnection connection, 
       Guid applicationId, string userName, bool createIfNeeded, bool newUserIsAnonymous, 
       DateTime lastActivityDate, SqlTransaction myTransaction)
{
  if (applicationId == Guid.Empty || userName == null || userName.Length < 1) 
  {
    return Guid.Empty;
  }
  if (connection == null)
  {
    return Guid.Empty;
  }

  SqlCommand lookupCommand = 
      new SqlCommand(@"sprocMemberSelectIdByName", connection);
  lookupCommand.CommandType = CommandType.StoredProcedure;

  if (myTransaction != null)
  {
    lookupCommand.Transaction = myTransaction;
  }

  lookupCommand.Parameters.Add(new SqlParameter("@appId", applicationId));
  lookupCommand.Parameters.Add(new SqlParameter("@userName", userName));

  object lookupResult = lookupCommand.ExecuteScalar();
  if ((lookupResult != null) && (lookupResult is Guid))
  {
    return new Guid(lookupResult.ToString());
  }

  if (createIfNeeded)
  {
    SqlCommand createCommand = 
              new SqlCommand(@"sprocMemberInsertSingleItem", connection);
    createCommand.CommandType = CommandType.StoredProcedure;

    if (myTransaction != null)
    {
      createCommand.Transaction = myTransaction;
    }
            
    createCommand.Parameters.Add(new SqlParameter("@appID", applicationId));
    createCommand.Parameters.Add(new SqlParameter("@userName", userName));
    createCommand.Parameters.Add(new SqlParameter("@isAnonymous", newUserIsAnonymous));
    createCommand.Parameters.Add(new SqlParameter("@lastActivityDate", 
           new DateTime(lastActivityDate.Year, lastActivityDate.Month, 
           lastActivityDate.Day, lastActivityDate.Hour, 
           lastActivityDate.Minute, lastActivityDate.Second)));

    if (createCommand.ExecuteNonQuery() != 0)
    {
      lookupResult = lookupCommand.ExecuteScalar();
      if ((lookupResult != null) && (lookupResult is Guid))
      {
        return new Guid(lookupResult.ToString());
      }
    }
  }

  return Guid.Empty;
}	

There are a lot of similarities between the original code, but also a few interesting differences. First of all, you won't see in-line SQL in my code. This makes the code a bit easier to maintain, and also easier to reuse specific SQL statements. Secondly, I changed some of the behavior of the application. The Access implementation uses Ints to identify users, roles and applications. The SQL Server version that comes with ASP.NET 2.0 uses Guids as primary keys. My own database uses Guids as well, so it was a logical choice to develop the code in favor of the Guids. Since the ProviderUserKey of a MembershipUser is an object, it doesn't matter to calling code whether an Integer or a Guid is used (as long as the calling code knows *what* is used).

The final thing to notice is the Transaction object. Some of the methods in the Access implementation use transactions to ensure database integrity. Access has pretty limited support for transactions. So instead of creating ADO.NET Transaction objects, the following construct was used to start a transaction:

  OleDbCommand command = new OleDbCommand("BEGIN TRANSACTION", connection);
  command.ExecuteNonQuery();
  fBeginTransCalled = true;

Later in the code, either COMMIT TRANSACTION or ROLLBACK TRANSACTION was called, depending on the success or failure of the operation.

I had to update number of helper methods to support transactions as well. For example, the GetApplicationId and GetUserId now have an overload that accepts a Transaction object. I had to do this, because these methods are used in transaction aware methods. As soon as you have an open Connection with a Command object that has a Transaction object attached, all other command actions and objects need a Transaction as well (at least, according to my findings; there may be ways to avoid this problem). To fix this, I could have created a separate connection, for read-only access. However, extending some of the helper methods so they support transactions felt a lot more natural.

During the rewrite, I moved ALL the T-SQL code to stored procedures. Along the way, I recreated the database tables from Access in my own database, and modified them where appropriate (that is, I changed Ints to Guids and so on). I decided that at this stage it would be much easier to implement the table structure of the Access database. Once all the stored procedures were created, it would be much easier to modify the procedures again so they were using my own database scheme instead. That allowed me to focus on the rewrite, without getting distracted with database design too much.

5. Tweaking the Database Design

Once all the code had been upgraded to stored procedures, I could focus on a redesign of the database. One of the things I changed is the way users are stored. The .NET providers for Membership have a separate User and Membership table. My current database stores this data in a single table. Modifying the stored procedures wasn't very difficult; most columns were easily transferred, renamed or dropped completely. I also removed some of the procedures I had written in the previous phase, and combined their code with a few others to create multi purpose procedures.

At the end, I ended up with around 30 procedures for the Membership and Role providers, each of them accessing my own tables now and returning relevant information about users, roles and applications.

6. Fixing the Password Problem

It turned out that the main problem that brought me to writing a custom provider was a lot easier to fix than anticipated. As you may recall, my passwords were stored as hashes only. ASP.NET 2 is capable of storing clear text, encrypted and hashed passwords. It does this by storing the PasswordFormat in a separate column. I used this column to introduce another type of PasswordFormat: Legacy. I updated the entire table and set that column for all users to -1. I then used the following code in the CheckPassword methods to determine whether an "old" user is accessing the site, or a new one, and hash the password accordingly:

private bool CheckPassword(SqlConnection connection, Guid userId, 
     string password, out bool userIsApproved, SqlTransaction myTransaction)
{
  string salt;
  string pass2;
  int passwordFormat, status;
  string pass = GetPasswordWithFormat(connection, userId, 
          null, false, out passwordFormat, out status, 
          out salt, out userIsApproved, myTransaction);
  if (passwordFormat != -1)
  {
    // New user account. Using default hashing behavior from the Access provider code
    pass2 = EncodePassword(password, passwordFormat, salt);
  }
  else
  {
    // backwards compatibility hack. Get the password from the database and compare it
    // with my custom hashed version the way I did in my ASP.NET 1.x application 
    pass2 = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
  }
  return (pass == pass2);
}

This turned out to work remarkably well. When existing users log in, the call to GetPasswordWithFormat will give status a value of -1, which in turn ensures that my "legacy code" is run by calling HashPasswordForStoringInConfigFile on the FormsAuthentication class and passing it a proper hashing algorithm (SHA1 in this case). Users that sign up after the upgrade will automatically use the new code in the Provider implementation.

The good thing of this solution is that the problem will go away automatically over time. When users change their password, they'll make use of the new feature, get a proper PasswordFormat and then use the new code from that point onward.

With the change in the password module, I had fixed about every issue I had in the Membership provider. Changing the Roles provider was even less of a hassle; most code worked out of the box, as the ASP.NET 2 database schema was pretty compatible with my own. At the time of writing, I haven't completed the Profiles provider yet, but I am not anticipating much problems.

Debugging Tips

Once you have a basic framework of your providers ready, it's time to test them. You could write special methods for testing the layer (which is always a good idea; using Nunit for example). However, you may also want to do some quick testing of some methods. The easiest way to test your provider code is by using the Web Site Administration Tool that comes with Visual Web Developer (choose Website | ASP.NET Configuration). But if you do use that tool, you'll find that it doesn't break into your code, even if you set some break points. That's because the process isn't started with debugging enabled. But this is easy to fix. Right after you start the tool, choose Debug | Attach to Process in Visual Web Developer and then locate the Web server's process in the Available Processes list. The process is called WebDev.WebServer.Exe. If you have multiple processes with the same name, make sure you choose the one with the right port number as you can see from the following screen shot.

Now, when you perform any actions in the WSA tool that cause a call on your provider (e.g. get a list of users, add users to roles etc), your code will break and you can walk through your provider's code line by line.

Notice that the WSA tool does not cover all of your methods; so even if your code runs fine while testing the tool, you still need to do additional testing.

What's Next

With my custom providers done, I am making good progress with my upgrading process to ASP.NET 2.0. There are, however, a few things that still need to be done. First of all, I need to update my security related pages that should now make use of the new Membership and Role providers. That will be easy now; it's mostly a matter of removing obsolete code and removing calls like MyCustomBusinessObject.GetUsers() by calls to Membership.GetAllUsers(). Building a security tool can hardly be any easier than this.

Note

Please note that I am not releasing the code for my providers. First of all, they aren't finished completely. But a more logical reason is that since they are custom providers, they are custom to my database and my database only. There is no point in trying to reuse code that has been written specifically against my database. To learn *how* the code works, the Access implementation that you can download from the Microsoft site should be more than enough. If you do have compelling reasons to see my code, drop me a line and explain why you want to see it....

References

I listed a few of the sources I used for my custom articles throughout this article, but for completeness sake I'll list them here again.

• Provider Toolkit homepage
• ASP.NET 2.0 Provider Model: Introduction to the Provider Model
• Download the sample Access project (C# only)
• Introduction to Membership
• Managing Authorization Using ASP.NET Roles
• Implementing a Membership Provider
• Implementing a Role Provider