MySQL Membership and Roles Providers for ASP.NET

Recently I started working on a personal project and before diving into some real coding, I decided to go ahead and refresh some of my ASP.NET skills and knowledge as I didn’t do any serious ASP.NET development in more than 1.5 years. As most probably I will host the project on GoDaddy and will start with Economy plan until some upgrades will be necessary I am planning to use MySQL as data repository as they offer 10 MySQL databases of 1GB each- enough space for what I need at the beginning.

Since my project will need to support authentication of users and different roles within the application, I started refreshing my knowledge on the Membership and Roles providers. As my choice for database is MySQL I was afraid I will have to write custom providers to work with membership and roles stored in a MySQL database. Fortunately, I found out there is a much easier way of doing this thanks to the latest MySQL Connector for .NET.

Here are the steps I followed in order to set-up Membership and Roles providers using the MySQL .NET Connector.

  • download and install the latest MySQL .NET Connector. I am using version 6.3.4
  • create a new Web Project in Visual Studio and after add references to MySql.Data and MySql.Web
  • the MySQL .NET Connector will modify your machine.config file after installation and will add new entries in the membership, roleManager and profile providers. You will need to locate the machine.config file under the following location: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG and edit it. You will need to add one extra attribute to the MySql Membership Provider defined in there: autogenerateschema=“true”.  The autogenerateschema=“true” attribute will cause MySQL Connector/NET to silently create, or upgrade, the schema on the database server, to contain the required tables for storing membership information. After this change the MySql Membership Provider in the  machine.config file should look like this:
XML
  1. <add name="MySQLMembershipProvider"
  2. autogenerateschema="true"
  3. connectionStringName="LocalMySqlServer"
  4. ... />
  • one other change made in the machine.config file by the MySQL .NET Connector is in the connectionsStrings section where a new connection for MySql is added:
XML
  1. <add name="LocalMySqlServer" connectionString="" />

We will need to override this connection string in our web.config in order to specify the details on the MySQL database used to create the tables structure that supports the membership and roles providers. Because the membership and roles providers defined in the machine.config file use the already defined MySql connection string, we will need to remove it and then add it again. Our connectionStrings section in web.config file should look like:

XML
  1. <connectionStrings>
  2. <remove name="LocalMySqlServer"/>
  3. <add name="LocalMySqlServer"
  4. connectionString="Datasource=localhost;Database=db_name;uid=user;pwd=password;"
  5. providerName="MySql.Data.MySqlClient"/>
  6. </connectionStrings>
  • although this step can be done from the Website Administration Tool, I prefer to do it directly in the web.config file as it’s very simple. In order to support forms authentication, locate the authentication section in your web.config file and change the mode attribute from Windows to Forms:
XML
  1. <authentication mode="Forms"/>
  • open the Website Administration Tool and switch to the Provider tab and then select the Select a different provider for each feature (advanced) link to set the providers. You should see the MySQLMembershipProvider and MySQLRoleProvider listed. I selected both of them as I want both membership and roles to be stored in my MySQL database. After you close the Administration Tool, your web.config file should be get modified and you’ll notice that under the system.web section the following lines where added:
XML
  1. <roleManager defaultProvider="MySQLRoleProvider"/>
  2. <membership defaultProvider="MySQLMembershipProvider"/>
  • save your solution and build it. If you check your database you should see the tables structure generated:

tables

  • once you finish this step and want to go ahead and use the MySQL Membership and Roles providers in your code you should modify your web.config file once again. I replaced the above mentioned entries for role and membership sections with the entries found in the machine.config file:
XML
  1. <roleManager enabled="true" defaultProvider="MySQLRoleProvider">
  2. <providers>
  3. <clear/>
  4. <add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider"
  5. connectionStringName="LocalMySqlServer" applicationName="MysqlTest" />
  6. </providers>
  7. </roleManager>
  8.  
  9. <membership defaultProvider="MySQLMembershipProvider">
  10. <providers>
  11. <clear/>
  12. <add name="MySQLMembershipProvider" type="MySql.Web.Security.MySQLMembershipProvider"
  13. connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false"
  14. enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="MysqlTest"
  15. requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5"
  16. minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1"
  17. passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
  18. </providers>
  19. </membership>

Of course you can customize the attributes of the role and membership providers as you wish. I changed the applicationName attribute to be able to differentiate later between application entries in the tables.

  • from this moment you should be able to use Login related controls in your application. Just for testing purposes I added one CreateUserWizard control which works fine without writing  any code, and then I used one Login control for which I implemented the OnAuthenticate event as follows:
C#
  1. protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
  2. {
  3. bool authenticated = false;
  4. authenticated = Membership.ValidateUser(Login1.UserName, Login1.Password);
  5. e.Authenticated = authenticated;
  6.  
  7. if(authenticated)
  8. {
  9. FormsAuthentication.SetAuthCookie(Login1.UserName, true);
  10. Response.Redirect("Secret.aspx");
  11. }
  12. else
  13. {
  14. // do some error handling
  15. }
  16. }

In order to implement this I followed as references:

  1. http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-asp-roles.html
  2. http://blog.nolanbailey.com/2008/03/08/mysql-aspnet-membership-and-role-provider/194

Hope this comes useful for others as well!