Implementing a Custom MySQL ASP.NET Identity Storage Provider

Article
07/21/2022

by Raquel Soares De Almeida, Suhas Joshi, Tom FitzMacken

ASP.NET Identity is an extensible system which enables you to create your own storage provider and plug it into your application without re-working the application. This topic describes how to create a MySQL storage provider for ASP.NET Identity. For an overview of creating custom storage providers, see Overview of Custom Storage Providers for ASP.NET Identity.

To complete this tutorial, you must have Visual Studio 2013 with Update 2.

This tutorial will:

Show how to create a MySQL database instance on Azure.

Show how to use a MySQL client tool (MySQL Workbench) to create tables and manage your remote database on Azure.

Show how to replace the default ASP.NET Identity storage implementation with our custom implementation on a MVC application project.

This tutorial was originally written by Raquel Soares De Almeida and Rick Anderson ( @RickAndMSFT ). The sample project was updated for Identity 2.0 by Suhas Joshi. The topic was updated for Identity 2.0 by Tom FitzMacken.

Download completed project

At the end of this tutorial, you will have an MVC application project with ASP.NET Identity working with a MySQL database hosted on Azure.

You can download the completed MySQL storage provider at AspNet.Identity.MySQL (GitHub).

The steps you will perform

In this tutorial you will:

Create a MySQL database on Azure
Create the ASP.NET Identity tables in MySQL
Create an MVC application and configure it to use the MySQL provider
Run the app

This topic does not cover the architecture of ASP.NET Identity and the decisions you must make when implementing a customer storage provider. For that information, see Overview of Custom Storage Providers for ASP.NET Identity.

Review MySQL storage provider classes

Before jumping into the steps to create the MySQL storage provider, let's look at the classes that make up the storage provider. You will need classes that manage the database operations and classes that are called from the application to manage users and roles.

Storage classes

IdentityUser - contains properties for the user.
UserStore - contains operations for adding, updating or retrieving users.
IdentityRole - contains properties for roles.
RoleStore - contains operations for adding, deleting, updating and retrieving roles.

Data access layer classes

For this example, the data access layer classes contain SQL statements for working with the tables; however, in your code you might want to use object-relational mapping (ORM) such as Entity Framework or NHibernate. In particular, your application may experience poor performance without an ORM that includes lazy loading and object caching.

MySQLDatabase - contains the MySQL database connection and methods for performing database operations. UserStore and RoleStore are both instantiated with an instance of this class.
RoleTable - contains database operations for the table that stores roles.
UserClaimsTable - contains database operations for the table that stores user claims.
UserLoginsTable - contains database operations for the table that stores user login information.
UserRoleTable - contains database operations for the table that stores which users are assigned to which roles.
UserTable - contains database operations for the table that stores users.

Create a MySQL database instance on Azure

Log in to the Azure Portal.
Click +NEW at the bottom of the page, and then select STORE.
In the Choose and Add-on wizard, select ClearDB MySQL Database and click on the next arrow at the bottom right of the dialog.
Keep the default Free plan and change the Name to IdentityMySQLDatabase. Select the region nearest you and then click the next arrow.
Click the checkmark to complete the database creation.
After your database has been created, you can manage it from the ADD-ONS tab in the management portal.
You can get the database connection information by clicking on CONNECTION INFO at the bottom of the page.
Copy the connection string by clicking on the copy button and save it so you can use later in your MVC application.

Create the ASP.NET Identity tables in a MySQL database

Install MySQL Workbench tool to connect and manage MySQL database

Install the MySQL Workbench tool from the MySQL downloads page
Launch the app and add click on the MySQLConnections + button to add a new connection. Use the connection string data you copied from the Azure MySQL database you created earlier in this tutorial.
After establishing the connection, open a new Query tab; paste the commands from MySQLIdentity.sql into the query and execute it in order to create the database tables.
You now have all the ASP.NET Identity necessary tables created on a MySQL database hosted on Azure as shown below.

Create an MVC application project from template and configure it to use MySQL provider

If needed, install either Visual Studio Express 2013 for Web or Visual Studio 2013 with Update 2.

Download the ASP.NET.Identity.MySQL project from GitHub

Browse to the repository URL at AspNet.Identity.MySQL (GitHub).
Download the source code.
Extract the .zip file into a local folder.
Open the AspNet.Identity.MySQL solution and build it.

Create a new MVC application project from template

Right click the AspNet.Identity.MySQL solution and Add, New Project
In the Add New Project Dialog select Visual C# on the left, then Web and then select ASP.NET Web Application. Name your project IdentityMySQLDemo; and then click OK.
In the New ASP.NET Project dialog, select the MVC template with the default options (that includes Individual User Accounts as authentication method) and click OK.
In Solution Explorer, right-click your IdentityMySQLDemo project and select Manage NuGet Packages. In the search text box dialog, type Identity.EntityFramework. Select this package in the list of results and click Uninstall. You will be prompted to uninstall the dependency package EntityFramework. Click on Yes as we will no longer this package on this application.
Right click the IdentityMySQLDemo project, select Add, Reference, Solution, Projects; select the AspNet.Identity.MySQL project and click OK.
In the IdentityMySQLDemo project, replace all references to
using Microsoft.AspNet.Identity.EntityFramework;
with
using AspNet.Identity.MySQL;

In IdentityModels.cs, set ApplicationDbContext to derive from MySqlDatabase and include a constructor that take a single parameter with the connection name.

public class ApplicationDbContext : MySQLDatabase
{
    public ApplicationDbContext(string connectionName)
        : base(connectionName)
    {
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext("DefaultConnection");
    }
}

Open the IdentityConfig.cs file. In the ApplicationUserManager.Create method, replace instantiating UserManager with the following code:

var manager = new ApplicationUserManager(
    new UserStore<ApplicationUser>(
    context.Get<ApplicationDbContext>() as MySQLDatabase));

Open the web.config file and replace the DefaultConnection string with this entry replacing the highlighted values with the connection string of the MySQL database you created on previous steps:
```
<add name="DefaultConnection" connectionString="Database=IdentityMySQLDatabase;
Data Source=<DataSource>;User Id=<UserID>;Password=<Password>"
providerName="MySql.Data.MySqlClient" />
```

Run the app and connect to the MySQL DB

Right click the IdentityMySQLDemo project and select Set as Startup Project
Press Ctrl + F5 to build and run the app.
Click on Register tab on the top of the page.
Enter a new user name and password and then click on Register.
The new user is now registered and logged in.
Go back to the MySQL Workbench tool and inspect the IdentityMySQLDatabase table's contents. Inspect the users table for the entries as you register new users.

Next Steps

For more information on how to enable other authentication methods on this app, refer to Create an ASP.NET MVC 5 App with Facebook and Google OAuth2 and OpenID Sign-on.

To learn how to integrate your DB with OAuth and to set up roles to limit users access to your app, see Deploy a Secure ASP.NET MVC 5 app with Membership, OAuth, and SQL Database to Azure.

Share via