I recently completed implementing .NET Membership and Roles on an application I have hosted on GoDaddy and using MySQL .NET Connector v5.2.3 and MySQL v5.0. I ran into a few hitches along the way, and I figured I better get all this down while it's still fairly fresh in my head in case I ever need to do this again. This article is intended to be a fairly comprehensive walkthrough of how I did it.
While I do hope this helps someone else out there, I make no warranties about it. It's just what worked for me. Keep in mind that I am working in a Windows XP Pro environment on my development machine, using Visual Studio 2008 Professional and v3.5 of the .NET framework, and I am deploying everything to a Windows configuration on GoDaddy, also with v3.5 of .NET. I'm also going to reference a lot of other articles rather than duplicate the information, so you'll need to read those as well to get the complete picture.
Downloads: What You'll Need
First of all, there are a couple of things that you will need to download and install if you haven't already got them. I've included links here that, at the time of this writing, worked. You may need to login to the MySQL site to get these downloads, and of course, if the links don't work, you can hunt all these down on the MySQL site Downloads section.
-
MySQL Server 5.0 installed (download here)
-
MySQL GUI tools including MySQL Administrator and Query Browser (pick a mirror for download here)
-
MySQL Connector .NET v5.2.3 (download here)
There is a bug in Connector .NET v5.2.3 which affects implementation of Membership and Roles2. Of course, I found a workaround which is described in this article. The bug is reported to be fixed in v5.2.4, but at the time I did this, v5.2.4 wasn't available yet. If v5.2.4 is available, you may want to try that instead.
Setting Up Connections to Your MySQL Database
Ziran Sun wrote a very thorough article1 about using MySQL and ASP.NET. I found it very helpful and highly recommend it, even though it's a bit out of date. There are a few additional things that I had to do to get my application working though, so here's my walkthrough to get an application running on GoDaddy using MySQL. 
- Add references to the MySQL Connector .DLL's. In your solution explorer, right click the root folder and choose "Add Reference" (see picture at right). You will probably find the MySQL.Data DLL in your .NET tab. If not, you can go to the Browse tab and navigate to it. On my computer, it was located in the C:\Program Files\MySQL\MySQL Connector Net 5.2.3\Binaries\.NET 2.0 folder. You will also need a reference to the MySQL.Web DLL. On my computer, that was located in the C:\Program Files\MySQL\MySQL Connector Net 5.2.3\Web Providers folder.
- Place a copy of these DLL's in your Bin folder. If you don't have a Bin folder, just add it to your project by (again) right clicking on the root folder in Solution Explorer, choosing "Add ASP.NET Folder", and "Bin". Then go get those DLL files and copy them to the Bin folder.
- Copy DbProviderFactories node from your machine.config to your web.config. You won't need this until you try to deploy your application to GoDaddy. Reggie Burnett wrote an excellent explanation3 about this step on his blog. I won't be redundant. Go read his article about it. The only thing I will add is that I found my machine.config file on my computer in the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG folder.
- Configure your connection strings in your web.config file. I like to configure 3 connection strings.
- A local connection string to connect to the database on my laptop
- A remote connection string to connect to the database on GoDaddy (get this in your Hosting Control Panel - GoDaddy has some instructions7 for how to do this for Sql Server, but the steps to do this for MySQL are almost the same. I'm providing a graphic below to illustrate how I did this using my GoDaddy account.)
- A "current" connection string
<connectionStrings>
<remove name="LocalMySqlServer" />
<add name="LocalMySqlServer" connectionString="server=localhost;user id=root;password=<!-- whatever password you used when you setup MySQL server-->;persist security info=True;database=<!-- the name of your database -->"
providerName="MySQL.Data.MySqlClient" />
<add name="prodCS" connectionString="<!-- copy this from GoDaddy hosting control manager -->"
providerName="MySQL.Data.MySqlClient" />
<add name="currentCS" connectionString="<!-- copy this from either of your other connection strings -->"
providerName="MySQL.Data.MySqlClient" />
</connectionStrings>
I do this because, as I am working on an application, I like to deploy my stuff to GoDaddy often to see how my changes will look in production. If I am making frequent changes to the web.config, I will, of course, have to change my connection string in the web.config each time I deploy, and I don't want to have to remember it each time or go look it up. So, I keep the local and remote connection strings defined there as a reference only. In my application, I do all my database connections using my "current" connection string, and then I just copy the connect string from either local or remote to the "current" connection string in the web.config each time I move from local to remote or vice versa. Once I am done making changes to the web.config, I can leave everything as is, and both remote production and local development versions will work just fine because all my pages point to the "current" connection string. By the way, here is how I got my "prodCS" connection string for a MySQL database on GoDaddy.
If you need an article explaining how to create a new MySql database on GoDaddy, I have one here.
Add the ASP.NET Membership Schema to Your Local / Development MySQL Database
Nolan Bailey has an article4 that explains how to do this. However, because of the previously mentioned bug2, the autogenerateschema attribute wasn't working for me. To get around this, I downloaded the source code for v5.2.3 of the Connector (choose the "Sources" link on this page) and copied the sql code from the \MySQL-Connector-net-5.2.3-src\MySQL.Web\Providers\Properties folder. It's found in 4 files: "schema1", "schema2", "schema3", and "schema4". 
I simply copied these files in order into one file, put a "use <database name>" at the top of the code, and saved it as a sql file (see picture at right). For convenience, the sql script file I created as I've just described is here. To use it, you'll need to edit the first line and change the name of the database from "test" to whatever you've named your database.
To add the tables to my local database, I went into MySQL Administrator, opened MySQL Query Browser (Tools > MySQL Query Browser), chose File > Open Script..., and then selected my membershipSql.sql file off my hard drive. Once the script loaded, I simply clicked the "Execute" button (see picture below) to run the script and the Membership schema was added to my database without error.
At this point, I now had the standard ASP.NET Membership schema installed on my local development database. Let me just say, as an aside, that I first tried to do this with the MySQL Administrator Restore function which did not work for me. I also tried the mysqlimport command line tool without success. I also want to point out that, at this point, I am not trying to get this schema into my production database on GoDaddy. I'll do that later, but I'm going to make some more changes using the WebSite Administration tool which will affect the database, so I want to finish those before I move the database to GoDaddy.
Configure the Role and Membership Providers Using the WebSite Administration Tool
I went through some headaches with this, too. In the end, I think the secret is to use the WebSite Administration Tool (in Visual Studio, go to WebSite > ASP.NET Configuration) first to setup roles and membership before you ever touch the web.config file. When I opened up the tool, before doing anything else, I clicked the "Provider" tab and chose "Select a different provider for each feature (advanced)". See below.
If everything has gone right so far, you should see choices for MySqlMembershipProvider and MySqlRoleProvider. I chose both of these. See below.
From there, I went ahead and setup a couple of roles, created user accounts, setup some access rules. There's an excellent video6 on the ASP.NET website which covers how to use this tool to do these sorts of things as well as implementing the various ASP.NET Membership Web Controls in your application. I won't go into any of that here. Check out the video. Once you have the basic setup completed using that tool, then you can go into your web.config and customize if you want. If you don't have to mess with the web.config to accomplish your desired setup, then I found it best to leave it alone.
Export Your Completed ASP.NET Membership Schema to Your Remote / Production Database
Perhaps it's obvious now why I held off on this step, but if not, here's why.
The main reason is so I could use the ASP.NET Configuration tool to configure my user accounts. Once you deploy the database to production on GoDaddy, setting those accounts up is much more tricky. If you will need this ability, then you probably want to investigate implementing a Membership Management Console on your website. Peter Kellner has an excellent one5 that I have used, and I now have an article on this blog describing how I recently implemented it using MySql. At any rate, this time around, I decided to create all my accounts first on the local database. Once I did that, I had to get my database from my local machine out to GoDaddy. To do this, I used the MySQL Administrator Backup utility. See below.
This tool creates a sql script that, when executed, will create an exact copy of your database. The first time I executed the script on GoDaddy, the tables all got added, but I still ran into problems with my application. It took me awhile to figure out why, but I eventually discovered that it was a case-sensitivity issue. MySQL Administrator, by default, forces table names to all lower-case. The v5.2.3 of the MySQL .NET Connector defines the providers with table names like my_aspnet_Membership (notice the M in caps), and for some reason, my GoDaddy MySQL 5.0 db is case-sensitive. So, although the script executed without error and created the database on GoDaddy's server, I was still getting errors in my application because the tables weren't being recognized due to all lower case table names. To fix it, I just went into the script file which has been generated by MySQL Administrator's Backup utility and manually edited the table names to match what was in the sql source code for the MySQL.Web provider. Once I did that, I re-imported the script to GoDaddy and the application worked great.
To add the tables to my database on GoDaddy, I went into my Hosting Control Panel, opened up the database and used the "Import" function.
The next screen gives you a place to browse your hard drive to select the sql script file. At that point, I had my schema setup in production and was able to deploy my application with the various Membership controls and they all worked. Let me again reference the video6 on the ASP.NET website which I found to be very helpful for learning the various controls and how to implement them.
References
1 A Step-by-Step Guide To Using MySQL with ASP.NET By Ziran Sun
2 MySQL Bug Report #39072: Web Provider Does Not Work
3 Referencing ConnectorNet on a Remote Machine By Reggie Burnett
4 MySQL ASP.NET Membership and Role Provider By Nolan Bailey
5 Microsoft ASP.NET 2.0 Member/Role Management with IIS By Peter Kellner
6 VIDEO: How Do I Secure my Site using Membership and Roles?
7 Where can I find database connection strings for my MS SQL database?
Subscribe