MySQL ASP.NET Membership and Role Provider
At this time, there doesn’t seem to be a good guide published on setting up your .NET web application to use MySQL as your ASP.NET Membership Provider.
The following worked for me. However, no warranty is expressed or implied. 
- Download and install MySQL Connector/Net 5.2.1 or later version.
- Add a reference to MySQL.Web to your web application.
C:\Program Files\MySQL\MySQL Connector Net 5.2.1\Web Providers\MySql.Web.dll - Add the autogenerateschema=”true” attribute. Since the MySQL database schema wasn’t automatically created for me, I ended up using the autogenerateschema attribute. The attribute will signal the MySQL provider to build (or upgrade) the database schema.The MySQL 5.2.1 release notes state the following…
Using the new provider schema
=============================
For this release. the only way to upgrade a given server to the new schema is to
add a configuration option for one of your providers. The option is ‘autogenerateschema’.
By setting this to true, the provider will silently upgrade the server to the new schema.
Please note that there is no reversing of this procedure so please just do this on test
setups and not on your production systems.Personally, I found it easiest to just add autogenerateschema=”true” to my machine.config on my development machine (as opposed to web.config) and it’s under providers…
<membership>
<providers>
<add name=”MySQLMembershipProvider” autogenerateschema=”true” ….
</providers>Save the changes.
- Edit your web application’s web.config.
<connectionStrings>
<remove name=”LocalMySqlServer”/>
<add name=”LocalMySqlServer” connectionString=”Datasource=localhost;Database=DB;uid=Username;pwd=Password;”
providerName=”MySql.Data.MySqlClient”/> </connectionStrings>
Save the changes. - Build your Web Application.
- Now, administer/config your web application.
Under the ASP.NET Web Site Administration Tool provider tab, click “Select a Different Provider (advanced)” and change the provider to MySQLMembershipProvider.
At this point, you should be able to use MySQL as your ASP.NET Membership and Role Provider (the tables will be automatically built for you).
After the tables are built, you’ll want to setup your web application’s web.config (using your machine.config as a template) so that you will have access to all of the membership provider settings.
29 Responses to “MySQL ASP.NET Membership and Role Provider”
Discussion Area - Leave a Comment
You must be logged in to post a comment.
Hi
Thanks for a very useful blog.
I got so far that the tables are autogenerated, but I can’t figure out what you mean by the last sentence:
After the tables are built, you’ll want to setup your web application’s web.config (using your machine.config as a template) so that you will have access to all of the membership provider settings.
Would you please give an example of web.config where the providers are working
Niels
Hi Niels,
Once your tables are setup, you’ll want to customize your web.config and add something like the following …
<roleManager enabled=”true” defaultProvider=”MySQLRoleProvider”>
<providers>
<clear/>
<add name=”MySQLRoleProvider”
type=”MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”
connectionStringName=”LocalMySqlServer”
applicationName=”LoginControl” />
</providers>
</roleManager>
<membership defaultProvider=”MySQLMembershipAppProvider”>
<providers>
<clear/>
<add name=”MySQLMembershipAppProvider”
type=”MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”
autogenerateschema=”true”
connectionStringName=”LocalMySqlServer”
enablePasswordRetrieval=”false”
enablePasswordReset=”true”
requiresQuestionAndAnswer=”false”
applicationName=”LoginControl”
requiresUniqueEmail=”true”
passwordFormat=”hashed”
maxInvalidPasswordAttempts=”7″
minRequiredPasswordLength=”7″
minRequiredNonalphanumericCharacters=”1″
passwordAttemptWindow=”10″
passwordStrengthRegularExpression=”"
/>
</providers>
</membership>
Thanks for the information, I greatly appreciate it. However, I need some help here. This is the first time I’ve worked with Membership, and I’ve only been a developer for about a year, so if in answering, you could explain like you would a 3 year old, that would be great, lol. I followed all the steps above. I don’t see that it is auto generating the schema. And after I added the code above that you posted in the comments for Niels, I get an error I downloaded the connector and added the reference. Below is my error. It shows line 72 as being in red.
Server Error in ‘/’ Application.
——————————————————————————–
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Could not load file or assembly ‘MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ or one of its dependencies. The system cannot find the file specified.
Source Error:
Line 70:
Line 71:
Source File: C:\Documents and Settings\bsouth\My Documents\Visual Studio 2005\Projects\EdgarsBakeryOrders\EdgarsBakeryOrders\web.config Line: 72
Assembly Load Trace: The following information can be helpful to determine why the assembly ‘MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ could not be loaded.
=== Pre-bind state information ===
LOG: User = ACCELLERA\bsouth
LOG: DisplayName = MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d
(Fully-specified)
LOG: Appbase = file:///C:/Documents and Settings/bsouth/My Documents/Visual Studio 2005/Projects/EdgarsBakeryOrders/EdgarsBakeryOrders/
LOG: Initial PrivatePath = C:\Documents and Settings\bsouth\My Documents\Visual Studio 2005\Projects\EdgarsBakeryOrders\EdgarsBakeryOrders\bin
Calling assembly : (Unknown).
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: C:\Documents and Settings\bsouth\My Documents\Visual Studio 2005\Projects\EdgarsBakeryOrders\EdgarsBakeryOrders\web.config
LOG: Using machine configuration file from c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d
LOG: Attempting download of new URL file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/Temporary ASP.NET Files/root/b4defa2f/8bc11252/MySql.Web.DLL.
LOG: Attempting download of new URL file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/Temporary ASP.NET Files/root/b4defa2f/8bc11252/MySql.Web/MySql.Web.DLL.
LOG: Attempting download of new URL file:///C:/Documents and Settings/bsouth/My Documents/Visual Studio 2005/Projects/EdgarsBakeryOrders/EdgarsBakeryOrders/bin/MySql.Web.DLL.
LOG: Attempting download of new URL file:///C:/Documents and Settings/bsouth/My Documents/Visual Studio 2005/Projects/EdgarsBakeryOrders/EdgarsBakeryOrders/bin/MySql.Web/MySql.Web.DLL.
LOG: Attempting download of new URL file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/Temporary ASP.NET Files/root/b4defa2f/8bc11252/MySql.Web.EXE.
LOG: Attempting download of new URL file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/Temporary ASP.NET Files/root/b4defa2f/8bc11252/MySql.Web/MySql.Web.EXE.
LOG: Attempting download of new URL file:///C:/Documents and Settings/bsouth/My Documents/Visual Studio 2005/Projects/EdgarsBakeryOrders/EdgarsBakeryOrders/bin/MySql.Web.EXE.
LOG: Attempting download of new URL file:///C:/Documents and Settings/bsouth/My Documents/Visual Studio 2005/Projects/EdgarsBakeryOrders/EdgarsBakeryOrders/bin/MySql.Web/MySql.Web.EXE.
——————————————————————————–
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Could not load file or assembly ‘MySql.Web, Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d’ or one of its dependencies.
The system cannot find the file specified.
Hi Brian,
I’m assuming you installed the .NET connector for MySQL and that you didn’t install version 5.2.1.0.
In your web.config, you need to change the version # and publickeytoken to match what is installed on your machine.
You will be able to find the correct version # and publickeytoken from your machine.config.
Once you update your web.config to the correct version # and publickeytoken, it should work and you should be good to go.
Hope this helps…
Cool, thanks. One more question. How do I know if it auto generated the schema? Will it add it to my existing database?
Please ignore previous comment, I got it working. Now I just need to figure out how to add users.
Now I just need to figure out how to add users.
Hi Brian,
You’ll want to use the the ASP.NET 2.0 Login controls: Login, LoginView, PasswordRecovery, LoginStatus, LoginName, CreateUserWizard, and ChangePassword.
MSDN : CreateUserWizard
http://tinyurl.com/4rm84b
Hi
Thanx for great article, works like a charm
It really helped me with my project. I still have a little problem - passwordFormat=”hashed” seems not to work, program executes correctly only if I remove this line… what am I doing wrong?
hi nagrobek,
did you customize your web.config as indicated in the above comments? do you have a specific error message displayed?
Hi nolan,
Followed this nice article but did not managed to get things working.
Do you know if Connector/Net has any problems with .Net 3.5?
In the ASP.NET Configuration page I get the message: “Could not establish a connection to the database.”
In my web.config I’m using a connectionstring that works fine in the application when using MySql.Data.MySqlClient.MySqlConnection
Hi andreas,
I quickly setup a new project with 3.5 framework and did not have any problems with 3.5 framework.
However, I haven’t added the login controls to the page, but I’m able to admin the site, etc. etc. through the project.
It sounds like a simple problem…
for starters…
in your web.config, is your connectionStringName (under roleManager provider and under membership provider) set to the correct database name ?
For example, if your connection string name is “MySlickDB” then your connectionStringName should be MySlickDB too (under the roleManager AND the Membership providers section in your web.config).
However, with .NET 3.5 Framework and the .NET connector 5.2.2…..the VS Query Builder will throw the following exception often.
I’m assuming it’s a bug with 3.5 framework and .NET connector 5.2.2. The MySQL .NET connector documentation indicates only 2.0 framework is supported at this time.
This blog is great. It’s just what I’ve been looking for and has probably saved me quite a bit of work.
I’ve got as far as the bit just before editing my web.config like you showed Niels.
Everything seems to be working pretty much. I can create users and log in with them.
The one little snag I’ve got is using your example that you replied to Niels, but modding for my config: Version=5.1.6.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D”
Everything seems ok except when I go to “Security” it complains that it doesn’t support the password format.
Presumably it doesn’t like “hashed”. Do you know why this might be and where I could get an idea of what this version does support?
I forgot to mention, I’m stuck using MySQL 4.1 as that is what is on the company server at the moment.
I think I’ve spotted the problem…sorry to waste your time
I changed “hashed” to “Hashed” with a capital ‘H’ and things seem to be working again.
hi fooboo,
As a result of your question, I grabbed the source code for 5.2.2 .NET connector and 5.1.6 .NET connector.
From a cursory examination, the following appears to have support in both…
“hashed” MembershipPasswordFormat.Hashed;
“encrypted” MembershipPasswordFormat.Encrypted;
“clear” MembershipPasswordFormat.Clear
You might try downloading/installing the latest MySQL (on your workstation) and try it out… just to see if it’s a difference in MySQL versions (4.1 vs. latest/greatest) ?
If you find out anything, I’d be interesting in hearing about it…
–nolan
It was the ‘h’ in hashed that was causing the problem.
Once I’d capitalized it all seems to be working fine.
We have to develop for 4.1 still as our sever hasn’t got the latest version installed. I’m hoping we’ll upgrade soon though so I can take advantage of the newest features etc.
Cool, thanks for the follow-up information…
–nolan
This setup works fine for my development machine. I have a site developed and it all seems to work locally but when I upload it to our server i get “Unable to check current provider schema”.
I’ve been wracking my brains and calling in help to no avail. We can’t see why it wont work on the server set up exactly as it is on my pc.
Have you come across this?
Oh, one more thing …
I have also installed connector 5.1.6 as well to see if it will work, and nothing. It does not let me see/select the DB in the Database Explorer window.
Therefore, (correct me if im wrong) the only option i have is to code this in C#. (well or VB if the person wishes)
Thanks
Naren
i’ve been extremely busy lately.
fooboo…
did you figure this one out and would you mind sharing your solution for others?
you probably have done what I’ll suggest (i’ll assume the provider is installed on the server and it’s the same version as your workstation)… double check that the database schema is correct and that your web.config is correct (no references/changes that you’re missing in there).
narengokal…
you’ll need to manually add your local mysql database instance to your Visual Studio server explorer (use the mysql provider).
if you don’t see the server explorer in Visual Studio, use the “View -> Solution Explorer” command to display the server explorer.
Nope
I upgraded to 5.2.2 to see if the schema changes there fix this problem but with 5.2.2 I get:
Parser Error Message: Unable to initialize provider. Missing or incorrect schema
Almost the same thing.
I created the tables by dumping my local working setups db and restoring on the server so it definitely has all the right tables for the schema but still it can’t find it.
The only thing I can’t do (the sysad wont let me) is put autogenerateschema = true in the machine.config to see if letting it generate the schema means it can find it.
Okay, mine is sorted out .. I did not know that in order to use the tool and see your MYSQL database in database explorer, u need to also download the ODBS driver for MYSQL. I only installed the COnnector, which is ONLY used for access using code, and not used for the tool.
Thanks
Naren
I still don’t know what’s causing the problem with mine but I’ve found a sort of solution. I switched to using a local db that I set up rather than the db on a separate server that our server guy set up (we have one server for websites and a super duper one for all the dbs) and my one works fine with it.
I now have a similar problem on some rented hosting that I don’t have the same level of access to. This time it’s MySql.Data it can’t find…here I go again
Hello,
I’ve been wrestling with this recently - only using the MySQL Connector/Net 5.2.3 .
Version 5.2.3 does not respond to the autogenerateschema=”true” attribute. After many hours I was forced to try version 5.2.2 which worked according to your advice.
You can’t get it through the MySQL site without a fight - I found it mirrored (officially) at http://mirror.csclub.uwaterloo.ca/mysql/Downloads/Connector-Net/mysql-connector-net-5.2.2.zip .
I’ll re-post this on the official site so that this bug won’t waste anyone else’s time.
Thanks again for your excellent post.
I checked the site. They are aware of this bug, and it will be fixed in 5.2.4.
Hey, Nolan,
I ran into the autogenerateschema problem with 5.2.3 and found a work around. I just finished writing it up in an article on my blog here:
http://www.marvinpalmer.com/MarvinPalmer/post/Implement-NET-Membership-and-Roles-using-MySql-Connector-523-on-GoDaddy.aspx
[...] on a remote machine, for a little insight in the working of the provider engine. And finally, Nolan Baily for the missing link. Now that the nasty part is done, the fun part can [...]