UPDATE: A few weeks after posting this, I learned more about what’s going on with the membership providers. See the update here.

It’s been a few years since I’ve done any ASP.NET work. Recently, I fired up Visual Studio 2010 and created an ASP.NET MVC project that uses membership.  I remembered that I needed the table schema to support the membership functionality, which you can create by running aspnet_regsql.exe.  This tool shows a wizard that allows you to add or remove the database objects for membership, profiles, role management, and personalization.

Here is the contents of the MembershipTest database after running the tool.  Notice the tables are prefixed with “aspnet_” and there are views and stored procedures to support the functionality.

Now we just edit web.config to set where the membership database is located (MembershipTest).

  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=localhost;Initial Catalog=MembershipTest;
        Integrated Security=SSPI;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />
    <add name="DefaultConnection" connectionString="data source=localhost;Initial Catalog=MembershipTest;
        Integrated Security=SSPI;MultipleActiveResultSets=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

We haven’t written any code – we just have the MVC project that was created from the VS project template.  Run it, and register a new user, thus exercising the membership logic to create a new user in the database.  Check out the database schema. There are a handful of new tables listed, those WITHOUT the “aspnet_” prefix.

When we look in the aspnet_users table (which was created by the aspnet_regsql tool), our user is not there.  Look in the Users table, and it IS there.  What’s going on here?  From what I can tell, the objects created by the aspnet_regsql tool ARE NOT USED by the latest SqlMembershipProvider and DefaultMembershipProvider.   So who is creating the objects required (those without the “aspnet_” prefix)?

So far, I haven’t found any documentation on this, but Reflector is our friend.  Looking through the provider assemblies, I find the code that is creating the database and schema at run time!

In the MVC project AccountController, we call Membership.CreateUser in the Register method.  Let’s find that method using Reflector.  Look up DefaultMembershipProvider.CreateUser, which calls the private Membership_CreateUser.  At the very top of that method, it calls ModelHelper.CreateMembershipEntities( ).

Follow that call chain down, and eventually you get to see the first part of the schema creation – the actual database.  After that, it goes through a whole bunch of code to generate the objects themselves.

So just to prove it to myself, I deleted the MembershipTest database, and ran my project again (same connection string in config, pointing to the non-existent MembershipTest database).  Run the project, create a user.  Sure enough, the database is created, the required objects are there (and we never ran the aspnet_regsql tool).  It seems that the newest providers don’t need any of the old Views or Stored Procedures either. None are created.

UPDATE: A few weeks after posting this, I learned more about what’s going on with the membership providers. See the update here.

The only thing I can come up with is that the ASPNET_REGSQL tool is obsolete.  Maybe with the advent of the Entity Framework Code-First technology, Microsoft took the attitude that they’ll create the DB objects in code if they’re not already there.

Note: it turns out that you don’t even have to run the web site project and register a new user.  You can use the ASP.NET Configuration tool (Project menu in VS) and create a user there.  It uses the same provider configuration, so that will also create the required database schema.

Even better – I ran this same test against SQL Azure, and it works fine as well.  Creates the database and objects, and membership works just fine (UPDATE: …but running the site IN Azure blows up, since the provider is not installed there yet. See follow-up post.)