How to Setup and Configure MySql Membership Provider 6.2.2 – Porting to Mono Part 2 of 3

As the second part of this series, I’ll look at building a website that can use the MySql Membership Provider. This is one of the main hang-ups I’ve run into while porting one of my ASP.NET applications to Mono. With the latest MySql Connector, this turns out to be a very easy thing to do. It’s nearly as simple as setting up your web.config with the correct parameters and getting the right connector version.


Download the MySql Connector

Fortunately now, the MySql.Web assembly comes with the prebuilt versions of the MySql Connector/Net. If you don’t have it, go get it.  I recommend the Mono build, even for the Windows folks, because it’s a simple zip file with the assemblies. If you would like to see additional samples, download the Source version of their connector. When you have it downloaded, add a reference to MySql.Web in your web project.

 

Database

As with the Microsoft Sql Server membership provider, you have a choice to place your database tables in an existing application database or in its own database. I prefer to use its own just to keep it separated in case I want to share the Membership component among several applications. If you build upon my last post, you can simply create an empty database for your membership data.  Don’t create any tables yet. Just create an empty shell and create a user account for the application to connect your database and has access to create and alter tables. If you’re new to MySql, the MySql Administration Tools are extremely handy for this.

 

Configuration

The configuration options are mostly the same as the ones you’ve seen in the Microsoft Sql Server membership provider. This one has one added feature, which is an autogenerateschema flag.  This flag instructs the membership provider to actually push schema updates based on the version you’re running. To enable it, simply set it to true.  As with other membership configuration, once you have it enabled and configured to connect to your database, it should work immediately.

There are a few caveats that I ran into while configuring this. For example, hashed passwords only works on Windows .Net; not Mono. Encrypted passwords takes a little more configuration and also (through practice here) only seemed to work on Windows .Net.  There may be some bugs in my version of Mono 2.6.1 or maybe another configuration oddity I couldn’t identify.

Nonetheless, the MySql providers work 100% on Windows. If you’re running on Mono, your main option is to use Clear Text passwords; or if you want to get a little creative, download the source code to the MySql Connector and rewire how it runs its encryption through the framework and roll your own instead.

 

Configuration Tool

So moving forward, I built a tiny little configuration tool that you can use to generate the web.config sections you need for this. It takes into account some of my experiences with the things I mentioned like Hashed and Encrypted passwords. It will also auto-generate your MachineKey section if you choose to use encrypted passwords on the Windows side.

Launch Configuration Tool

Here is the default, Windows based configuration that is generated. You may use this and tweak it or generate your own.

<?xml version="1.0"?>
<configuration>
    <connectionStrings>
        <add name="MySqlMembershipConnection"
            connectionString="Data Source=server_name;user id=username;password=password;database=database_name;"
            providerName="MySql.Data.MySqlClient"/>
    </connectionStrings>

    <system.web>
        

        <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>

Testing Membership

So once you have your membership configured, you’ll need to test it out. First, don’t forget to update your connection string credentials. Then the easiest way to test your membership configuration (if you are using Visual Studio) is to use the ASP.NET Configuration Tool (appears when selecting your web project). Solution ToolbarWhen membership configuration is working properly the landing page will show you how many users are configured in the system. If there are none, it will still show you zero. You will also have access to add, remove, and update users from this screen.  If it doesn’t show you anything, then there’s likely something wrong in your configuration.

aspnetConfig

 

After the first time testing your membership, you’ll notice all the schema in the database will have been generated and is now filled with data. This was caused by the autogeneratechema flag being set to “true”.  As new versions of MySql.Web assembly are released from MySql, any schema changes new to each version will automatically be applied with this flag enabled. It should also alleviate that “Schema is missing or incorrect” error.

database

 

Wrap Up

So with that, you should have your ASP.NET application configured to run membership to a MySql database. If you’re using Mono, this configuration should work with the default ASP.NET MVC web application (after you reference the MySql assemblies and setup the configuration of course).  Look forward to my next post, which will be the final part of this series about porting an ASP.NET MVC application to Mono. We’ll be taking a very simple MVC application called SiteManager on CodePlex and convert it to run on Mono and MySql.

 

