ASP.NET MVC5 - Keeping Users in Oracle Database

Question

Once creating a ASP.NET MVC5 project (with target framework is .NET 4.5.1 and the authentication type is Individual User Account), so what is the most elegant way to configure the project so that it keeps the user, claims, roles etc. in an Oracle 12c database? I mean, how can I keep the authorization/authentication data in Oracle without deforming the automatically-generated MVC5 project structure.

I guess changing the <defaultConnection> tag is not enough and there should be another Oracle implementation to replace Microsoft.AspNet.Identity.EntityFramework. It would be very helpful to gather an answer telling what to do step by step right from the start, where the template project is generated by VisualStudio (i.e. which references should be added to the project; in which way the Web.config file should be arranged? Should the Oracle tables be named strictly as AspNetUsers, AspNetClaims etc. or can I inject the already existing table names into the code side?)

Note: Devart's dotConnect for Oracle is out of my scope as it is not a free product. I use Oracle ManagedDataAccess for database access and to make use of Entity Framework, but using the ASP.NET Identity 2 + EntityFramework6 logic with Oracle database (without deeply changing the structure of the classical MVC5 project) has been frustrating to me.

Answer 1

Andres Bejarano worked great for me but I want to add something that would have savde me a lot of time. First make sure you can connect to your database using Oracle.ManagedDataAccess.Client (I had problems with my TNS names file see OracleConnection.Open is throwing ORA-12541 TNS no listener).

Second, I was getting the following error when I tried to register a user:

No Entity Framework provider found for the ADO.NET provider with invariant name 'Oracle.ManagedDataAccess.Client'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

This was because I didn't have all the correct Nuget packages installed and/or I had messed up the web.config.

Make sure you have ODAC installed http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

If you uninstall/reinstall the following Nuget packages in order. You should get around this error:

1. UnInstall-Package Oracle.ManagedDataAccess.EntityFramework
2. UnInstall-Package Oracle.ManagedDataAccess
3. UnInstall-Package EntityFramework -Version 6.1.1
4. Install-Package EntityFramework -Version 6.1.1
5. Install-Package Oracle.ManagedDataAccess
6. Install-Package Oracle.ManagedDataAccess.EntityFramework

Answer 2

This may arrive a bit late for you but I'll leave it in case anyone else runs into the same problem. So I finally managed to make Identity 2.0 and Oracle work together. The following steps work if you don't want to make any changes to the default IdentityUser (ex. if you're ok with having a char ID instead of int or long) and just want the tables on your existing Oracle schema.

1. Create Identity tables on Oracle. You can change the table names if you want to, just make sure to include the necessary columns for Identity to work with it. You can also add any extra columns you may need on your application (script originally found on Devart, I copied it to a gist in case URL breaks):

   Gist here

2. If you're using an EDMX file, you need to add a new connection string cause the one that gets generated automatically won't work, you need a standard connection string. Try following this template:

   <add name="IdentityContext" connectionString="Data Source=localhost:1521/xe;PASSWORD=password;USER ID=username;" providerName="Oracle.ManagedDataAccess.Client" />

3. Tell your ApplicationDbContext to use your new connectionString

   public ApplicationDbContext()
       : base("IdentityContext", throwIfV1Schema: false)
   {
   }
   

4. Tell Identity to use your existing schema and tables. Add this method inside the ApplicationDbContext definition found in IdentityModels.cs:

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
       base.OnModelCreating(modelBuilder); // MUST go first.
   
       modelBuilder.HasDefaultSchema("YOUR_SCHEMA"); // Use uppercase!
   
       modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers");
       modelBuilder.Entity<IdentityRole>().ToTable("AspNetRoles");
       modelBuilder.Entity<IdentityUserRole>().ToTable("AspNetUserRoles");
       modelBuilder.Entity<IdentityUserClaim>().ToTable("AspNetUserClaims");
       modelBuilder.Entity<IdentityUserLogin>().ToTable("AspNetUserLogins");
   }
   

5. Rebuild and thats it!

Let me know if it works for you!

Answer 3

You can implement it yourself. It's not impossible. Someone has done it here and here. Things might have changed a bit.

I've implemented my AspNet.Identity for nhibernate and I don't have any reference to entity framework. Cannot show you the code but I've basically followed what some others have done with RavenDb, Dapper or nHibernate.

You have to implement your:

• User (Microsoft.AspNet.Identity.IUser),
• Role (Microsoft.AspNet.Identity.IRole)
• UserStore (Microsoft.AspNet.Identity.IUserStore)
• UserManager (Microsoft.AspNet.Identity.UserManager)
• RolesStore (Microsoft.AspNet.Identity.IRoleStore)
• RoleManager (Microsoft.AspNet.Identity.RoleManager)

Answer 4

Yes is an old question but i think is useful to say some things about this.The answer of Andres Bejarano worked for me BUT there are some things about dll Versions. For Entity Framework 6 On VS 2015 i added to the MVC project a reference to the following dll:

• Oracle.DataAccess.EntityFramework 6.121.2.0
• Oracle.ManagedDataAccess 4.121.2.0
• Oracle.managedDataAccess.EntityFramework 6.121.2.0.

After this you need to write the correct configurations in web.config file as follow.
At the end of the Configuration tag add:

<system.data>
<DbProviderFactories>
  <remove invariant="Oracle.ManagedDataAccess.Client" />
  <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories> </system.data>

In provider section add:

<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

And of course in connectionstrings section you need to put the connection string for oracle: a thing like:

<add name="IdentityContext" connectionString="Data Source=YOURDB;User Id=YOURUSER;Password=YOURPASSWORD;"
  providerName="Oracle.ManagedDataAccess.Client" />

With this configurations the changes said by Andres Bejorano have worked for me without messing with the Nuget.