Language

Implementing a Custom MySQL ASP.NET Identity Storage Provider

By Raquel Soares De Almeida and Rick Anderson|

This tutorial shows you how to replace the default data storage implementation for ASP.NET Identity (EntityFramework - SQL Client provider) with a custom implementation. This tutorial will:

  • Describe the details on how to implement a custom storage provider for ASP.NET Identity.
  • Explain the core interfaces you need to implement and how to plug them into ASP.NET Identity.
  • Show how to create a MySQL database instance on Windows 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.
At the end of this tutorial, you will have an MVC application project with ASP.NET Identity working with a MySQL database hosted on Windows Azure.  You can download the completed project at https://github.com/raquelsa/AspNet.Identity.MySQL.

This tutorial was written by Raquel Soares De Almeida and  Rick Anderson ( @RickAndMSFT ).

Implementing a custom storage provider for ASP.NET Identity

ASP.NET Identity is an extensible system where you can plug in your own persistence store. ASP.NET Identity has the following high level constructs: Managers and Stores.

Managers are high level classes which the application developer uses to do any operation in the ASP.NET Identity system. The ASP.NET Identity system has the following Managers: UserManager and RolesManager. UserManager is used to perform any operation on the User such as creating a user or deleting a user. RoleManager is used to perform operations on Roles.

Stores are lower level classes which have the implementation details on how the particular entities such as User and Roles are stored. Stores are closely coupled with the persistence mechanism. Eg. Microsoft.ASP.NET.Identity.EntityFramework has UserStore, which is used to store the User type and its related types using EntityFramework.

Managers are decoupled from Stores. The reason they are decoupled is that you can replace the persistence mechanism of your application by changing the stores without having to rewrite your application. Manager can talk to any stores. Since you are using high level UserManager APIs to perform CRUD operations on the User, if you replace the UserStore to any other store such as AzureTable Storage, MySql etc you will not have to rewrite your application.

In the following example we will see how you can replace the persistence mechanism from Entity Framework to MySql without having to rewrite your application.

Implementing the storage classes

In order to implement your own storage providers, you need to implement the storage classes which are equivalent to the following classes in the Microsoft.AspNet.Identity.EntityFramework implementation: UserStore<TUser>; IdentityUser; RoleStore<TRole> and IdentityRole.

See the images below (click on the image to see a larger view) for a class diagram on the EntityFramework default implementation (Microsoft AspNet Identity EntityFramework).

Users

Roles

At the default ASP.NET Identity EntityFramerwork store, you will find additional classes for the entities: IdentityUserClaim; IdentityUserLogin; IdentityUserRole that represents the User’s related classes for Claims; Logins and Roles respectively.

On this MySQL store sample, I choose not to implement these classes as it would be more performant to not load these database entries into memory to perform basic operations (e.g.: add or remove a user’s claim). Instead, the backend store classes execute these operations directly on the database. One example of this is the UserStore.GetClaimsAsync() method below. It calls the userClaimTable.FindByUserId(user.Id) method which executes a query on that table directly and returns a list of Claims.

public Task<IList<Claim>> GetClaimsAsync(IdentityUser user)

{

ClaimsIdentity identity = userClaimsTable.FindByUserId(user.Id);

return Task.FromResult<IList<Claim>>(identity.Claims.ToList());

}

Here are the steps I took in order to implement this MySQL store for ASP.NET Identity

1. Write a User class that implements the IUser interface as illustrated in the above picture. See code snippet below.

public class IdentityUser : IUser

{

        public IdentityUser(){...}

        public IdentityUser(string userName) (){...}

        public string Id { get; set; }

        public string UserName { get; set; }

        public string PasswordHash { get; set; }

        public string SecurityStamp { get; set; }

}

2.  Write a User store class that implements the IUserStore, IUserClaimStore, IUserLoginStore, IUserRoleStore and IUserPasswordStore interfaces as illustrated in the above picture.
Note that you only have to implement the IUserStore and the other interfaces are optional in case you want the features these other interfaces provides.  See code snippet below:

 public class UserStore : IUserStore<IdentityUser>,

                         IUserClaimStore<IdentityUser>,

                         IUserLoginStore<IdentityUser>,

                         IUserRoleStore<IdentityUser>,

                         IUserPasswordStore<IdentityUser>

             {

        public UserStore(){...}

        public Task CreateAsync(IdentityUser user){...}

 public Task<IdentityUser> FindByIdAsync(string userId){...}  
.. .
}


3. Write a Role class that implements the IRole interface as illustrated in the picture above. See code snippet below:

public class IdentityRole : IRole

    {

        public IdentityRole(){...}

        public IdentityRole(string roleName) (){...}

        public string Id { get; set; }

        public string Name { get; set; }

       

}

4. Write a Role store class that implements the IRoleStore interface as illustrated in the picture above.
Note that implementing this store is optional depending on whether or not you want to use Roles on your system. See code snippet below:

 public class RoleStore : IRoleStore<IdentityRole>                        

             {

        public RoleStore(){...}

        public Task CreateAsync(IdentityRole role){...}

 public Task<IdentityRole> FindByIdAsync(string roleId){...}  
.. .
}

Additionally, you will find the data layer access classes specific to this sample implementation:

  • MySQLDatabase:  Class that encapsulates MySQL database connection and database queries. The store classes (UserStore and RoleStore) are initialized with instances of this class.

  • RoleTable: Class that encapsulates the Role table and its CRUD operations.

  • UserClaimsTable: Class that encapsulates the UserClaims table and its CRUD operations.

  • UserLoginsTable: Class that encapsulates the UserLogins table and its CRUD operations.

  • UserRolesTable: Class that encapsulates the UserRoles table and its CRUD operations.

  • UserTable: Class that encapsulates the Users table and its CRUD operations.

 

