Search code examples
asp.net-mvcentity-frameworkdatabase-connectionasp.net-identity

MVC 6 - How to configure remote database connection for Identity 2 and Entity Framework 6

I've been spinning my wheels on this for awhile now and haven't been able to find anything substantial on the topic.

I've implemented an MVC 6 project that is utilizing the Identity Framework 2 and Entity Framework 6. The application works fine with a local database being used as my identity repository. On the first connection, the Entity Framework creates the identity tables based on my identity model classes. I built it based off of a project from this book. The chapters on using Identity and all of the online samples I find don't cover pointing the entity framework to a remote database.

Here are my connection strings I've experimented with...

Local database connection string. Project works fine.

<add name="IdentityDb" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\IdentityDb.mdf;Integrated Security=True" providerName="System.Data.SqlClient" />

Failed approach 1. I based this off of other connection strings from my other projects. I removed the meta data parameters because when it comes to my understanding, this points to the edmx file but I don't have one since I'm taking a code first approach and letting identity and the entity frameworks build the database. I'm wondering if I'm missing something here because I've always taken a database first approach until now.

<add name="IdentityDb" connectionString="provider=System.Data.SqlClient;provider connection string=&quot;data source=****;initial catalog=IdentityTest;Uid=*****;Pwd=*****;integrated security=False;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Returned exception details. The metadata keyword is missing.

System.ArgumentException: Some required information is missing from the connection string. The 'metadata' keyword is always required.

Failed approach 2. I built an empty Identity.edmx file thinking this must be where all of the metadata is stored and maybe the entity framework will update it and have it's required resources once the app connects to the database the first time.

<add name="IdentityDb" connectionString="metadata=res://*/Identity.csdl|res://*/Identity.ssdl|res://*/Identity.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=****;initial catalog=IdentityTest;Uid=****;Pwd=****;integrated security=False;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Returned exception detail. The meta data is missing.

System.Data.Entity.Core.MetadataException: Unable to load the specified metadata resource.

Last failed approach. I found a lot of online sources where people simply changed the metadata parameters to "res://*". I'm guessing this is some kind of wildcard that would locate the metadata resources if they existed...

<add name="IdentityDb" connectionString="metadata=res://*;provider=System.Data.SqlClient;provider connection string=&quot;data source=****;initial catalog=IdentityTest;Uid=****;Pwd=****;integrated security=False;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Returned exception details (With a identity.edmx file in the project)

System.NotSupportedException: Model compatibility cannot be checked because the DbContext instance was not created using Code First patterns. DbContext instances created from an ObjectContext or using an EDMX file cannot be checked for compatibility.

Returned exception details (Without an identity.edmx file in the project)

System.ArgumentException: Argument 'xmlReader' is not valid. A minimum of one .ssdl artifact must be supplied

My database context class

public class AppIdentityDbContext : IdentityDbContext<AppUser>
{
    public AppIdentityDbContext() : base("name=IdentityDb") { }

    static AppIdentityDbContext()
    {
        // Seeds the database when the schema is first created through the Entity Framework.
        Database.SetInitializer<AppIdentityDbContext>(new IdentityDbInit());
    }

    // OWIN uses this method to create instances of this class when needed.
    public static AppIdentityDbContext Create()
    {
        return new AppIdentityDbContext();
    }

    public System.Data.Entity.DbSet<UAM.Models.Identity.AppRole> IdentityRoles { get; set; }
}

// Seed class for initially populating the identity database.
public class IdentityDbInit : DropCreateDatabaseIfModelChanges<AppIdentityDbContext>
{
    protected override void Seed(AppIdentityDbContext context)
    {
        PerformInitialSetup(context);
        base.Seed(context);
    }

    public void PerformInitialSetup(AppIdentityDbContext context)
    {
        // Initial database configuration
    }
}

Any help or insight would be much appreciated. What is the correct approach for doing this? Is there something that I need to do when I transition from using a local database to a remote one? Let me know if I should provide anymore code samples. I'm fairly new to MVC and am currently building this module so that I can use it for a few enterprise applications that I'll be developing this year.

Solution

  • You need to specify providerName="System.Data.SqlClient" instead of providerName="System.Data.EntityClient"

    For Example

    <add name="IdentityDb" connectionString="Data Source=*******;Database=IdentityTest;Integrated Security=false;User ID=**;Password=******;" providerName="System.Data.SqlClient"/>