ASP.NET Universal Providers

By on

1 Comment

Earlier this month, I posted about how the ASP.NET membership providers are creating the required database schema for me automagically when I first hit the site.  Here is a quick update to that statement now that I more thoroughly understand what’s going on.

Scott Hanselman does a great job introducing us to the ASP.NET Universal Providers for Session, Membership, Roles and User Profile so I won’t repeat it here.  What I DON’T get from his article is that the Universal Providers are the default for a new ASP.NET MVC3 project (and that they’re not yet supported on Azure).  I hadn’t touched ASP.NET or MVC for multiple years, so I just went along quietly and created a new project, pointed my connection string at a SQL Server and things were all working.  It wasn’t until I published the site to Azure that things fell apart.

The Universal Providers (“DefaultMembershipProvider”) are referenced throughout the web.config for all the different pieces of membership, and here you see it set as the default provider (the one that membership code in the site will look for and use).

  <membership defaultProvider="DefaultMembershipProvider">
    <providers>
      <clear />
      <add name="AspNetSqlMembershipProvider"
            type="System.Web.Security.SqlMembershipProvider"
            connectionStringName="ApplicationServices"
            enablePasswordRetrieval="false"
            enablePasswordReset="true"
            requiresQuestionAndAnswer="false"
            requiresUniqueEmail="false"
            maxInvalidPasswordAttempts="5"
            minRequiredPasswordLength="6"
            minRequiredNonalphanumericCharacters="0"
            passwordAttemptWindow="10"
            applicationName="/" />
      <add name="DefaultMembershipProvider"
            type="System.Web.Providers.DefaultMembershipProvider, 
                   System.Web.Providers, Version=1.0.0.0, Culture=neutral,
                   PublicKeyToken=31bf3856ad364e35"
            connectionStringName="DefaultConnection"
            enablePasswordRetrieval="false"
            enablePasswordReset="true"
            requiresQuestionAndAnswer="false"
            requiresUniqueEmail="false"
            maxInvalidPasswordAttempts="5"
            minRequiredPasswordLength="6"
            minRequiredNonalphanumericCharacters="0"
            passwordAttemptWindow="10"
            applicationName="/" />
    </providers>
  </membership>

This works fine as long as we were developing on our local machines (where the Universal Providers are installed), and even when we’re hitting the SQL Azure database from our local machine (the provider is local, the database is remote – so the code can create the SQL Azure compatible schema on the fly regardless of the DB location).  When the site is deployed to Azure, where the Universal Providers are not installed, you get an error: Unable to find the requested .Net Framework Data Provider. It may not be installed.

It took forever to figure out that this was the problem, and 1 minute to fix it – just switch web.config to use the SQL Providers that are already configured, just not as the default.

  <membership defaultProvider="AspNetSqlMembershipProvider">

 

The bottom line: the Universal Providers are not yet available on Azure servers, so you have to go with the legacy SQL Providers.  These providers, as always, require you to run the ASPNET_REGSQL tool to create the required database schema before you hit the site.

  • Universal Providers: create their required DB schema on first use, do not have the aspnet_xxx prefix on the tables, and do not use any views or stored procedures.
  • SQL Providers: require you to run ASPNET_REGSQL before first use, the tables are namespaced with “aspnet_” in front, and there are views and stored procedures that go along with the tables (all created by the ASPNET_REGSQL tool)

 
 
 
 

ASP.NET Membership Schema Created Automatically

By on

Leave a comment

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.

aspnet_regsql

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.

dbschema-tool

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.

dbschema-run

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( ).

Method1

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.

Method2

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.

dbschema-clean

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.)

 
 

Entity Framework Self Tracking Entities Cascade Delete Fails

By on

1 Comment

This is just a quick FYI if you come across a situation while using self tracking entities where you’re getting weird errors when trying to delete a entity that had cascade delete turned on for related entities.

Instead of doing:

MyEntity.MarkAsDeleted();

myContext.MyObjectSet.ApplyChanges(MyEntity);
myContext.SaveChanges();

You need to first make sure the entity is attached and then call DeleteObject for the cascade to run correctly.

So now the code now becomes:

myContext.MyObjectSet.Attach(MyEntity);
myContext.MyObjectSet.DeleteObject(MyEntity);
myContext.SaveChanges();

Happy cascade delete.