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.
42 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 [...]
Hi Nolan, I must confess I am confused: I am creating a site on ASP.NET (Visual Studio Express 2008) and I would like to use MySQL for my Membership Control Database. I saw your article and Rakotomalala Andriniaina’s article (http://www.codeproject.com/KB/database/mysqlmembershipprovider.aspx), but I got lost. What would be a very beginner’s tutorial sequence for get this done? Thanks!
hi falinhares… i’m thinking it doesn’t work with vs 2008. i’ve not been following the latest mysql .net connector releases lately because i became frustrated with exceptions (see above july 10th comment).
[...] nolanbailey.com – MySQL ASP.NET Membership and Role Provider [...]
Hi Nolan,
Following up on my previous question, is it possible to use this for managing user profiles also (for capturing additional information)?
Anthony
Hi:
very nice tutorial, . . . and I really needed it. I have gotten to the very end, where I’m suppose to be able to “use MySQL as your ASP.NET Membership and Role Provider (the tables will be automatically built for you).” When I flip to the Security Tab, it tells me “Unable to initialize provider. Missing or incorrect schema. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 145) “. I’m using the most recent connector, 5.2.5.
Any ideas what might be happening? I’m assuming that the schema was included as part for the 5.2.1 dll?
Thanks
Never mind. I didn’t realize that I needed to actually create the database first, then let the tool create the tables. In any case, great tutorial.
Nolan,
I too am struggling with getting asp.net and mysql providers talking correctly . I’ve tried to follow your blog posting carefully, and I think I have done so. Here is my web.config and a error from wsat which occurs when I click on security:
I think roleManager is ok, but membership is not happening.
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.
The following message may help in diagnosing the problem: Input string was not in a correct format. (c:\Practice\WebSite15_secureweb3\web.config line 55)
here is the offending line:
type=”MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=5.2.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”
and below it is in context.
PS, I’m using vs 2008
TIA
Larry
Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
\Windows\Microsoft.Net\Framework\v2.x\Config
–>
Hi, Nolan. Great article, and it got me going.
The only problem I’m having now is that while I can add a user, and add a user to a role (using the role provider “Roles.AddUserToRole”) the AddUserToRole duplicates the user in the database. In fact, I can add a user to a role, where the user didn’t exist, and it will create the user and then add it to the role. I’m assuming that’s not by design.
So if I do this:
Membership.CreateUser(“demoUser”,”demo1″)
Roles.AddUserToRole(“demoUser”, “sysadmin”)
I will end up with two demoUser users in the Users table.
Any thoughts?
I figured out the duplicate user problem after looking at the source.
I had not named the application in both Role and Membership sections of the web.config, so when it was checking to see whether to create a new user or use and existing one, it didn’t find the new user, as it was added to a different application in the Applications table.
Duh. Another good reason to name your application, and not leave the default.
Hi Nolen,
Thanks for the information. I almost have everything working. When I go into the ASP.NET Configuration tool and try to create a new User or Role, I get this exception:
Unable to connect to any of the specified MySQL hosts. at System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean isMembership, String methodName, Object[] parameters, Type[] paramTypes) at ASP.security_roles_manageallroles_aspx.BindGrid() at ASP.security_roles_manageallroles_aspx.Page_Load() at System.Web.Util.CalliHelper.ArglessFunctionCaller(IntPtr fp, Object o) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
I Googled the problem but couldn’t find a solution. I was hoping you could provide some info on this problem. Thanks for any help.
Here’s my configuration settings:
Windows XP Proffessional
Visual Studio 2005 Proffessional Edition
ASP.NET 2.0.50727
MySQL Server 5.0.67
MySQL Connector Net 6.0.3
My web.config sections (The user name and password in the connection string are not shown for security reasons. Also, I am running the MySQL Server on port 3307 and not the default port of 3306.):
Ok, well, I wasn’t able to post my web.config information but I did figure out the problem. It was pretty much my fault:
In the web.config file, under the roleManager / providers section, I had referenced the connection string incorrectly: I was using ‘connectionString’ when I should have been using ‘connectionStringName’. Anyway, long story short, its all working now.
hey – I eventually managed to get it all working – thank you!
Only issue left – is authenticating any exitsing account.
creating new users via the MS site util and through the created website is no problem, as is requesting passwords by email etc.
just logging in does not seem to work…believe it or not…
taking hashing off to clear text reveals the passwords correctly in the MYSQLMemebrship tables – but I cannot get any accounts to log in to the website.
any idea’s? have tried changing .Net cookie / URI handling, MySQL character sets etc – but with no luck.
Hello!
I’m using:
Win 7
Visual Studio Pro 2008
MySQL 5.1.39 (latest)
Connector /net 6.0.4 (with 6.1.2 was not able connect to DB-only me?)
(.NET Framework 3.5)
Problem that I get is when I’m trying to create an user.
1.) ASP.NET configuration > security > create user
–Error (Password length minimum: 7. Non-alphanumeric characters required: 1.)
2.) Run project > log on > register
–Error (Password question supplied is invalid. ON CODE _provider.CreateUser(userName, password, email, null, null, true, null, out status);)
help
tnx!