VistaDB Membership Provider for ASP.NET MVC

The VistaDB Membership Provider

Having got my ASP.NET MVC application up and running with a VistaDB database, my next task was to see if I could get the Membership Provider working. VistaDB claims to come with a ready-made ASP.NET membership provider, so I hoped that it would be nice and easy to plug in to a website.

Unfortunately, it wasn't quite so simple. The VistaDB help file doesn't explain how to use it at all. The source code for the membership provider is included but doesn't compile due to a missing AssemblyInfo.cs file. And the MembershipSample web site that is supposed to demonstrate how to use it is incomplete - it doesn't contain the necessary Provider configuration in web.config and it doesn't provide a sample database.

First Attempt

After a bit of fishing around I found that the VistaDB 3.3 install directory contains aspnetdb.vdb3 which is a blank database for the provider to use. Double-click it to open it in the VistaDB DataBuilder application and you can select Database | Generate Script to create a SQL Script that creates the necessary tables, thereby allowing you to put them into another vdb3 file if you wish.

1. Rather than trying to build the incomplete MembershipProvider solution, I simply copy VistaDB.Web.cs file into my project.

2. I copied the aspnetdb.vdb3 into my App_Data folder. I decided against putting the tables in another database for the time being.

3. Add a connection string to web.config

<connectionStrings>
    <add name="VistaDBMembershipProvider" connectionString="Data Source=|DataDirectory|\aspnetdb.vdb3;Open Mode=NonexclusiveReadWrite"/>
</connectionStrings>

4. Now add an authentication setting to web.config. Since I am using ASP.NET MVC, I am going to specify that the Login page will be in the Admin controller:

<authentication mode="Forms">
   <forms loginUrl="Admin/Login"></forms>
</authentication>

5. Now tell it what membership provider to use, and configure the membership provider:

<membership defaultProvider="VistaDBMembershipProvider">
<providers>
    <add 
        name="VistaDBMembershipProvider" 
        type="VistaDB.Web.Security.VistaDBMembershipProvider"   
        connectionStringName="VistaDBMembershipProvider" 
        applicationName="MyApplication" 
        maxInvalidPasswordAttempts="2"
        passwordAttemptWindow="10"
        minRequiredAlphaNumericCharacters="2"
        minRequiredPasswordLength="8"
        passwordStrengthRegularExpression="" 
        enablePasswordReset="true"
        enablePasswordRetrieval="true"
        requiresQuestionAndAnswer="true" 
        requiresUniqueEmail="true"
        writeExceptionsToEventLog="true"
        passwordFormat="Encrypted"
        />
</providers> 
</membership>

Securing the Site

The next normal thing to do would be to use web.config to indicate which pages are not accessible to unauthenticated users. However, in the world of ASP.NET MVC, the approach is slightly different. Rob Conery has a post on adding action filters.

6. I copied the code for the RequiresAuthenticationAttribute and RequiresRoleAttribute filters. MVC is constantly changing though, and I needed to change instances of FilterExecutingContext to be ActionExecutingContext to get it compiling.

7. So now I needed to test my authentication by putting this attribute on a controller action. I chose my BlogController's Edit action.

[RequiresAuthentication()]
public ActionResult Edit(string id)
{
   return RenderView();
}

8. We also need to create the Login action on our Admin controller. Fredrik Normén had some sample code on his blog which I used as the basis for my Login controller. Again, I found that changes to the MVC framework meant that his code didn't compile as is. Here is what I ended up with:

public ActionResult Login(string userName, string password, string ReturnUrl)
{
    if (this.IsValidLoginArgument(userName, password))
    {
        if (Membership.ValidateUser(userName, password))
            return RedirectFromLoginPage(userName, ReturnUrl);
        else
            this.ViewData["LoginFailed"] = "Login failed! Make sure you have entered the right user name and password!";
    } 
    return RenderView("Login");
} 

private ActionResult RedirectFromLoginPage(string userName, string ReturnUrl)
{
    FormsAuthentication.SetAuthCookie(userName, false); 

    if (!string.IsNullOrEmpty(ReturnUrl))
        return Redirect(ReturnUrl);
    else
        return Redirect(FormsAuthentication.DefaultUrl);
} 

