January 23, 2015

ASP.NET Identity 2.1 implementation for MySQL

In this blog post I will try to cover how to use a custom ASP.NET identity provider for MySQL I have created.

Default ASP.NET Identity provider uses Entity Framework and SQL Server to store information’s about users.

If you are trying to implement ASP.NET Identity 2.1 for MySQL database, then follow this guide.

This implementation uses Oracle fully-managed ADO.NET driver for MySQL.

This means that you have a connection string in your web.config similar to this:

<add name="DefaultConnection" connectionString="Server=localhost;
Database=aspnetidentity;Uid=radenko;Pwd=somepass;" providerName="MySql.Data.MySqlClient" />

 

This implementation of ASP.NET Identity 2.1 for MySQL has all the major interfaces implemented in custom UserStore class:

ASPIdentityUserStoreInterfaces

Source code of my implementation is available at GitHub – MySqlIdentity

First, you will need to execute this a create script on your MySQL database which will create the tables required for the ASP.NET Identity provider.

MySqlAspIdentityDatabase

  • Create a new ASP.NET MVC 5 project, choosing the Individual User Accounts authentication type.
  • Uninstall all EntityFramework NuGet packages starting with Microsoft.AspNet.Identity.EntityFramework
  • Install NuGet Package called MySql.AspNet.Identity
  • In ~/Models/IdentityModels.cs:
    • Remove the namespaces:
      • Microsoft.AspNet.Identity.EntityFramework
      • System.Data.Entity
    • Add the namespace: MySql.AspNet.Identity.
      Class ApplicationUser will inherit from IdentityUser class in MySql.Asp.Net.Identity namespace
    • Remove the entire ApplicationDbContext class. This class is not needed anymore.
  • In ~/App_Start/Startup.Auth.cs
    • Delete this line of code
