Creating ASP.NET Membership Tables in a MySQL Database

Did you know you could use a MySQL database to store your ASP.NET Membership Table information rather than Microsoft SQL Server? I’ve written this simple guide to help you get started.

First, create a MySQL database through the Winhost Control Panel. Click on the Sites tab.

Click on the Manage link and then click on the MySQL button.

Click the Add button and enter the values for Database Name, Database User, and Quota. Click the Create button to finish creating the database. Now click on the Manage link and record the connection information (Database Name, Database Server, Database User, and Database Password) on a piece of paper or a text editor such as Notepad.

Start Visual Studio and create a New Project by going to File -> New –> Project… (Ctrl+Shift+N).

Add the necessary .NET assemblies to your project using NuGet. Go to TOOLS -> Library Package Manager -> Manage NuGet Packages for a Solution… Type mysql in the Search field and hit Enter. Install both the MySql.Data and MySql.Web assemblies. A check mark will appear to the right when the assemblies have been installed.

Another way to verify that the assemblies have been installed correctly is that they will appear as References in the Solution Explorer window.

When you compile your project, a copy of these assemblies will be added to your local /bin folder which you will need to upload to your site account.

The next step is to modify the Web.config file with this markup:

<?xml version="1.0"?>

<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->

<configuration>
  <connectionStrings>
    <add name="MySqlMembershipConnection"
         connectionString="Data Source=my02.winhost.com;user id=membership;password=password;database=mysql_48503_membership;"
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Logon"
             timeout="30"
             name=".ASPXFORM$"
             path="/"
             requireSSL="false"
             slidingExpiration="true"
             defaultUrl="Default.aspx"
             enableCrossAppRedirects="false" />
    </authentication>
    <membership defaultProvider="MySqlMembershipProvider">
      <providers>
        <clear/>
        <add name="MySqlMembershipProvider"
             type="MySql.Web.Security.MySQLMembershipProvider, mysql.web"
             connectionStringName="MySqlMembershipConnection"
             enablePasswordRetrieval="false"
             enablePasswordReset="true"
             requiresQuestionAndAnswer="false"
             requiresUniqueEmail="true"
             passwordFormat="Hashed"
             maxInvalidPasswordAttempts="5"
             minRequiredPasswordLength="6"
             minRequiredNonalphanumericCharacters="0"
             passwordAttemptWindow="10"
             applicationName="/"
             autogenerateschema="true"/>
       </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="MySqlRoleProvider">
      <providers>
        <clear />
        <add connectionStringName="MySqlMembershipConnection"
             applicationName="/"
             name="MySqlRoleProvider"
             type="MySql.Web.Security.MySQLRoleProvider, mysql.web"
             autogenerateschema="true"/>
      </providers>
    </roleManager>
    <profile>
      <providers>
        <clear/>
        <add type="MySql.Web.Security.MySqlProfileProvider, mysql.web"
             name="MySqlProfileProvider"
             applicationName="/"
             connectionStringName="MySqlMembershipConnection"
             autogenerateschema="true"/>
      </providers>
    </profile>
  </system.web>
</configuration>

Replacing the connection string section with the Winhost Connection String to your MySQL database. Save the file. The key to creating the tables is this attribute in the XML markup: autogenerateschema=”true” Now, select BUILD -> Build Solution (Ctrl+Shift+B) to compile your application, and then select PROJECT -> ASP.NET Configuration. This will launch the ASP.NET Web Site Administration Tool.

Once the tool has been launched, the tables will be created, and you can check by logging into your MySQL database using an administration tool like MySQL WorkBench.

Now, let’s build a very simple ASP.NET application that will allow you to create users. Go back to your project in Visual Studio, right click on your project name in Solution Explorer and select Add -> New Item… (Ctrl+Shift+A). Select Web Form and name the file. Switch to Design Mode. In the Toolbox Window, expand Login of the tree view, and drag and drop the CreateUserWizard control on to the page. Build your application.

Deploy your files to the Winhost server using FTP or Web Deploy and then access your site using a browser either with your domain name or Secondary URL. Fill out the create user form and click on the Create User button.

If you go back and access your database using MySQL WorkBench and query the my_aspnet_users table, you can see that the user was created.

You can also just use the ASP.NET Web Site Administration Tool to create the users. Of course, none of this information is necessarily new, so I want to thank Nathan Bridgewater’s blog and Oracle for helping me put together this tutorial for our customers at Winhost.