Useful Links





38 thoughts on “How to Setup and Configure MySql Membership Provider 6.2.2 – Porting to Mono Part 2 of 3

  1. Hi, I wish I’d found this post before. I actually manually set up my tables, because I read somewhere that the autogenerateschema switch didn’t work. Anyway, the problem I’m having now is that I’m always getting denied. (403) even though I’ve carefully set up my roles and checked the web.config file the Asp.Net tool creates. It seems to be something in WCF, since I’m actually checking membership in the Service class itself. Any ideas?

  2. I’ll shoot you an email. At first glance it seems like your web server is intercepting the request as opposed to Asp.Net membership handling it. If you’re using Windows/IIS, make sure your disk permissions allow the requesting user (usually IUSR_MachineName).

  3. Hi,

    I tried your code and it is the only one that get me close to the solution.
    However , when i am creating an account in the web site manager tool , i a getting a message , Your account was not created.
    But i do see that the tables users does have the new user where membership does not. and this is probably the problem. It unable to create an entry in membership table.

    Any idea?
    Thanks,
    Eli

  4. @Eli Arad

    There are a few tweaks required to make this configuration work. For example, editing the credentials and host information for the membership database is the first thing you need to do. Also adding an authorization configuration section should enforce authentication rules if any.

    If you’re getting a result where the user data is created by the membership data isn’t, it may be isolated to your environment.

    Try testing with a brand new database (empty schema). Be sure the membership connection string user account has create table access and ensure that the autogenerate schema is enabled (bottom option). (I would also double check that the user account can access the database from the host you’re using). I would also double check that you’re using the most recent build of the mysql.web and mysql.data assemblies (6.2.2) found here: http://dev.mysql.com/downloads/connector/net/#downloads (I’ve been using the Mono build to avoid the installation).

    Let me know if that helps.

  5. Just what I needed. I’m using Silverlight Business Template and I managed to configure everything, users can register (and everything gets written to database and it’s visible through asp.net configuration tool), but none of the users can login. I get “The username or password is incorrect”. I did relax password restriction in Silverlight (thought that might be problem), but still the same.

    Any idea what might be causing this?

    Thanks,

    Mario B.

  6. It took everything in angle brackets out it’s supposed to read:

    I would just like to add that for people with login problems. Take the web config file above and ad the
    *machineKey validationKey=”AutoGenerate,IsolateApps”
    decryptionKey=”AutoGenerate,IsolateApps”
    validation=”SHA1″/*
    directive ABOVE *system.web*. Then delete the db tables and run the site to autogenerate the schema. This is because any changes you make to the config file as regards hash algorithms etc are not applied to an existing schema. Also this mode of encryption uses the aplication name so be sure its set on EACH provider line before creating the schema. Then everything works as expected.
    Kudo’s OP.

  7. Nathan

    Thanks for taking the time to document this. It worked perfectly for me after I modified the “type” statement to refer to my particular MySql libraries:

    type=”MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.3.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”

    Magic!!

  8. @Jimbu
    You bet! Yeah that may have to be tweaked every once in awhile. Different versions of that mysql.web assembly are floating out there and they have a couple different assembly names. Sometimes you can get away with just referencing the type and assembly name like: type=”MySql.Web.Security.MySQLMembershipProvider, MySql.Web” It is case sensitive though; and on Linux Mono especially, you’ll run into trouble if the assembly name doesn’t match the filename case.

  9. Does anybody know a solution for password encryption? I cannot get it to work with Mono 2.8 and the MySQL MembershipProvider. It always returns an invalid password error message. Thanks for your help.

  10. Thank you for this post. I was able to create the aspnet schema and open the Web Site Administration Tool. I can create roles but, however, I get an error trying to create a user.

    I am using Visual Studio 2010 Pro, mySql.data and mySql.web 6.3.5.0, mySql 5.1 Server on a Windows 7 desktop.

    Any help would be appreciated.

    Thank you

  11. Hello,
    I don’t know if you can help me, I’m using mysql to manage membership using on my dev environment Mysql Connector, everything works fine but once I moved my web site on my production server( I got a hosting plan), I get the following error:
    Unable to initialize provider. Missing or incorrect schema.
    I create the db on the hosting server but I cannot figured out How I shlould set up the schema to make it work. Some help will be much appreciated. Thanks

  12. @Marzia

    One of the things built-in to the MySql connector’s membership provider is the ability to migrate schema for you. If you enable it with the attribute option, “autogenerateschema=true”. Set this on each of the membership section: membership, profile, and roles. Make sure the connection string you configured is using a mysql user account with full privileges to the database. Most hosting providers only give you one, so that shouldn’t be a problem. The tool above has an option to generate a configuration with autogenerate schema enabled if you want to see what it looks like in action.

    Hope that helps!

  13. Nathan, this was an awesome find. Thank you for putting it together and saving me countless hours searching the web or a solution.

    I do have one question. When using the ASP.Net Web Site Administration Tool is it normal that if you try and test the Providers on the Provider tab the test fails? Everything on with my membership appears to be working properly I just wanted to make sure there wasn’t something behind the scene that might not and cause me problems down the road.

    Thanks!!

  14. Hi Nathan,
    I was wondering how to work things for MySql. But now since I found this important blog of urs, I am going to perform all the above metioned activities tonight. I have just one question out of curiosity.

    I have downloaded Community edition of Mysql from http://www.mysql.com using windows installer. ASP.NET Membership generates bunch of Stored procedures automatically. Now we know that the syntax differs for MS Sql Server and MySql, by mentioning “autogenerateschema=true”, does the provider manages the schema for MySql? or its ASP.NET configuration tool that does that?

  15. @Gaurang Sounds good! Good luck!

    So here’s how the MySql membership works (as I recall):

    There’s an initialize method that is called anytime a Membership implementation is enabled and starts up. During initialize, it always checks the schema version. If autogenerateschema is set to “true” and the schema version doesn’t exist or needs to be updated, it will automatically apply any incremental (or full) updates to the database schema at that time. The caveat here is that your application credentials used by your membership data connection also require schema change access to the database.

    If the data connection does not have proper access to change schema or if the schema version in the current database needs to be updated but autogenerateschema is false, then you will see exceptions thrown.

    In a happy scenario, MySql Membership provider will automatically update your schema as needed and just plain work when you try to use it.

    Good luck!

  16. I had a few problems while trying to get this to work. I was referencing the Mysql.Web and Mysql.Data from the install path(C:\Program Files (x86)\MySQL\MySQL Connector Net 6.5.4), and not from the project/bin folder. When I did this, everything was working perfectly:)

    PS: I dont know if this made a different, but I also changed this: type=”MySql.Web.Security.MySQLMembershipProvider, mysql.web”
    to
    type=”MySql.Web.Security.MySQLMembershipProvider, Mysql.Web”

    Thanx for the tutorial!

  17. Hi @Helge,

    Nice work! Yeah, this library tends to change over time. And yes, the assembly name case does make a difference in case based on which version you’re using. I’ve noticed the source version builds a little differently than their released version. I think 6.2-something was live when I wrote this up.

  18. Maybe just an update, or perhaps just my build (doubtful), but my Machine Config shows “MySql.Web.Profile.MySQLProfileProvider” not “MySql.Web.Security.MySqlProfileProvider” for Profile Provider (6.4.4)

    MC entry:

  19. OK MC Entry didn’t come out (becuase of chevrons I gues, so here changed chavrons to hash signs)

    #add name=”MySQLProfileProvider” type=”MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.4.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d” connectionStringName=”LocalMySqlServer” applicationName=”/” /#

  20. Dear Friends,
    Thanks for your support. You saved my day. But after trying to register i am getting this error

    To call this method, the “Membership.Provider” property must be an instance of “ExtendedMembershipProvider”.

    Any help ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>