Using N2CMS with MySQL

As I have mentioned before in an earlier blog, when I am developing locally I like to use SQL Server 2008 primarily as it's the one I have most experience with. However, some of my clients can only use MySql.

To complicate matters further, I've recently had an order come through to build a site which will have to use the ASP.NET membership with MySql which is something I've not done before.

I'd recommend reading these blog posts first:

• http://www.integratedwebsystems.com/2010/02/how-to-setup-and-configure-mysql-membership-provider-6-2-2-porting-to-mono-part-2-of-3/ 
• http://blog.nolanbailey.com/2008/03/08/mysql-aspnet-membership-and-role-provider/194

Both were a big help in explaining what to do. 

However, I had to do a few tweaks first to get it working. Nothing too difficult but again it's the kind of thing which can eat time. So, if you want to get your N2CMS site working with ASP.NET Membership and MySql, here's my two minute guide:-

• Download the .NET connector from http://www.mysql.com/downloads/connector/net/ the version I am using is 6.2.3

• Add a reference to the MySql.Web.Dll which you will find in "c:\Program Files\MySql\MySql Connector Net 6.2.3\Assemblies"

• Change the N2CMS connectionstring to point to your MySql database (for this example I am just going to leave the of the connection string as "N2CMS" which is the default one)

• Replace your membership, roles and profiles nodes in the web.config with this:-

    <membership defaultProvider="MySqlMembershipProvider">
      <providers>
        <clear/>
        <add name="MySqlMembershipProvider"
        type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
        connectionStringName="N2CMS"
        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="N2CMS"
        applicationName="/"
        name="MySqlRoleProvider"
        type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
        autogenerateschema="true"/>
      </providers>
    </roleManager>

    <profile defaultProvider="MySqlProfileProvider">
      <providers>
        <clear/>
        <add
        type="MySql.Web.Profile.MySqlProfileProvider, MySql.Web, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
        name="MySqlProfileProvider"
        applicationName="/"
        connectionStringName="N2CMS"
        autogenerateschema="true"/>
      </providers>
    </profile>

If you are wondering how I got the version and PublicKeyToken, right click on the MySql.Web.dll in the solution explorer and click properties - this will give you the version. To get the PublicKeyToken, you will need to look in the c:\windows\microsoft.net\framework\v2.0.xxx\config\machine.config file and search for "MySql".

If you now run N2 you will get the installer and be able to install a new site! Note you can also manage the providers, members, roles etc from within Visual Studio with the ASP.NET Configuration tool. 

Once you've done this you may need to alter the rules on passwords etc, but that is beyond this two minute guide!

Hope this helps!