Warning: Not a good idea on a production database. I use this only for local dev environments when I have virtual machines running via a bridged connection and DNS changes often.
Log into MySQL on the server machine via the command line
mysql -u root -p
Using the MySQL wildcard ‘%’ in place of an IP
GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
If you are running into issues with the MySQL .Net Connector (any version) specially when it comes to user login after registration you may need to added some additional configuration to your web.config file.
I ran into a strange situation where a web application which was setup using the MySQL .Net Connector 6.3.0.0 for membership would allow a new user to register without an issue but once that user logs out and then attempts to log back in all they get is the “Your login attempt was unsuccessful” error message. I quadruple checked the web.config and made sure that the membership, roles, and profile providers for MySQL membership were all setup correctly. They were perfect! I finally in a ditch effort changed the password format from ‘Hashed’ to ‘Clear’, wiped out my membership database and recreated it. Amazingly, that fixed it, my users could register the same as before but this time they could actually login once logged out.
I did some Google searches on the strange difference between ‘Clear’ and ‘Hashed’ passwords and the MySQL .Net Connector and found that it relied heavily on the ‘MachineKey’ entry and was expecting a ‘ValidationKey’ and ‘DecryptionKey’ to be explicitly set. since I am running multiple applications on my production servers and since they are all using different keys in the farm I decided it would be best to place the ‘MachineKey’ entry in the web.config for each of my applications in order to allow them each to have their own keys.
Once I added the ‘MachineKey’ with the explicitly set values I switched the membership provider from ‘Clear’ to ‘Hashed’ password format, cleared out my membership database and retried registering and logging in users. This completely fixed my issue. Below is the entry that needs to be added to your web.config.
<machineKey
validationKey=”yourkeygoeshere”
decryptionKey=”yourkeygoeshere”
validation=”SHA1″
decryption=”AES”/>
When you install the MySQL Connector it adds several configuration entries to your Machine.config. By default the newly installed Membership provider has been set up for a development environment.
The main reason I have come to this conclusion is that the MySQLMembershipProvider configuration entry has the passwordFormat attribute set to ‘Clear’ and the applicationName attribute set to ‘/’.
Just like in the ASP.net Membership provider the MySQL provider allows multiple applications to use the Membership database by keying off the applicationName attribute configured in the Provider. Being that the provider entry is added to the Machine.config file the default applicationName attribute of ‘/’ will be used for all applications using the provider. This is fine if you want the same users to have access across all of the applications using the Membership database. But if your plan is to use a single membership database across multiple applications but would like to keep the users segregated this setup will not work.
I also mentioned that the passwordFormat is set to ‘Clear’. This means that all passwords will be saved in the database in clear text. This of course is only to be used during development to you can pull test user’s passwords when you forget them. In a production environment that passwordFormat should be set to ‘Hashed’. This of course forces the password and the password recovery question’s answer to be hashed using a cryptographic algorithm, thus protecting the user’s password from anyone including rogue application and database administrators or hackers that manage to infiltrate the database.
To override the MySQL Membership Provider’s default settings you need to add the following to each of your application’s web.config files.
<membership defaultProvider="MySQLMembershipProvider">
<providers>
<remove name="MySQLMembershipProvider"/>
<add name="MySQLMembershipProvider" autogenerateschema="true" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="GndCoreMembership" requiresUniqueEmail="True" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
</providers>
</membership>
<profile>
<providers>
<remove name="MySQLProfileProvider"/>
<add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
</profile>
<roleManager enabled="true" defaultProvider="MySQLRoleProvider" >
<providers>
<remove name="MySQLRoleProvider"/>
<add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
</roleManager>
The ‘remove’ element is necessary before you add the provider because it already exists in your machine.config file. If you don’t the first attempt to access a protected resource will fail with a configuration error stating the provider already exists.
With the arrival of the MySQL .Net 6.1 Connector you now have an alternative to Microsoft SQL server or SQL Server Express when it comes to using the new Microsoft Entity Framework.
This new connector library is provided free directly from the MySQL website and actually integrates with Visual Studio 2008. Simply download the installer, install and you will now have access to your MySQL databases directly from within the Visual Studio Server Explorer.
By having access to your MySQL databases from the Server Explorer within Visual Studio you can simply drag and drop tables directly onto your Entity model.
If you are like me you can’t afford a production version of Microsoft SQL Server and even more crazy you can’t afford a license for each processor you have running in your production server. There is no way you are going to use SQL Server Express because you need to allow more than one application to hit the membership database and you can’t stand the file locking that occurs on all express database files.
That being said the best alternative and one I really really like is good old MySQL Community Server, it is amazing! An what is even more cool is that MySQL comes with a set of pre-built .Net connectors which you can download from their site and install. When you install the .Net connector it adds config entries to your Machine.config for all of the .Net Membership, Role and Profile providers they created for MySQL. Once this is done your system is ready to access all .Net membership using the MySQL DBMS. Simply amazing.
To set up your ASP.net website to use the MySQL membership providers you have a couple of config entries to add to your Web.config file. they are as follows:
1. Set up your Connection String
<connectionStrings>
<remove name="LocalMySqlServer"/>
<add name="LocalMySqlServer"
connectionString="Datasource=YOURSERVER;Database=YOURDATABASE;uid=YOURUSERNAME;pwd=YOURPASSWORD;"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
2. Set your default Membership Provider and Role Provider
<membership defaultProvider="MySQLMembershipProvider" />
<roleManager enabled="true" defaultProvider="MySQLRoleProvider" />
3. Optional but extremely helpful on your development machines is to set up the MySQL membership provider to install the membership schema in the database automatically if it does not exist. Saves you from manually running scripts. After installation of the .Net Connector go to your Machine.config and locate the system.web section, within it you should see entries for Membership, Profile and RoleManager. In addition to the default ASP.net providers that come with .Net you will see a new entries in each of those config sections for MySQL. Locate the MySQLMembershipProvider and add a new attribute autogenerateschema=”true”. It should look something like this:
<membership>
<providers>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
<add name="MySQLMembershipProvider" autogenerateschema="true" type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
</providers>
</membership>
<profile>
<providers>
<add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="MySQLProfileProvider" type="MySql.Web.Profile.MySQLProfileProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
</profile>
<roleManager>
<providers>
<add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="MySQLRoleProvider" type="MySql.Web.Security.MySQLRoleProvider, MySql.Web, Version=6.1.2.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/" />
</providers>
Now you should be able to open the website administration tool from within Visual Studio and walk through setting up Users and Roles as well as Access Rules just like you would if you used the built in ASP.net membership providers.
Good Luck!
MemoryTin 