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. Replace @Html.Partial(“_LoginPartial”) in the shared _Layout view with @Html.Partial(“_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;