22.2.4.2. Tutorial: MySQL Connector/Net ASP.NET Membership and Role Provider

Create a new database in the MySQL Server using the MySQL Command-Line Client program (mysql), or other suitable tool. It does not matter what name is used for the database, but record it. You specify it in the connection string constructed later in this tutorial. This database contains the tables, automatically created for you later, used to store data about users and roles.

Create a new ASP.NET Web Site in Visual Studio. If you are not sure how to do this, refer to Section 22.2.4.6, "Tutorial: Databinding in ASP.NET Using LINQ on Entities", which demonstrates how to create a simple ASP.NET web site.

Add References to MySql.Data and MySql.Web to the web site project.

Locate the machine.config file on your system, which is the configuration file for the .NET Framework.

Search the machine.config file to find the membership provider MySQLMembershipProvider.

Add the attribute autogenerateschema="true". The appropriate section should now resemble the following (note: for the sake of brevity some information has been excluded):

<membership> <providers>   <add name="AspNetSqlMembershipProvider"      type="System.Web.Security.SqlMembershipProvider"      ...      connectionStringName="LocalSqlServer"      ... />   <add name="MySQLMembershipProvider"      autogenerateschema="true"      type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"      connectionStringName="LocalMySqlServer"      ... /> </providers></membership>

Note that the name for the connection string to be used to connect to the server that contains the membership database is LocalMySqlServer.

The autogenerateschema="true" attribute will cause MySQL Connector/Net to silently create, or upgrade, the schema on the database server, to contain the required tables for storing membership information.

It is now necessary to create the connection string referenced in the previous step. Load the web site's web.config file into Visual Studio.

Locate the section marked <connectionStrings>. Add the following connection string information:

<connectionStrings>  <remove name="LocalMySqlServer"/>  <add name="LocalMySqlServer"       connectionString="Datasource=localhost;Database=users;uid=root;pwd=password;"       providerName="MySql.Data.MySqlClient"/></connectionStrings>

The database specified is the one created in the first step. You could alternatively have used an existing database.

At this point build the solution to ensure no errors are present. This can be done by selecting Build, Build Solution from the main menu, or pressing F6.

ASP.NET supports the concept of locally and remotely authenticated users. With local authentication the user is validated using their Windows credentials when they attempt to access the web site. This can be useful in an Intranet environment. With remote authentication, a user is prompted for their login details when accessing the web site, and these credentials are checked against the membership information stored in a database server such as MySQL Server. You will now see how to choose this form of authentication.

Start the ASP.NET Web Site Administration Tool. This can be done quickly by clicking the small hammer/Earth icon in the Solution Explorer. You can also launch this tool by selecting Website, ASP.NET Configuration from the main menu.

In the ASP.NET Web Site Administration Tool click the Security tab.

Now click the User Authentication Type link.

Select the From the internet radio button. The web site will now need to provide a form to allow the user to enter their login details. These will be checked against membership information stored in the MySQL database.

Figure 22.42. Authentication Type

You now need to specify the Role and Membership Provider to be used. Click the Provider tab.

Click the Select a different provider for each feature (advanced) link.

Now select the MySQLMembershipProvider and the MySQLRoleProvider radio buttons.

Figure 22.43. Select Membership and Role Provider

In Visual Studio, rebuild the solution by selecting Build, Rebuild Solution from the main menu.

Check that the necessary schema has been created. This can be achieved using the mysql command interpreter.

Figure 22.44. Membership and Role Provider Tables

Assuming all is present and correct, you can now create users and roles for your web application. The easiest way to do this is with the ASP.NET Web Site Administration Tool. However, many web applications contain their own modules for creating roles and users. For simplicity, the ASP.NET Web Site Administration Tool will be used in this tutorial.

In the ASP.NET Web Site Administration Tool, click the Security tab. Now that both the Membership and Role Provider are enabled, you will see links for creating roles and users. Click the Create or Manage Roles link.

Figure 22.45. Security Tab

You can now enter the name of a new Role and click Add Role to create the new Role. Create new Roles as required.

Click the Back button.

Click the Create User link. You can now fill in information about the user to be created, and also allocate that user to one or more Roles.

Figure 22.46. Create User

Using the mysql command interpreter, you can check that your database has been correctly populated with the Membership and Role data.

Figure 22.47. Membership and Roles Table Contents