SimpleMembership with SQL Server Compact (SDF) - code first with existing DB

Question

Probably I'm missing something obvious but I cannot manage to get an SDF-based ASP.NET MVC 4 web app work with the new simple membership. I detail my steps so this can be useful as a reference for other newbies.

To start with, I found this very useful intro to the new membership system: http://weblogs.asp.net/jgalloway/archive/2012/08/29/simplemembership-membership-providers-universal-providers-and-the-new-asp-net-4-5-web-forms-and-asp-net-mvc-4-templates.aspx. My steps in a code-first with existing database (the SDF is a temporary placeholder for a full-fledged existing SQL Server db) were as follows:

1. I created a new internet app with VS 2012.
2. I added a new SDF file to App_Data (Accounts.sdf) and created there my tables for users and roles.
3. I added a new connection string to web.config:

<connectionStrings>
      <clear/>
      <add name="AccountsContext" connectionString="Data Source=|DataDirectory|\Accounts.sdf;Persist Security Info=False" providerName="System.Data.SqlServerCe.4.0" />
    </connectionStrings>

4. I changed the InitializeSimpleMembershipAttribute.cs file to use my own datacontext, which is hosted in an intermediate data layer; here I paste the few relevant changes I made to the template code:

...
    public SimpleMembershipInitializer()
    {
        Database.SetInitializer(null);
        try
        {
            using (AccountsContext context = new AccountsContext())
            {
                if (!context.Database.Exists())
                {
                    ((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
                }
            }
            WebSecurity.InitializeDatabaseConnection("AccountsContext", "User", "Id", "Name", autoCreateTables: true);
            // seed data here...
        }
    ...

Here it is the data context (notice the connection string name in the default ctor):

public sealed class AccountsContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }

    public AccountsContext() : base("Name=AccountsContext")
    {
        Database.Initialize(false);
    }

    public AccountsContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
        Database.Initialize(false);
    }

    public AccountsContext(DbConnection connection, bool contextOwnsConnection) : 
        base(connection, contextOwnsConnection)
    {
        Database.Initialize(false);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        // user
        modelBuilder.Entity<User>().Property(u => u.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // role
        modelBuilder.Entity<Role>().Property(r => r.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        modelBuilder.Entity<Role>().ToTable("webpages_Roles");
        modelBuilder.Entity<Role>().Property(r => r.Id).HasColumnName("RoleId");
        modelBuilder.Entity<Role>().Property(r => r.Name).HasColumnName("RoleName");

        // user-role
        modelBuilder.Entity<User>()
            .HasMany(u => u.Roles)
            .WithMany(r => r.Users)
            .Map(m =>
            {
                m.MapLeftKey("UserId");
                m.MapRightKey("RoleId");
                m.ToTable("webpages_UsersInRoles");
            });
    }
}

Now when I run the web app I immediately get an exception telling me that the LocalSqlServer connection name was not found. This belongs to the machine.config where I can find these entries:

...
<membership>
<providers>
  <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" .../>
  <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" ... autogenerateschema="true"/>
</providers>
...

Thus I tried to override these entries by adding these lines to my web.config:

<roleManager enabled="true" defaultProvider="SimpleRoleProvider">
  <providers>
    <clear/>
    <add name="SimpleRoleProvider" type="WebMatrix.WebData.SimpleRoleProvider, WebMatrix.WebData"/>
  </providers>
</roleManager>
<membership defaultProvider="SimpleRoleProvider">
  <providers>
    <clear/>
    <add name="SimpleMembershipProvider" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData"/>        
  </providers>
</membership>

If I run the app now I get an exception like this:

System.Configuration.ConfigurationErrorsException
Message=Default Membership Provider could not be found.
Source=System.Web
BareMessage=Default Membership Provider could not be found.

Yet, the web app has references to both WebMatrix.Data and WebMatrix.WebData (both version 2), already set by the VS template. So how could I let this work?

Answer 1

In my current mvc 4 project with mssql, its a simple one i so I just wanted very simple memmbership provider I disabled InitializeSimpleMembershipAttribute

by

[Authorize]
//[InitializeSimpleMembership]
public partial class AccountController : Controller

and added this code to global.asax under Application_Start so you dont need anymore SimpleMembershipInitializer

WebSecurity.InitializeDatabaseConnection(
             connectionStringName: "DefaultConnection",
             userTableName: "UserProfile",
             userIdColumn: "UserID",
             userNameColumn: "UserName",
             autoCreateTables: true);

in my sql database the application created some tables on of them was Roles and UserInRoles just added the roles I needed like Admin, customer, etc... you can do the same with your database or build some interface to manage roles and memmbership. and I restrict the access to some Controllers or Actions by adding this code

[Authorize(Roles = "Admin")]
public class MessagesController : Controller

Answer 2

Could not say this is obvious, yet it does work.

In order to have SimpleMembeship working with SQL Compact, the very first thing you need to do is to add with Nuget "EntityFramework.SqlServerCompact" and "Microsoft ASP.NET Universal Providers Core Library"

The name of your connection string being "AccountsContext", you should have the same in your Model:

   public class UsersContext : DbContext
    {
        public UsersContext()
            : base("AccountsContext")
        {
        }

        public DbSet<UserProfile> UserProfiles { get; set; }
        public DbSet<webpages_Membership> Membership { get; set; }
    }

Then, your section should not have anything SQL related, as this is for ASP security, not the new SimpleMembership; here is how mine looks like:

<membership defaultProvider="simple">
      <providers>
        <clear />
        <add name="simple" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData" />
      </providers>
    </membership>