Setting up MySql Membership with Visual Studio 2010

Introduction

This article will help in setting up the MySql membership provider with Visual Studio 2010.

Background

Membership provider will help in automatically setting up the login functionality that comes with .NET Framework.

Prerequisites

• MySql server should be installed on the local machine or on the remote machine with which you have access.
• We need a database to which we will connect to test the membership provider after configuring.

Following Steps Will Help in Setting Up the MySql Membership Provider with .NET

• Download and install the fully-managed ADO.NET driver for MySQL from here version 6.3.4+
• You might have to close down the Visual Studio development env. before starting the install.

  

• After installation, you can confirm that the MySql assemblies are present in C:\WINDOWS\assembly folder like in the image below:

  

• Configure a database in MySql server, I have created a "Test" database on localhost, User as "root" and password as "rootpassword", I will be using these same credential in connection strings configuration.
• Put the following code for connection string in web.config:
  XML

  <connectionStrings>
  <remove name="LocalMySqlServer"/>
     <add name="LocalMySqlServer" 
          connectionString="Datasource=localhost;
                            uid=root;
                            Pwd=rootpassword;
                            Database=test;" 
          providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

• Put the following code for Membership provider, Profile Provider and Role Provider. Check that you refer to the correct connection string name in the below config.
  XML

  <membership defaultProvider="MySqlMembershipProvider">
  <providers>
  <clear />
    <add name="MySqlMembershipProvider" 
         type="MySql.Web.Security.MySQLMembershipProvider, 
               MySql.Web,Version=6.3.4.0,
               Culture=neutral,
               PublicKeyToken=c5687fc88969c44d" 
         connectionStringName="LocalMySqlServer" 
         enablePasswordRetrieval="false" 
         enablePasswordReset="true" 
         requiresQuestionAndAnswer="false" 
         requiresUniqueEmail="true" 
         passwordFormat="Hashed" 
         maxInvalidPasswordAttempts="5" 
         minRequiredPasswordLength="6" 
         minRequiredNonalphanumericCharacters="0" 
         passwordAttemptWindow="10" 
         applicationName="/" 
         autogenerateschema="true" />
  </providers>
  </membership>
  <profile>
  <providers>
  <clear />
    <add type="MySql.Web.Security.MySQLProfileProvider, 
               MySql.Web,Version=6.3.4.0,
               Culture=neutral,
               PublicKeyToken=c5687fc88969c44d" 
         name="MySqlProfileProvider" 
         applicationName="/" 
         connectionStringName="LocalMySqlServer" 
         autogenerateschema="true" />
  </providers>
  </profile>
  <roleManager enabled="true" defaultProvider="MySqlRoleProvider">
  <providers>
  <clear />
    <add connectionStringName="LocalMySqlServer" 
         applicationName="/" 
         name="MySqlRoleProvider" 
         type="MySql.Web.Security.MySQLRoleProvider,
               MySql.Web,Version=6.3.4.0,
               Culture=neutral,PublicKeyToken=c5687fc88969c44d" 
         autogenerateschema="true" />
  </providers>
  </roleManager>

• Open machine.config from "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG" in the connection string section, add the following code:

  XML

  <connectionStrings>
  <add name="LocalMySqlServer" 
  connectionString="Datasource=localhost;
                    uid=root;
                    Pwd=rootpassword;
                    Database=test;" />
  </connectionStrings>

• In same machine.config, check and modify the providers for Membership, Profile and Role also check that connectionStringName attribute points to correct connection string name. Don't delete any of the config settings that are present by default in these provider sections, just add the following lines between provider sections.
  XML

  <membership>
  <providers>
    <add name="MySQLMembershipProvider" 
         type="MySql.Web.Security.MySQLMembershipProvider, 
               MySql.Web, Version=6.3.4.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="" 
         autogenerateschema="true"/>
  </providers>
  </membership>
  <profile>
  <providers>
    <add name="MySQLProfileProvider" 
         type="MySql.Web.Profile.MySQLProfileProvider, 
               MySql.Web, Version=6.3.4.0,
               Culture=neutral,
               PublicKeyToken=c5687fc88969c44d" 
         connectionStringName="LocalMySqlServer" 
         applicationName="/" />
  </providers>
  </profile>
  <roleManager>
  <providers>
   <add name="MySQLRoleProvider" 
        type="MySql.Web.Security.MySQLRoleProvider,
              MySql.Web,
              Version=6.3.4.0,
              Culture=neutral,
       PublicKeyToken=c5687fc88969c44d" 
       connectionStringName="LocalMySqlServer" 
       applicationName="/" />
  </providers>
  </roleManager>

• Go to Visual Studio, click Project->ASP.NET configuration:

  

• ASP.NET Web Application Administration will open in a new window, click on security tab you can now create User, Roles and Access rules
• You can also check with the database in MySql that has been configured with the project with the list of *aspnet* tables, these are auto generated in the database.