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.

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:

1              <membership defaultProvider="MySqlMembershipProvider">
2              <providers>
3              <clear />
4              <add name="MySqlMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.5.4.0, PublicKeyToken=c5687fc88969c44d"
5              autogenerateschema="true"
6              connectionStringName="*NAME_OF_YOUR_CONN_STRING*"
7              enablePasswordRetrieval="false"
8              enablePasswordReset="true"
9              requiresQuestionAndAnswer="false"
10           requiresUniqueEmail="false"
11           passwordFormat="Hashed"
12           maxInvalidPasswordAttempts="5"
13           minRequiredPasswordLength="6"
14           minRequiredNonalphanumericCharacters="0"
15           passwordAttemptWindow="10"
16           passwordStrengthRegularExpression=""
17           applicationName="/" />
18           </providers>
19           </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. 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.

1              -- -----------------------------------------------------
2              -- Table `SCHEMA_NAME`.`my_aspnet_applications`
3              -- -----------------------------------------------------
4              CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_applications` (
5                `id` INT(11) NOT NULL AUTO_INCREMENT ,
6                `name` VARCHAR(256) NULL DEFAULT NULL ,
7                `description` VARCHAR(256) NULL DEFAULT NULL ,
8                PRIMARY KEY (`id`) )
9              ENGINE = InnoDB
10           AUTO_INCREMENT = 2
11           DEFAULT CHARACTER SET = latin1
12           COLLATE = latin1_swedish_ci;
13           -- -----------------------------------------------------
14           -- Table `SCHEMA_NAME`.`my_aspnet_membership`
15           -- -----------------------------------------------------
16           CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_membership` (
17             `userId` INT(11) NOT NULL DEFAULT '0' ,
18             `Email` VARCHAR(128) NULL DEFAULT NULL ,
19             `Comment` VARCHAR(255) NULL DEFAULT NULL ,
20             `Password` VARCHAR(128) NOT NULL ,
21             `PasswordKey` CHAR(32) NULL DEFAULT NULL ,
22             `PasswordFormat` TINYINT(4) NULL DEFAULT NULL ,
23             `PasswordQuestion` VARCHAR(255) NULL DEFAULT NULL ,
24             `PasswordAnswer` VARCHAR(255) NULL DEFAULT NULL ,
25             `IsApproved` TINYINT(1) NULL DEFAULT NULL ,
26             `LastActivityDate` DATETIME NULL DEFAULT NULL ,
27             `LastLoginDate` DATETIME NULL DEFAULT NULL ,
28             `LastPasswordChangedDate` DATETIME NULL DEFAULT NULL ,
29             `CreationDate` DATETIME NULL DEFAULT NULL ,
30             `IsLockedOut` TINYINT(1) NULL DEFAULT NULL ,
31             `LastLockedOutDate` DATETIME NULL DEFAULT NULL ,
32             `FailedPasswordAttemptCount` INT(10) UNSIGNED NULL DEFAULT NULL ,
33             `FailedPasswordAttemptWindowStart` DATETIME NULL DEFAULT NULL ,
34             `FailedPasswordAnswerAttemptCount` INT(10) UNSIGNED NULL DEFAULT NULL ,
35             `FailedPasswordAnswerAttemptWindowStart` DATETIME NULL DEFAULT NULL ,
36             PRIMARY KEY (`userId`) )
37           ENGINE = InnoDB
38           DEFAULT CHARACTER SET = latin1
39           COLLATE = latin1_swedish_ci
40           COMMENT = '2';
41           -- -----------------------------------------------------
42           -- Table `SCHEMA_NAME`.`my_aspnet_profiles`
43           -- -----------------------------------------------------
44           CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_profiles` (
45             `userId` INT(11) NOT NULL ,
46             `valueindex` LONGTEXT NULL DEFAULT NULL ,
47             `stringdata` LONGTEXT NULL DEFAULT NULL ,
48             `binarydata` LONGBLOB NULL DEFAULT NULL ,
49             `lastUpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
50             PRIMARY KEY (`userId`) )
51           ENGINE = InnoDB
52           DEFAULT CHARACTER SET = latin1
53           COLLATE = latin1_swedish_ci;
54           -- -----------------------------------------------------
55           -- Table `SCHEMA_NAME`.`my_aspnet_roles`
56           -- -----------------------------------------------------
57           CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_roles` (
58             `id` INT(11) NOT NULL AUTO_INCREMENT ,
59             `applicationId` INT(11) NOT NULL ,
60            `name` VARCHAR(255) NOT NULL ,
61             PRIMARY KEY (`id`) )
62           ENGINE = InnoDB
63           DEFAULT CHARACTER SET = latin1
64           COLLATE = latin1_swedish_ci
65           ROW_FORMAT = DYNAMIC;
66           -- -----------------------------------------------------
67           -- Table `SCHEMA_NAME`.`my_aspnet_schemaversion`
68           -- -----------------------------------------------------
69           CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_schemaversion` (
70             `version` INT(11) NULL DEFAULT NULL )
71           ENGINE = InnoDB
72           DEFAULT CHARACTER SET = latin1
73           COLLATE = latin1_swedish_ci;
74           -- -----------------------------------------------------
75           -- Table `SCHEMA_NAME`.`my_aspnet_sessioncleanup`
76           -- -----------------------------------------------------
77           CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_sessioncleanup` (
78             `LastRun` DATETIME NOT NULL ,
79             `IntervalMinutes` INT(11) NOT NULL )
80           ENGINE = InnoDB
81           DEFAULT CHARACTER SET = latin1
82           COLLATE = latin1_swedish_ci;
83           -- -----------------------------------------------------
84           -- Table `SCHEMA_NAME`.`my_aspnet_sessions`
85           -- -----------------------------------------------------
86           CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_sessions` (
87             `SessionId` VARCHAR(255) NOT NULL ,
88             `ApplicationId` INT(11) NOT NULL ,
89             `Created` DATETIME NOT NULL ,
90             `Expires` DATETIME NOT NULL ,
91            `LockDate` DATETIME NOT NULL ,
92             `LockId` INT(11) NOT NULL ,
93             `Timeout` INT(11) NOT NULL ,
94             `Locked` TINYINT(1) NOT NULL ,
95             `SessionItems` LONGBLOB NULL DEFAULT NULL ,
96             `Flags` INT(11) NOT NULL ,
97             PRIMARY KEY (`SessionId`, `ApplicationId`) )
98           ENGINE = InnoDB
99           DEFAULT CHARACTER SET = latin1
100         COLLATE = latin1_swedish_ci;
101         -- -----------------------------------------------------
102         -- Table `SCHEMA_NAME`.`my_aspnet_users`
103         -- -----------------------------------------------------
104         CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_users` (
105           `id` INT(11) NOT NULL AUTO_INCREMENT ,
106           `applicationId` INT(11) NOT NULL ,
107          `name` VARCHAR(256) NOT NULL ,
108           `isAnonymous` TINYINT(1) NOT NULL DEFAULT '1' ,
109           `lastActivityDate` DATETIME NULL DEFAULT NULL ,
110           PRIMARY KEY (`id`) )
111         ENGINE = InnoDB
112         AUTO_INCREMENT = 2
113         DEFAULT CHARACTER SET = latin1
114         COLLATE = latin1_swedish_ci;
115         -- -----------------------------------------------------
116         -- Table `SCHEMA_NAME`.`my_aspnet_usersinroles`
117         -- -----------------------------------------------------
118         CREATE  TABLE IF NOT EXISTS `SCHEMA_NAME`.`my_aspnet_usersinroles` (
119           `userId` INT(11) NOT NULL DEFAULT '0' ,
120           `roleId` INT(11) NOT NULL DEFAULT '0' ,
121           PRIMARY KEY (`userId`, `roleId`) )
122         ENGINE = InnoDB
123         DEFAULT CHARACTER SET = latin1
124         COLLATE = latin1_swedish_ci
125         ROW_FORMAT = DYNAMIC;