Creating a MySQL database instance on Windows Azure

  1. Log in to the Windows Azure Management Portal.
  2. Click +NEW at the bottom of the page, and then select STORE.


  3.  In the Choose and Add-on wizard, scroll down and select ClearDB MySQL Database and click on the next arrow at the bottom right of the dialog.



  4. Keep the default Free plan and change the Name to IdentityMySQLDatabase.  Select the region nearest you and then click the next arrow.


  5. 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.



  6. You can get the database connection information by clicking on CONNECTION INFO at the bottom of the page (shown above).
  7.  Copy the connection string by clicking on the copy button and save it so you can use later in your MVC application.

Creating the ASP.NET Identity tables in a MySQL database

Installing MySQL Workbench tool to connect and manage MySQL database

1.      Install the MySQL Workbench tool from the MySQL downloads page

2.      In the installation wizard select the Setup Type: Custom;

3.      In the feature selection tab, select Applications and MySQLWorkbench only and complete the installation Wizard steps.

4.      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 in the previous step of this tutorial.
E.g: Connection Name: AzureDB; Host Name: us-cdbr-azure-west-b.cleardb.com; Usename:<username>; Password:<password>; Default Schema: IdentityMySQLDatabase.

5.   After establishing the connection, open a new Query tab; paste the commands below into the query and execute it in order to create the database tables.

CREATE TABLE `IdentityMySQLDatabase`.`users` (
  `Id` VARCHAR(45) NOT NULL,
  `UserName` VARCHAR(45) NULL,
  `PasswordHash` VARCHAR(100) NULL,
  `SecurityStamp` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `IdentityMySQLDatabase`.`roles` (
  `Id` VARCHAR(45) NOT NULL,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`Id`));

CREATE TABLE `IdentityMySQLDatabase`.`userclaims` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `UserId` VARCHAR(45) NULL,
  `ClaimType` VARCHAR(100) NULL,
  `ClaimValue` VARCHAR(100) NULL,
  PRIMARY KEY (`Id`),
  FOREIGN KEY (`UserId`)
    REFERENCES `IdentityMySQLDatabase`.`users` (`Id`) on delete cascade);

CREATE TABLE `IdentityMySQLDatabase`.`userlogins` (
  `UserId` VARCHAR(45) NOT NULL,
  `ProviderKey` VARCHAR(100) NULL,
  `LoginProvider` VARCHAR(100) NULL,
  FOREIGN KEY (`UserId`)
    REFERENCES `IdentityMySQLDatabase`.`users` (`Id`) on delete cascade);

CREATE TABLE `IdentityMySQLDatabase`.`userroles` (
  `UserId` VARCHAR(45) NOT NULL,
  `RoleId` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`UserId`, `RoleId`),
  FOREIGN KEY (`UserId`)
    REFERENCES `IdentityMySQLDatabase`.`users` (`Id`) 
on delete cascade
on update cascade,
  FOREIGN KEY (`RoleId`)
    REFERENCES `IdentityMySQLDatabase`.`roles` (`Id`)
on delete cascade
on update cascade); 

6. You now have all the ASP.NET Identity necessary tables created on a MySQL database hosted on Azure as shown in the picture below

Creating a MVC application project from template and configuring it to use MySQL provider

  1. Start by installing Visual Studio Express 2013 for Web or Visual Studio 2013 if you haven't already.

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

  1. Browse to the repository URL at: https://github.com/raquelsa/AspNet.Identity.MySQL
  2. Click on the Download Zip button (at the bottom right of the page) to download and save the project’s zip file.
  3. Extract the .zip file into a local folder.
  4. Open the AspNet.Identity.MySQL solution and build it.

Creating a new MVC application project from template

  1. Right click the AspNet.Identity.MySQL solution and Add, New Project
  2. 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.


  3. 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.

  4. 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.

  5. Right click the IdentityMySQLDemo project, select Add, Reference, Solution, Projects; select the AspNet.Identity.MySQL project and click OK
  6.  In the IdentityMySQLDemo project, delete the IdentityModels.cs file under Models folder.

  7. In the IdentityMySQLDemo project, replace all references to “using Microsoft.AspNet.Identity.EntityFramework;” with “using AspNet.Identity.MySQL;”

  8. In the IdentityMySQLDemo project, replace all references to “ApplicationUser” with “IdentityUser”;

  9. In the IdentityMySQLDemo project, open AccountController.cs file under the Controllers folder and replace the constructor code:

    1.  public AccountController()
               
    : this(new UserManager<IdentityUser>(new UserStore<IdentityUser>(new ApplicationDbContext())

                  {

            }

    With:


    public AccountController()
               
    : this(new UserManager<IdentityUser>(new UserStore(new MySQLDatabase())))

                  {

            }

    1.      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" />

Running the app and connecting to the MySQL DB

  1. Right click the IdentityMySQLDemo project and select Set as Startup Project
  2. Press Ctrl + F5 to build and run the app. 
  3. Click on Register tab on the top of the page.
  4. Enter a new user name and password and then click on Register.

  5. The new user is now registered and logged in.


  6.  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 http://www.asp.net/mvc/tutorials/mvc-5/create-an-aspnet-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 a Windows Azure Web Site.

Author Information

Raquel Soares De Almeida

Raquel Soares De Almeida – Raquel Almeida is a Software Developer Engineer in Test on the Azure Application Platform and Tools team where her focus is on ASP.NET and Web Stack Runtime.

Rick Anderson

Rick Anderson – Rick Anderson works as a programmer writer for Microsoft, focusing on ASP.NET MVC, Windows Azure and Entity Framework. You can follow him on twitter via @RickAndMSFT.