Using MySql 5 as Membership Backend for ASP.NET 4.5 MVC 4 Application

Oracle provide MySql ASP.Net web providers through NuGet – search for MySql.Web. Using the MySql providers you can easily use MySql as your membership/profile/role backend for an ASP.Net application. But beware the MySql providers do not currently support the ASP.NET 4.5 templates since those templates use ExtendedMembershipProvider and SimpleMembershipProvider and they have an internal dependency on MS SQL Server

I am going to demonstrate using it as the membership provider for an MVC 4 application using Razor but the steps are almost identical for the ASPX views.

Web.config for MySql Membership Provider

Use the following configuration in your web.config file:

<membership defaultProvider="MySqlMembershipProvider">
<providers>
<clear />
<add name="MySqlMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.5.4.0, PublicKeyToken=c5687fc88969c44d"
autogenerateschema="true"
connectionStringName="*NAME_OF_YOUR_CONN_STRING*"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="false"
requiresUniqueEmail="false"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
passwordAttemptWindow="10"
passwordStrengthRegularExpression=""
applicationName="/" />
</providers>
</membership>

Obviously you also need to configure a valid MySql connection string, create a MySql schema, change any other security settings you want and put the name of your connection string into the provider configuration but your on your own doing that.

Create Tables

Once you have the initial setup done open the ASP.Net configuration website (VS2012: Project menu -> ASP.NET Configuration – at the bottom of the menu) and create a user in the security tab. Doing this will create the database structure and a new user providing you have set the configuration up correctly.

Unbreak the MVC 4 AccountController

This is a simple fix for getting MySql up and running but without enabling any new cool features of the WebMatrix, WebSecurity & SimpleMembership. Basically we just rollback the AccountController to use the old style MembershipProvider which is supported by the MySql MembershipProvider.

  1. Delete the MVC 4 AccountController, AccountModels, Account view folder and _LoginPartial shared view
  2. Create a new MVC 3 web application
  3. Copy the MVC 3 AccountController, AccountModels, Account view folder and _LogOnPartial shared view into your MVC 4 application
  4. [email protected](“_LoginPartial”) in the shared _Layout view [email protected](“_LogOnPartial”)

This won’t support OAuth authentication but will get your MySql Membership provider up and running with ASP.Net 4.5 and MVC 4.

Raw SQL for MySql Membership Tables

If you want to directly create the membership data structure without using the ASP.NET configuration tools use the following SQL as a base and modify SCHEMA_NAME and collation to be what you require.

-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_applications`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_applications` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(256) NULL DEFAULT NULL ,
  `description` VARCHAR(256) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_membership`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_membership` (
  `userId` INT(11) NOT NULL DEFAULT '0' ,
  `Email` VARCHAR(128) NULL DEFAULT NULL ,
  `Comment` VARCHAR(255) NULL DEFAULT NULL ,
  `Password` VARCHAR(128) NOT NULL ,
  `PasswordKey` CHAR(32) NULL DEFAULT NULL ,
  `PasswordFormat` TINYINT(4) NULL DEFAULT NULL ,
  `PasswordQuestion` VARCHAR(255) NULL DEFAULT NULL ,
  `PasswordAnswer` VARCHAR(255) NULL DEFAULT NULL ,
  `IsApproved` TINYINT(1) NULL DEFAULT NULL ,
  `LastActivityDate` DATETIME NULL DEFAULT NULL ,
  `LastLoginDate` DATETIME NULL DEFAULT NULL ,
  `LastPasswordChangedDate` DATETIME NULL DEFAULT NULL ,
  `CreationDate` DATETIME NULL DEFAULT NULL ,
  `IsLockedOut` TINYINT(1) NULL DEFAULT NULL ,
  `LastLockedOutDate` DATETIME NULL DEFAULT NULL ,
  `FailedPasswordAttemptCount` INT(10) UNSIGNED NULL DEFAULT NULL ,
  `FailedPasswordAttemptWindowStart` DATETIME NULL DEFAULT NULL ,
  `FailedPasswordAnswerAttemptCount` INT(10) UNSIGNED NULL DEFAULT NULL ,
  `FailedPasswordAnswerAttemptWindowStart` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`userId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
COMMENT = '2';
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_profiles`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_profiles` (
  `userId` INT(11) NOT NULL ,
  `valueindex` LONGTEXT NULL DEFAULT NULL ,
  `stringdata` LONGTEXT NULL DEFAULT NULL ,
  `binarydata` LONGBLOB NULL DEFAULT NULL ,
  `lastUpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (`userId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_roles`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_roles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `applicationId` INT(11) NOT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
ROW_FORMAT = DYNAMIC;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_schemaversion`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_schemaversion` (
  `version` INT(11) NULL DEFAULT NULL )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_sessioncleanup`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_sessioncleanup` (
  `LastRun` DATETIME NOT NULL ,
  `IntervalMinutes` INT(11) NOT NULL )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_sessions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_sessions` (
  `SessionId` VARCHAR(255) NOT NULL ,
  `ApplicationId` INT(11) NOT NULL ,
  `Created` DATETIME NOT NULL ,
  `Expires` DATETIME NOT NULL ,
  `LockDate` DATETIME NOT NULL ,
  `LockId` INT(11) NOT NULL ,
  `Timeout` INT(11) NOT NULL ,
  `Locked` TINYINT(1) NOT NULL ,
  `SessionItems` LONGBLOB NULL DEFAULT NULL ,
  `Flags` INT(11) NOT NULL ,
  PRIMARY KEY (`SessionId`, `ApplicationId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `applicationId` INT(11) NOT NULL ,
  `name` VARCHAR(256) NOT NULL ,
  `isAnonymous` TINYINT(1) NOT NULL DEFAULT '1' ,
  `lastActivityDate` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;
-- -----------------------------------------------------
-- Table `SCHEMA_NAME`.`my_aspnet_usersinroles`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_usersinroles` (
  `userId` INT(11) NOT NULL DEFAULT '0' ,
  `roleId` INT(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`userId`, `roleId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci
ROW_FORMAT = DYNAMIC;

 

8 Responses to “Using MySql 5 as Membership Backend for ASP.NET 4.5 MVC 4 Application”

Read below or add a comment...

  1. Will says:

    Worked great, Thanks!

  2. Masud Parvez says:

    Thanks Brother,

    You Saved my Day.
    Keep up the good work.

  3. asava samuel says:

    Joshua

    It looks like Kellerman Software has a MySQL LINQ Provider:
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

  4. Dennis says:

    This worked and I have basic login functionw, but how do I get membership roles to function with mysql in addition to this? When I do that it just throws up an error, I guess because the part about providing roles is still SQL based in the web.config file?

  5. David Squire says:

    I just tried to set this up and I keep getting a compile error saying, “Error 1 The type or namespace name ‘UsersContext’ could not be found (are you missing a using directive or an assembly reference?)”

    Is there something else that I am missing? Where is the UserContext class supposed to be coming from?

    Thank you!

    • Vineet says:

      Please exclude the initializesimplemembership.cs class which is there in the Filters folder and you will see the error disappear

  6. guy says:

    i receive error on the “create tables” section
    in the security tab:
    Could not load file or assembly MySql.Web, Version=6.6.5.0, PublicKeyToken=c5687fc88969c44d’ or one of its dependencies

  7. wdelmas says:

    great article!
    git hub repo about ASP MVC 3 + Memebership + Mysql available here: https://github.com/wdelmas/ASP-MVC4-Membership-Mysql

Leave A Comment...

*