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.
- Delete the MVC 4 AccountController, AccountModels, Account view folder and _LoginPartial shared view
- Create a new MVC 3 web application
- Copy the MVC 3 AccountController, AccountModels, Account view folder and _LogOnPartial shared view into your MVC 4 application
- 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;