How to configure MySQL for ASP.Net Membership/Roles Provider

If your web application heavily uses your SQL 2008 database and you want to implement ASP.Net Membership/Roles provider, you can use the MySQL database instead of SQL 2008. This should enhance application performance.

To get started, you will need to download the MySQL Connector/.Net from MySQL website and install it on your computer. When you have installed it on your computer you will need to navigate to the programs folder and under the MySQL/MySQLConnector/Assemblies copy the MySQL.Data and MySQL.web into your applications Bin folder.

Note: The MySQL.Data assembly is already installed on the Winhost GAC, but the version installed on the servers GAC is version 6.2.3.0. Unfortunately the MySQL.web is not installed on the servers GAC therefore you will need to upload it along with your web application in the Bin folder. You can either use the MySQL.Data installed on the server or also upload your own MySQL.Data assembly in the Bin folder. Either way should work. You simply need to reference or add the assemblies correctly. For this example I went ahead and used my own MySQL assemblies and uploaded it to the Bin folder. This is to provide uniformity.

Make sure you setup your MySQL database. You will need to know the MySQL server name, MySQL database name, the MySQL login, and MySQL password for your connection string.

Once this is done, you will need to modify your applications web.config file.

Portion for the connection string…

<connectionStrings>
<remove name="LocalMySqlServer"/>
<add name="LocalMySqlServer" connectionString="Server=MySQL server name; Database=MySQL database name; uid=db login; pwd=db password;" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>

Coding for the membership/provider/roles in the web.config file…

<membership defaultProvider="MySqlMembershipProvider">
<providers>
<clear />
<add name="MySqlMembershipProvider" type="MySql.Web.Security.MySqlMembershipProvider,MySql.Web,Version=6.3.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" autogenerateschema="true" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requireQuestionAndAnswer="false" requireUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="0" passwordStrengthRegularExpression="" applicationName="/" />
</providers>
</membership>
<profile defaultProvider="MySqlProfileProvider">
<providers>
<clear/>
<add name="MySqlProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.3.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" conectionStringName="LocalMySqlServer" applicationName="/"/>
</providers>
</profile>
<roleManager enabled="true" defaultProvider="MySqlRoleProvider">
<providers>
<clear/>
<add connectionStringName="LocalMySqlServer" applicationName="/" name="MySqlRoleProvider"
type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.3.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
</providers>
</roleManager>
<trust level="Full"/>
<machineKey validationKey="AutoGenerate" validation="SHA1"/>


Once you have finished with the web.config file, you can go to the ASP.Net Configuration tool under the menu Website and setup your user for the form login. Bear in mind when you go to the Provider tab and test the connection with your Membership Provider and Roles Provider you will get a connection error. Pay no attention to that. The reason is that ASP.Net Membership does not know how to use the MySQL.Data to connect to the MySQL server. You can still go to the Security tab and setup your users, create roles, and manage access rules. Infact you will need to run that first before you deploy your ASP.Net membership/roles provider on our server. When you run the Security settings, it triggers the autogenerateschema and setups all the required tables and objects on the MySQL database so that the login credentials will be stored on the MySQL server.

You will have a second option to create the membership/roles schema on the database other then auto generating it. If you download the source version of the MySQL Connector/Net, you will find a folder MySQL.Web/Providers/Properties. Inside that folder you will see 6 .sql files. Run them against your MySQL database and that will create all the required objects on the MySQL database for ASP.Net membership/roles to work.

One last note on this matter, the validation “SHA1” is very important. This has to be specifically defined on your web.config file. Otherwise you will not be able to log into your web application. According to the a forum post I found on MySQL, .Net changed its hashing algorithm from SHA1 to HMACSHA256. You will need to force MySQLMembershipProvider to use SHA1.

 

Why do you think, using MySQL, instead of SQL 2008 enhances application performance?

 

If you are using your SQL 2008 database for something else, you obviously want it to focus on that. Lets say you only have 1 SQL 2008, and you are using it for eCommerce to store and keep track of the items you are selling; now if you are also using it for membership/roles authentication then your database not only have to keep track of inventory but all the login and sign off that forms authentication makes. So it would be wiser to separate the two functions to two different databases.

If you are on the basic plan, you only get 1 SQL 2008 database. Well, why not use the MySQL database for your forms authentication? If you are on the Max or Ultimate plan you get more then 1 database. For the Max plan you are afforded 5 SQL 2008 databases. If you have 1 SQL 2008 available for ASP.Net Membership/roles provider then by all means use that. If you do not have a SQL 2008 database available you can still use MySQL.

I guess what you need to keep in mind is not that MySQL is better then SQL 2008, its that MySQL is available instead of using your SQL 2008 database to perform multiple duties.

 

Can you explain me a bit about "SHA1"? I can't find it in my web.config. I've followed your steps and I can create users and roles in Asp.net web site administration tool but under "Provider" tab it shows me the error "Could not establish a connection to the database."

 

For some reason ASP.Net Configuration Tools in VS will not read the provider if you are using MySQL. It can only see the provider for Microsoft SQL. But its there and it will work, it just can't be seen by ASP.Net Configuration Tools.

 

Yes, it works

Thank you very much.. !!!

 

"Once you have finished with the web.config file, you can go to the ASP.Net Configuration tool under the menu Website and setup your user for the form login."

Does anyone know where they are referring to "under the menu Website". Is this a Winhost "menu" some place?

 

Yes, I do. It refers to the Website Administration Tool (aka, WAST) in Visual Web Developer or Visual Studio.

You would be opening the website, then click "Website" on the toolbar. On the dropdown menu that opens up, click "ASP.NET Configuration" all the way at the bottom.

It sounds like you are just like me in that when following various tutorials and instructions, it is hard to determine "where they are" at any given point.

Part of the learning curve....