Using MySQL Providers with ASP.NET

I'm figuring out how to use MySQL with Visual Studio and ASP.NET. The first thing to install (besides MySQL Server) is the MySQL Connector for .NET, currently at version 6.0.4.
After installing this connector, you've all the .NET providers and entity framework stuff to go. One thing I should mention is that the .NET Connectors will install MySql providers which you can use in your website, MySql won't popup in the Express editions of Visual Studio. So you can't connect to a MySQL database with your server explorer with these versions.

So installing the MySQL connector on your server will modify the server's machine.config and add the provider config in it. Not installing the connector on your server means you need to configure the providers in the system.web section of the web.config of the site (and bin deploying the MySql dll's of course):

<membership defaultProvider="MySQLMembershipProvider">
<providers>
<remove name="MySQLMembershipProvider" />
<add connectionStringName="MySqlServer" enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="true"
applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10"
passwordStrengthRegularExpression="" name="MySQLMembershipProvider"
type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</providers>
</membership>
<profile>
<providers>
<remove name="MySQLProfileProvider"/>
<add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="MySqlServer" applicationName="/" />
</providers>
</profile>
<roleManager defaultProvider="MySQLRoleProvider">
<providers>
<remove name="MySQLRoleProvider" />
<add connectionStringName="MySqlServer" applicationName="/" name="MySQLRoleProvider"
type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</providers>
</roleManager>


These new providers need a connection string, so add to your connectionString section:

<connectionStrings>
<add name="MySqlServer" providerName="MySql.Data.MySqlClient" connectionString="server=mysql1;user id=webuser;password=xxxxxx;persist security info=True;database=website" />
</connectionStrings>

Next you need the schema. The docs will say they will be created automatically, but I didn't saw a single table created. By looking at the connector source code, I distilled the following script to create the schema in your database:

CREATE TABLE `my_aspnet_applications` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) DEFAULT NULL,
`description` varchar(256) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

CREATE TABLE `my_aspnet_membership` (
`userId` int(11) NOT NULL DEFAULT '0',
`Email` varchar(128) DEFAULT NULL,
`Comment` varchar(255) DEFAULT NULL,
`Password` varchar(128) NOT NULL,
`PasswordKey` char(32) DEFAULT NULL,
`PasswordFormat` tinyint(4) DEFAULT NULL,
`PasswordQuestion` varchar(255) DEFAULT NULL,
`PasswordAnswer` varchar(255) DEFAULT NULL,
`IsApproved` tinyint(1) DEFAULT NULL,
`LastActivityDate` datetime DEFAULT NULL,
`LastLoginDate` datetime DEFAULT NULL,
`LastPasswordChangedDate` datetime DEFAULT NULL,
`CreationDate` datetime DEFAULT NULL,
`IsLockedOut` tinyint(1) DEFAULT NULL,
`LastLockedOutDate` datetime DEFAULT NULL,
`FailedPasswordAttemptCount` int(10) unsigned DEFAULT NULL,
`FailedPasswordAttemptWindowStart` datetime DEFAULT NULL,
`FailedPasswordAnswerAttemptCount` int(10) unsigned DEFAULT NULL,
`FailedPasswordAnswerAttemptWindowStart` datetime DEFAULT NULL,
PRIMARY KEY (`userId`)
) COMMENT='2';

CREATE TABLE `my_aspnet_profiles` (
`userId` int(11) NOT NULL,
`valueindex` longtext,
`stringdata` longtext,
`binarydata` longblob,
`lastUpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`userId`)
) ;


CREATE TABLE `my_aspnet_roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`applicationId` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ROW_FORMAT=DYNAMIC;

CREATE TABLE `my_aspnet_schemaversion` (
`version` int(11) DEFAULT NULL
) ;

CREATE TABLE `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 DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

CREATE TABLE `my_aspnet_usersinroles` (
`userId` int(11) NOT NULL DEFAULT '0',
`roleId` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`userId`,`roleId`)
) ROW_FORMAT=DYNAMIC;

INSERT my_aspnet_SchemaVersion (version) VALUES (4);


Finally, you can use the ASP.NET Configuration website to configure users. To get only the providers working, it took a whole blog post. The .NET connector for MySQL is still a little bit rough on the edges, but after figuring the above things out, it will work!