app.CreatePerOwinContext(ApplicationDbContext.Create);
  • In ~/App_Start/IdentityConfig.cs
    Remove the namespaces:

    • Microsoft.AspNet.Identity.EntityFramework
    • System.Data.Entity
  • In method Create inside ApplicationUserManager class replace ApplicationUserManager with another which accepts MySqlUserStore :
 public static ApplicationUserManager Create(IdentityFactoryOptions<ApplicationUserManager> options, IOwinContext context) 
 {
     //var manager = new ApplicationUserManager(new UserStore<ApplicationUser>(context.Get<ApplicationDbContext>()));
        var manager = new ApplicationUserManager(new MySqlUserStore<ApplicationUser>());

MySqlUserStore accepts an optional parameter in the constructor – connection string so if you are not using DefaultConnection as your connection string you can pass another connection string.

After this you should be able to build your ASP.NET MVC project and run it successfully using MySQL as a store for your user, roles, claims and other information’s.

If you like this article don’t forget to subscribe to this blog and make sure you don’t miss new upcoming blog posts.

 

Related posts:

  1. Simple way to share Dependency Resolvers between MVC and Web API
  2. ASP.NET Web API GZip compression ActionFilter with 8 lines of code
  3. 8 ways to improve ASP.NET Web API performance
  4. Fixing issue with HTTPClient and ProtocolViolationException: The value of the date string in the header is invalid

23 Comments on ASP.NET Identity 2.1 implementation for MySQL

    • Radenko Zec
      March 21, 2015 at 12:00 pm (9 years ago)

      Thanks for comment Paulo.

      Reply
  2. Mohamed Ali
    April 28, 2015 at 1:48 pm (9 years ago)

    Very nice! How can I get a list of all roles? or all users?

    Thank you.

    Reply
  3. Maria Pia Larsen
    July 8, 2015 at 5:28 am (9 years ago)

    Thank you very much for this article. Do you have any advice how to implement this project with Identityserver3?

    Reply
  4. DafOwen
    August 19, 2015 at 3:05 pm (9 years ago)

    Does this support EF Migrations on IdentityModels.cs ?
    I don’t seem to be able to get it to work – maybe because ApplicationDbContext class has been removed ?
    Am looking to add additional columns to user model/table.

    Reply
    • Radenko Zec
      August 20, 2015 at 8:51 am (9 years ago)

      I am not sure about this. However I never add additional columns to user table. That is a bad practice by my opinion. You should create another table with additional columns which has UserId as primary key. This way you add additional columns without altering Identity Tables.

      Reply
  5. met
    August 24, 2015 at 7:25 am (9 years ago)

    Hello, could you tell me how to achieve the oracle?

    Reply
  6. Gigin Krishnan
    December 6, 2015 at 4:11 am (8 years ago)

    Hello, thanks for this post.

    When I host the project to godaddy, it shows an error

    “Unable to connect to any of the specified MySQL hosts”.

    I already added the server IP to Remote Database Access Hosts

    Can you please help me to resolve this

    Thanks
    Gigin Krishnan

    Reply
  7. Masen Sueifan
    June 12, 2016 at 1:11 pm (8 years ago)

    Hi, get an exception in AccountController.cs line 76
    var result = await SignInManager.PasswordSignInAsync(model.Email, model.Password, model.RememberMe, shouldLockout: false);
    can you please help me? I get this exception after click on login button

    Reply
  8. Damith Amarasena
    August 18, 2016 at 6:46 am (8 years ago)

    Hi,
    Say if I have to use DbContext as usual and insert some data into DB. How can I do it without ApplicationDbContext class?

    Reply
  9. jnoelsalcedo
    January 11, 2017 at 9:57 pm (7 years ago)

    Hi can you explain to me how to use IdentityDbContext to change aspnetusers table name.

    Tnxs.

    Reply
  10. Angela Temple Rogers
    January 31, 2017 at 1:11 am (7 years ago)

    I ran the sql to create the tables in my MySql database and followed your instructions here. However, when I try and register a user, I get “Table ‘xxxxxx.AspNetUsers’ doesn’t exist when it calls this line: var result = await UserManager.CreateAsync(user, model.Password); Any suggestions?

    Reply
    • Angela Temple Rogers
      January 31, 2017 at 8:48 pm (7 years ago)

      I had to re-create the tables with case sensitive names, so instead of aspnetusers I had to have it be AspNetUsers. Hope that helps someone else.

      Reply
  11. Daniele Paoloni
    March 13, 2018 at 2:31 pm (6 years ago)

    Great work perfect with Mysql 6.9.11 Connector

    Reply
  12. Tony
    April 19, 2018 at 1:21 pm (6 years ago)

    It look me a bit to understand what you were trying to accomplish, but this worked perfectly. Thanks.

    Reply
    • Dan Roller
      March 7, 2019 at 7:16 pm (5 years ago)

      Now 4 years later and using MySql 8, must I fall back to version 6?

      Reply
  14. Dan Roller
    March 7, 2019 at 7:35 pm (5 years ago)

    Sorry to reply in wrong spot
    Now 4 years later and using MySql 8, must I fall back to version 6?

    Reply
  15. Nikhil Virani
    April 15, 2019 at 4:57 pm (5 years ago)

    What should i do for changes IdentityDbContext table’s names?

    Reply
  16. windson
    December 7, 2019 at 7:04 pm (4 years ago)

    Nice tutorial. For .NET Core 3 and Identity Server 4 this video https://youtu.be/X4I0DUw6C84 has is the update approach to configure ASP.NET Core 3 Identity for MySQL using Visual Studio 2019

    Reply
  17. Johnny
    November 28, 2020 at 10:36 pm (3 years ago)

    Hello, first of all thank sou much for that! 🙂

    I’m trying to implement your solution, working with user it’s allright but i cannot work with roles (roles are created manually on DB), example:

    Dim manager = Context.GetOwinContext().GetUserManager(Of ApplicationUserManager)()
    Dim roleList As New List(Of String)
    roleList.Add(“UserRole1”)
    Dim user = New ApplicationUser() With {.UserName = userName, .Email = userName, .Roles = roleList}

    This save the user on DB but not the role in the “aspnetuserroles” table, also how can list all roles availables?

    Thanks so much, greetings!

    Reply

Leave a Reply