If you are like me you can’t afford a production version of Microsoft SQL Server and even more crazy you can’t afford a license for each processor you have running in your production server. There is no way you are going to use SQL Server Express because you need to allow more than one application to hit the membership database and you can’t stand the file locking that occurs on all express database files.
That being said the best alternative and one I really really like is good old MySQL Community Server, it is amazing! An what is even more cool is that MySQL comes with a set of pre-built .Net connectors which you can download from their site and install. When you install the .Net connector it adds config entries to your Machine.config for all of the .Net Membership, Role and Profile providers they created for MySQL. Once this is done your system is ready to access all .Net membership using the MySQL DBMS. Simply amazing.
To set up your ASP.net website to use the MySQL membership providers you have a couple of config entries to add to your Web.config file. they are as follows:
1. Set up your Connection String
<connectionStrings>
<remove name="LocalMySqlServer"/>
<add name="LocalMySqlServer"
connectionString="Datasource=YOURSERVER;Database=YOURDATABASE;uid=YOURUSERNAME;pwd=YOURPASSWORD;"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
2. Set your default Membership Provider and Role Provider
<membership defaultProvider="MySQLMembershipProvider" />
<roleManager enabled="true" defaultProvider="MySQLRoleProvider" />
3. Optional but extremely helpful on your development machines is to set up the MySQL membership provider to install the membership schema in the database automatically if it does not exist. Saves you from manually running scripts. After installation of the .Net Connector go to your Machine.config and locate the system.web section, within it you should see entries for Membership, Profile and RoleManager. In addition to the default ASP.net providers that come with .Net you will see a new entries in each of those config sections for MySQL. Locate the MySQLMembershipProvider and add a new attribute autogenerateschema=”true”. It should look something like this:
<membership>
<providers>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
<add name="MySQLMembershipProvider" autogenerateschema="true" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
</providers>
</membership>
<profile>
<providers>
<add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
</profile>
<roleManager>
<providers>
<add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
Now you should be able to open the website administration tool from within Visual Studio and walk through setting up Users and Roles as well as Access Rules just like you would if you used the built in ASP.net membership providers.
Good Luck!
MemoryTin 
Published by