The Blog of Josh Stodola

I am a huge fan of MySQL, primarily because it is free and fast. While it does not have the endless ensemble of features that SQL Server does, MySQL can certainly hold its own. And it works perfect for personal projects because it is portable, not to mention that purchasing SQL Server practically requires mortgage. I use the MySQL GUI Tools for basic queries and the excellent .NET Connector for programmatibility, both of which are graciously provide for free. Can't beat it! However, I will not lie and say that everything is as seamless as it is with SQL Server. Projects like SubSonic are targeted for SQL Server, and while they claim to "support" MySQL, it seldom works as cleanly as the SQL Server demo videos. In this post, I'll walk you through setting up the Membership, Profile, and Role providers for ASP.NET using MySQL as the backend. I struggled with setting this up initially, and did not have any luck finding resources on the web, so I am doing my duty. My environment is this:

• Visual Studio 2008, .NET 3.5 SP1
• MySQL Server version 5.1, Connector/Net version 6.2.2
• Windows XP 64-bit

Once you have the .NET connector installed, you'll need to add a reference to MySql.Web.dll to your project. Then you are able to tweak the web.config file accordingly and add your provider references. They exist in the MySql.Web.Security namespace, with the exception of Profile (it has its own namespace). This is where I ran into problems because although I knew the name of the assembly, I did not know the rest of the required details. The default provider for ASP.NET in web.config looks like this…

<add name="AspNetSqlMembershipProvider"
     type="System.Web.Security.SqlMembershipProvider,
           System.Web,
           Version=2.0.0.0,
           Culture=neutral,
           PublicKeyToken=b03f5f7f11d50a3a"
     connectionStringName="ApplicationServices"
     enablePasswordRetrieval="false"
     enablePasswordReset="true"
     requiresQuestionAndAnswer="false"
     requiresUniqueEmail="false"
     passwordFormat="Hashed"
     maxInvalidPasswordAttempts="5"
     minRequiredPasswordLength="6"
     minRequiredNonalphanumericCharacters="0"
     passwordAttemptWindow="10"
     passwordStrengthRegularExpression=""
     applicationName="NAME_OF_YOUR_PROJECT"
  />

The type attribute is the most daunting. I can guess that the first part is the full reference to the assembly and the second one is the name of the DLL (the namespace). I can find the version of the MySQL DLL in the "Add Reference" dialog. I guess I can just try Culture=neutral, that sounds about right. But what about PublicKeyToken? What the hell is this? I tried a bunch of things, but ultimately it was never able to connect to MySQL successfully without a correct token value.

Getting the PublicKeyToken for a .NET Assembly (DLL)

Using the Visual Studio 2008 command prompt, type in the following command:

sn -T "C:\Path\To\Assembly.dll"

And this will give you the PublicKeyToken value. Thank you, Google!

With that bit of information out of the way, I was ready to try getting the Security setup. In the affected sections of web.config, the important attributes are "connectionStringName" and "autogenerateschema". The connectionStringName should point to your connection string within the web.config file. The autogenerateschema will automatically generate your tables in MySQL (sweet!), but you only need this on the membership element. When it was all said and done, that section of my web.config file looked like this…

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

Now go ahead and launch the ASP.NET Configuration tool (from the Project menu). If you can click on the Security tab without seeing an error, you are golden! I haven't experienced any problems thus far, but if I do, I will be sure to update this post and/or add a comment.

Thanks for stopping by! Look for more posts to come in the near future.