September 12, 2011 07:09 by
ScottRecently I had to setup an ASP.NET MVC 2 project which would utilize the built-in membership of ASP.NET. However, I didn't have access to a MS SQL database, so I had to use a MySQL data provider instead. The following is a quick guide on how to get it setup.
First, you need to download the MySQL Connector/Net from this page. This makes it possible to connect to MySQL databases from .NET applications and gives you access to the ADO.NET interfaces. I choose to download the latest development release (at the time of writing) Connector/Net 6.3.3 beta, as this fully integrates with Visual Studio 2010 which the latest public release (6.2.3 at the time of writing) does not. Download BOTH the source (mysql-connector-net-6.3.3-src.zip) and the installation file (mysql-connector-net-6.3.3.zip). I will explain why in a second.
Once you've downloaded both files, extract them and install the connector. Now, normally when using the membership provider, the database tables/schemas are automatically created. The MySQL membership provider does this as well, unfortunately it just doesn't do it right. At least it didn't work for me. Instead, you have to create the databases semi-manually. Go to the location where you extracted the source and browse to the following folder "\MySql.Web\Providers\Properties". In this folder you will see a number of .sql files: schema1.sql, schema2.sql, schema3.sql, schema4.sql, schema5.sql and schema6.sql. Run each of these, in turn and starting with schema1.sql, against your MySQL database.
Now, fire up Visual Studio 2010 and open your application. Add a reference to MySql.Web.dll which can be found in the directory you installed the Connector, e.g. C:\Program Files\MySQL\MySQL Connector Net 6.3.3\Assemblies\v2.0
Next, unless you haven’t done this already, add your MySQL connection string to the configuration/connectionStrings element in the Web.config, e.g.:
<connectionStrings>
<add name="MySQLConn" connectionString="Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERNAME;Pwd=PASSWORD;"/>
</connectionStrings>
Finally, open up your Web.config and add these lines to the <system.web> element:
<membership defaultProvider="MySqlMembershipProvider">
<providers>
<clear/>
<add name="MySqlMembershipProvider"
type="MySql.Web.Security.MySQLMembershipProvider,MySql.Web,Version=6.3.3.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d"
autogenerateschema="true" connectionStringName="MySQLConn"
enablePasswordRetrieval="false" enablePasswordReset="true"
requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
passwordFormat="Hashed" maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0"
passwordAttemptWindow="10" passwordStrengthRegularExpression=""
applicationName="/" />
</providers>
</membership>
<profile defaultProvider="MySqlProfileProvider">
<providers>
<clear/>
<add name="MySqlProfileProvider"
type="MySql.Web.Profile.MySQLProfileProvider,MySql.Web,Version=6.3.3.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d"
connectionStringName="MySQLConn" applicationName="/" />
</providers>
</profile>
<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
<providers>
<clear />
<add name="MySqlRoleProvider"
type="MySql.Web.Security.MySQLRoleProvider,MySql.Web,Version=6.3.3.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d"
connectionStringName="MySQLConn" applicationName="/" />
</providers>
</roleManager>
Now, you've (hopefully) got a fully working MySQL membership provider. To test it, go to Project > ASP.NET Configuration and go to the Security tab. Here you should be able to manage users and roles.
NOTE: Make sure you enter the correct connectionstring name, version number and PublicKeyToken in the Web.config.