private bool IsValidLoginArgument(string userName, string password)
{
    return !(string.IsNullOrEmpty(userName) && string.IsNullOrEmpty(password));
}

9. I was then able to test my site, and sure enough, we were redirected to the Admin/Login page if we tried to access Blog/Edit/nnn. Of course, login always failed as I hadn't any users. So I selected Project|ASP.NET Configuration in Visual Studio to attempt to add a user with the built-in interface. However, it crashed while trying to create the user.

Greg Obleshchuk to the Rescue

The saga of getting a working ASP.NET Provider for VistaDB was taking up a bit too much of my time for my liking, and I found help in the form of Greg Obleshchuk who had coded his own VistaDB Membership Provider and made it available on the VistaDB forums. Note that you need an account before it will let you download it. The advantage of using his code is that he has also implemented a RolesProvider.

10. I downloaded Greg's provider code, which is implemented in VB. The first task was to create the database tables. Greg had included the CREATE TABLE commands in comments at the top of the source code. But for some reason, they were in a syntax that the VistaDB DataBuilder application didn't accept. I had to replace Guid with UNIQUEIDENTIFIER, Text with NVARCHAR, YesNo with BIT and Integer with INT, before it would let me add the three tables needed for the membership and roles providers. Here is the corrected SQL:

CREATE TABLE [Users]
(
   PKID UniqueIdentifier NOT NULL PRIMARY KEY,
   Username NVARCHAR (255) NOT NULL,
   ApplicationName NVARCHAR (255) NOT NULL,
   Email NVARCHAR (128) NOT NULL,
   Comment NVARCHAR (255),
   Password NVARCHAR (128) NOT NULL,
   PasswordQuestion NVARCHAR (255),
   PasswordAnswer NVARCHAR (255),
   IsApproved BIT, 
   LastActivityDate DateTime,
   LastLoginDate DateTime,
   LastPasswordChangedDate DateTime,
   CreationDate DateTime, 
   IsOnLine BIT,
   IsLockedOut BIT,
   LastLockedOutDate DateTime,
   FailedPasswordAttemptCount INT,
   FailedPasswordAttemptWindowStart DateTime,
   FailedPasswordAnswerAttemptCount INT,
   FailedPasswordAnswerAttemptWindowStart DateTime
 );

CREATE TABLE Roles
(
  Rolename NVARCHAR (255) NOT NULL,
  ApplicationName NVARCHAR (255) NOT NULL,
    CONSTRAINT PKRoles PRIMARY KEY (Rolename, ApplicationName)
);

CREATE TABLE UsersInRoles
(
  Username NVARCHAR (255) NOT NULL,
  Rolename NVARCHAR (255) NOT NULL,
  ApplicationName NVARCHAR (255) NOT NULL,
  CONSTRAINT PKUsersInRoles PRIMARY KEY (Username, Rolename, ApplicationName)
);

11. My next idea was simply to put the provider VB files into a folder in my website, but for some reason, Visual Studio insisted on compiling them as C# so I was forced to create a separate assembly for the providers.

12. After creating my new VistaDBProviders assembly (my first ever VB.NET assembly!), I then ran into more problems. My website didn't seem able to create instances of the providers no matter what I did in the web.config. After trying strong naming my assembly, I eventually discovered using Reflector that VB.NET automatically adds the name of your assembly as a namespace prefix to all classes. Once I had that worked out, the relevant lines in my web.config were as follows:

<add assembly="VistaDBProviders, Version=1.0.0.0, Culture=neutral, PublicKeyToken=919ca070305cc370"/> 
...
<providers>
  <add
      name="VistaDBMembershipProvider"
      type="VistaDBProviders.VistaDBMembershipProvider" 

13. My troubles weren't quite over. I discovered I needed to generate a MachineKey to be able to use encrypted passwords. I had had enough of messing around for one day so I decided go with passwordFormat="Clear"

Success at Last

Having done all this, I was finally able create a user using Project | ASP.NET Configuration and additionally could assign some roles. It would be nice if future drops of VistaDB could make this process a lot more streamlined, and include a Roles Provider as well.