ASP.Net has great build-in features for user roles and user management.
These features work out of the box perfectly with SQL server and SQL server express.
If you don’t have access to one of these database technologies you can use MySQL, this tutorial will show you how.
First of all, things you need:
- MySQL server (I use MySQL 4.1, since my host uses this one)
- Visual Studio 2008 or Web Designer express
- A MySQL server database
- MySQL .net connector http://www.mysql.com/downloads/connector/net/6.2.html (installer/windows version)
- A user with full access and privileges to the DB
Install the .Net connector on your asp.net test server (this can be your testmachine as well, as long as MySQL is running on it)
Once you got all of this set, start a new website in Visual Studio (File > New > Website)
Create the BIN folder in your solution (right click on your project > Add ASP.Net folder > Bin)
Right click the folder in your solution explorer > Add existing item
Navigate to Mysql.data.dll and Mysql.web.dll (default in C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Assemblies\)
In Visual Studio click the website menu > ASP.NET configuration
This will open a webinterface where you can set your application settings
Click Provider configuration >Select a different provider for each feature (advanced)
Select MySQLMembershipProvider and MySQLRoleProvider
Click Back
Close the browser for now
Navigate to your Machine.config file on your pc (C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG)
Search for
<defaultProvider=“MySQLMembershipProvider“>providers>remove name=“MySQLMembershipProvider“/>
<add name=“MySQLMembershipProvider“ type=“MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d“ connectionStringName=“LocalMySqlServer“ enablePasswordRetrieval=“false“ enablePasswordReset=“true“ requiresQuestionAndAnswer=“false“ applicationName=“/“ requiresUniqueEmail=“false“ passwordFormat=“Clear“ maxInvalidPasswordAttempts=“5“ minRequiredPasswordLength=“3“ minRequiredNonalphanumericCharacters=“0“ passwordAttemptWindow=“10“ passwordStrengthRegularExpression=“”/></providers></membership>
Cut this from machine.config and paste it in web.config in your project after the <system.web> tag
Add autogenerateschema="true" so it looks like this:
<membership defaultProvider=“MySQLMembershipProvider“>providers>remove name=“MySQLMembershipProvider“/>
<add name=“MySQLMembershipProvider“ autogenerateschema=“true“ type=“MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d“ connectionStringName=“LocalMySqlServer“ enablePasswordRetrieval=“false“ enablePasswordReset=“true“ requiresQuestionAndAnswer=“false“ applicationName=“/“ requiresUniqueEmail=“false“ passwordFormat=“Clear“ maxInvalidPasswordAttempts=“5“ minRequiredPasswordLength=“3“ minRequiredNonalphanumericCharacters=“0“ passwordAttemptWindow=“10“ passwordStrengthRegularExpression=“”/></providers></membership>
Search for <connectionstrings />
replace this with
<connectionStrings>
<remove name=”LocalMySqlServer”/>
<add name=”LocalMySqlServer”
connectionString=”Datasource=localhost;Database=users;uid=root;pwd=password;”
providerName=”MySql.Data.MySqlClient”/>
</connectionStrings>
edit this piece of code to match your DB, user and password
Press F6 to see if everything builds.
Go to Website > ASP.NET configuration > Security
Now you can create users, roles, select windows or web authentication, etc.
If you use a tool like HeidiSQL or phpmyadmin to take a look at your Database, you’ll see that Visual Studio filled it with a number of tables for users, roles, etc.
When first trying this I got this error when going to security in the config tool:
Unable to initialize provider. Missing or incorrect schema. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 145)
this was because my DB user in the connectionstring didn’t have sufficient privileges on the DB.
If you get errors there, try changing the DB user in the connection string to your root user, if that works, change the privileges on your DB user.
Don’t ever use your root account in a connectionstring!
If you liked this, or if you have questions, feel free to drop a comment!