Setting Up ASP.NET Identity Framework 2.0 with Database First (VS2013 Update 2 SPA Template)

Update (2/12/15)

Important: This article assumes you plan on using the project template’s default Identity Framework 2.0 implementation which as an example uses a string as the UserId’s primary key. If an integer is desired instead then follow this Microsoft article before proceeding to Step 2. Also if you plan on copying the provided database table scripts from this article keep in mind they are for the default string as a primary key implementation. It is recommended to create the scripts yourself to get the correct integer primary key code. Finally, keep in mind the instructions from the Microsoft article may vary depending on what update you have installed for Visual Studio 2013 (Update 2 and Update 3 have variations in implementation and instructions).

Introduction

With the recent release of ASP.NET Identity 2.0 some great new features have come out of the wood work such as Two-factor Authentication, Account Lockout, Account Confirmation, and Password Reset among others. A nice blog post from the .NET Web Development and Tools Group provides more details. However, as nice as these new features are, integrating Identity Framework 2.0 with an existing database isn’t easy. I spent several hours working on this and have finally found a working solution which I’m going to share.

I ended up using the ASP.NET MVC 5 SPA Template found in Visual Studio 2013 Update 2 which was just released (at the time of this writing). If you are interested in getting into the responsive single page application world which is much the hype these days, it is a fantastic starting point. If this isn’t your intention, most of the points found throughout this post should apply to many projects.

Step 1 – Create the ASP.NET MVC 5 Application Using the SPA Template

To begin, assuming you have already installed Update 2 for Visual Studio 2013, create a new ASP.NET MVC Web Application.

Figure 1.0 - Create Solution/Project
Figure 1.0 – Create Solution/Project
Next, select Single Page Application from the available templates and leave everything set to default.

Figure 1.1 - Select SPA Template
Figure 1.1 – Select SPA Template
When the project creation has finished you will end up with what you see in the screen capture below.

Figure 1.3 - Created Project
Figure 1.2 – Created Project

Step 2 – Build and Run the Application

At this point you will need to build the application (CTRL + F5) and it should come up in your browser of choice. In my case, Chrome as shown below.

Figure 1.4 - Application Shown in Chrome
Figure 1.3 – Application Shown in Chrome
Click on the link that says Register as a new user and then fill out the information to create an account.

Figure 1.5 - Register a New User
Figure 1.4 – Register a New User

Step 3 – Inspect the Web.config File Connection String and Connect to Local Database

Automatically, the application will create a database (using Local DB: (LocalDb)\v11.0) with all the necessary Identity Framework 2.0 tables upon registering a new user. Open the Web.Config file in the base of the project and make note of the connection string. In my case, the below connection string was created.

We are interested in the name of the database which turned out to be aspnet-SampleApp.Web-20140515030919 which is also the name of the MDF file which stores the database. This file is found in the App_Data directory of the project.

Now it’s time to fire up SQL Server Management Studio and connect to the local database. Ensure your connection looks like the one below.

Note: As an alternative, you can use the SQL tools (SSDT) which are included with Visual Studio 2013 to create the scripts for the tables.

Figure 1.6 - SQL Connection Settings
Figure 1.5 – SQL Connection Settings
Once authenticated, look for the database which matched the name of the connection string.

Figure 1.7 - LocalDb Database
Figure 1.6 – LocalDb Database
Upon expanding the tables folder of the database, you will find the following auto-generated tables under the dbo schema.

  • dbo._MigrationHistory
  • dbo.AspNetRoles
  • dbo.AspNetUserClaims
  • dbo.AspNetUserLogins
  • dbo.AspNetUserRoles
  • dbo.AspNetUsers
Figure 1.8 - Identity 2.0 Tables
Figure 1.7 – Identity 2.0 Tables

Step 4 – Generate SQL Scripts For Each Database Table

We need to get some information on the Identity Framework 2.0 tables and their structure in order to recreate these tables in our own database. This is done by creating SQL scripts for each table which can later be ran in a query on our own database.

Right click on each table and select the following: Script Table as -> CREATE To -> File. Give the script a name (I chose the name of the table) and save it. Each table’s script has been included here for your convenience.

dbo._MigrationHistory:

dbo.AspNetRoles:

dbo.AspNetUserClaims:

dbo.AspNetUserLogins:

dbo.AspNetUserRoles:

dbo.AspNetUsers:

Step 5 – Connect to Desired Database and Run SQL Scripts

Since this post is about Database First, connect to the database you wish to use. You will need to run the SQL scripts to create the Identity Framework 2.0 tables. The recommended order to run these scripts is shown below. If you don’t follow this order you may run into errors.

  1. dbo._MigrationHistory
  2. dbo.AspNetUsers
  3. dbo.AspNetRoles
  4. dbo.AspNetUserClaims
  5. dbo.AspNetUserLogins
  6. dbo.AspNetUserRoles

At this point if you need to add any relationships (E.g. foreign keys) from your own tables to these tables you are welcome to do so but do not modify any of the Entity Framework 2.0 tables directly or later on any of their POCO classes. Doing so will result in errors based upon feedback I’ve received.

Step 6 – Create Second Project For EDMX/Data Model

It is recommended and often common practice to separate the different portions of an application into different projects. In this case, we already created one named SampleApp.Web which is the main MVC project. The next project we will create will be SampleApp.Data which we will use to house the EDMX file to generate our POCO classes and database context logic.

Note: In a production environment it would be a good idea to create a third project named SampleApp.Domain to house only the POCO classes. This can be a bit tricky with the EDMX since POCO classes are auto generated into that same project. Julie Lerman has a great Pluralsight course called Getting Started With Entity Framework 5 which discusses two methods for doing this, one which is rather messy and then the preferred more elegant method. You really don’t want any references to Entity Framework in your SampleApp.Domain project and only the POCO classes themselves which other projects refer to when they need them. This is best practice.

Right click on the solution and select Add -> New Project and select Class Library. As mentioned above, I have given the project the name SampleApp.Data.

Figure 1.9 - Create Class Library Project
Figure 1.8 – Create Class Library Project
Before doing anything else, right click on the web project (in my case SampleApp.Web) and select Set as StartUp Project. This will make sure when the project is built it actually runs. Also, add a reference to the new class library project (SampleApp.Data) to the web project.

In addition, delete the Class1.cs file that was automatically created in the root of the project as it’s not needed.

Step 7 – Create ADO.NET Entity Data Model

Add a new folder to the data project named DAL, short for data access layer. Keep in mind you don’t have to follow my convention or even put it into a separate folder. Right click on that folder and select Add -> New Item. Select ADO.NET Entity Data Model and give it a name. In this case I have given it the name SampleAppModel.

Figure 2.0 - Create ADO.NET Entity Data Model
Figure 1.9 – Create ADO.NET Entity Data Model
The Entity Data Model Wizard will pop up asking what the model should contain. Select EF Designer from database.

Figure 2.1 - Entity Data Model Wizard
Figure 2.0 – Entity Data Model Wizard
Upon clicking next you will have to specify the connection information to your desired existing database. Do so and then provide a name for the connection as shown below. I chose the name SampleAppDbContext.

Figure 2.2 - Connection Name to Store in App.Config
Figure 2.1 – Connection Name to Store in App.Config

Note: You may be asked what version of Entity Framework you wish to use. I highly suggest using Entity Framework 6.0.

Next, the Entity Data Model Wizard will ask you which database objects you want to include in your model. Select all of the tables in your database (or choose only the tables you need) and give the Model Namespace a name which I chose SampleAppModel.

In an alternate case, if you created the EDMX file already then you would just update your model from the database to take into account the new Identity Framework 2.0 tables which were added in Step 5.

Figure 2.3 - Add Database Objects to Model
Figure 2.2 – Add Database Objects to Model

Step 8 – Modify the Connection String in Web.Config in the Web Project

Once your EDMX file has been created a new connection string will show up in App.Config (remember we created this in the data project). This connection string is shown below.

This is how the data layer created in the data project will connect to the database. Since our startup project is set to SampleApp.Web, only the items in Web.config will be taken into consideration. Thus, we need to move this connection string over to Web.config. Once this is done, delete the App.Config file.

So now you have two connection strings in your Web.config file, one named SampleAppDbContext used by your data layer and the other named DefaultConnection, used by Identity Framework 2.0. Keep in mind that Identity Framework 2.0 uses a different kind of connection string than SampleAppDbContext. Make note of your database file name found in the SampleAppDbContext connection string and copy it.

Now, modify the connection string named DefaultConnection and give it a new name (I used IdentityDbContext) and have it match the database file name found in the SampleAppDbContext connection string. This will be a separate connection string used only by Identity Framework 2.0. Also, change the initial catalog to the name of your database, in my case SampleAppDb. The modified connection string is below along with the other connection string the data layer uses.

Looking back, we changed the connection string name from DefaultConnection to IdentityDbContext, the name of the database file to SampleAppDb.mdf, and the initial catalog to SampleAppDb. Pay attention to the |DataDirectory| shortcut as this could have an impact on the file location.

So to recap, we now have two connection strings. One is used for accessing our data context as we normally would and the other is used for Identity Framework 2.0. Both of these connection strings are different in type, SampleAppDbContext uses the System.Data.EntityClient provider and IdentityDbContext uses the System.Data.SqlClient provider. This is why we need two connection strings.

Step 9 – Modify IdentityModels.cs to Use the Correct Connection String

Under the web project’s Models folder, open IdentityModels.cs. The unmodified contents of this file are below.

Now, change the ApplicationDbContext class constructor to use the new connection string for Identity Framework 2.0, in my case IdentityDbContext (under the call to the base class constructor). The updated file is shown below.

Finally, rebuild the application and run it. Register a new user and you will see that everything worked just fine. Upon closer inspection of the AspNetUsers table which is now in your own database, you will find the new user added. At this point you can delete the initial database file created automatically earlier in this post (aspnet-SampleApp.Web-20140515030919.mdf) as it’s no longer needed.

A Few Caveats

This solution certainly isn’t perfect but it worked for me. I spent countless hours toying with this. I found that the schema has to be dbo for the Identity Framework 2.0 tables. You can use a different schema for your own database tables but upon changing the schema for the Identity Framework 2.0 tables the application would crash and burn. There are other solutions out there on the web where this isn’t the case but I found them to be too cryptic. I am a big fan of simplicity so for the time being if my Identity Framework 2.0 tables are using the dbo schema then I really can’t sweat it.

Also, if you created a separate class library project for your data layer as I did in this post, don’t make the mistake of using or adding any connection strings to App.Config since that data will be ignored. Only Web.config in the Web project will be used since it’s set as the startup project. It’s my understanding that there are ways for explicitly using App.Config but here I don’t recommend it.

Conclusion

I hope this helped you integrate Identity Framework 2.0 with your existing database. I have nothing against Code First but most projects in the wild revolve around an existing database so knowing how to set things up properly is paramount. I would love to hear from you in the comments about your experience with this post and any other experiences with Identity Framework 2.0 and Database First. I am always open to new ideas or tricks and believe that sharing them is a win-win for everyone in the .NET community.

Sources

A few web searches led me to solve this problem. I have listed my sources below. None of them completely solved the problem for me and through trial and error I figured this out. But credit is given where credit is due and if I didn’t get some of the information from these sources I might have spent countless more hours working on a solution.

  1. http://stackoverflow.com/questions/20668328/using-asp-net-identity-database-first-approch
  2. http://patrickdesjardins.com/blog/the-entity-type-applicationuser-is-not-part-of-the-model-for-the-current-context

Further Reading

For a great article to help nail home some good points of Identity Framework 2.0, visit this website.

Daniel Eagle

Currently residing in the Austin area, Daniel Eagle is an avid gamer, writer, technology/science enthusiast, software developer, and educator. He takes great pride in spreading knowledge and helping others.

More Posts - Website

Follow Me:
TwitterLinkedInYouTube


  • Thank you very much for this. I’ve been looking for this exact thing.

  • It’s my pleasure, Derek. It was driving me nuts for hours and when I solved it I knew I had to share with everyone. I will post more articles in the future surrounding various topics but my goal is to post things that aren’t common yet very useful.

  • Mobstaa

    Thanks for this! Helped me a lot!

  • Jan Pahulje

    Does this also works on MVC5 model?

    • By model do you mean Model First? Both Database First and Model First use the designer surface which generates an EDMX file with all your POCO classes, mappings, and context. In this case Model First would work just the same as Database First as it pertains to this blog post.

      Conversely, Code First will work a bit differently which is outside the scope of this post. It is possible to get the same results with Code First but it will require a bit more work.

  • Jeroen Wolfslag

    Hi Daniel, this all looks very familiar, nice post.. 🙂

    Can you also elaborate on my issue: http://stackoverflow.com/questions/24775133/asp-net-identity-model-first-fails-because-of-renamed-aspnetuserroles-columns

    I think it is the follow-up after your conclusion, and I was wondering how you resolved this issue (if you encountered it).

    • Hi Jeroen. I looked over your Stack Overflow post. Many of the errors you’ve described within were ones that I ran into before finally figuring out how to incorporate Database First with Identity Framework 2.0. I haven’t implemented Model First yet but I have a few thoughts which I hope can help you.

      I highly recommend a solution which modifies the least amount of code as possible. You mentioned that in one step you modified xxx.Context.tt to inherit from IdentityDbContext. I did this once as well and that opened up Pandora’s Box. Try to keep your implementation spot on with what I’ve written in this tutorial.

      It’s my understanding you can create an MVC project and have it include an implementation of Identity Framework 2.0. I believe the files used in that implementation are the same as the ones used in the SPA template so the steps in this post should apply.

      A Few Thoughts on Model First and Database First

      Now, Model First basically just allows you to use the designer to generate the SQL to create your database from. Once the database is created you can continue to work with the designer but you have two options and here’s where Model First and Database First concepts almost touch each other.

      First, you could continue to use the design surface to add another entity as you did with Model First. If you do this and you want to capture your changes, you’d have to generate another SQL script which essentially drops everything in the database and recreates them with the changes you made. You’ll lose any data in the database.

      Second, you could make changes to the database directly and not use the designer. In this case some Database First concepts will come into light. You’ll have to update your model from the database using the right menu option in the designer. Here it will see the changes you made to the database, pull them into your model, and update all the code associated with it.

      How I’d Implement Model First

      1. Start from scratch again and setup a new MVC project with Identity Framework 2.0.

      2. Do not run the app yet to avoid having a database auto-generated.

      3. Use Model First to create your database script. Once done, save the SQL.

      4. Incorporate the SQL used to create the Identity Framework 2.0 tables (they are in this post) with the SQL that was created using Model First. Ensure any custom relationships you’ve defined are implemented.

      5. Create your database and then execute the final SQL script on it to add your tables, relationships, etc.

      6. Edit your Web.config to include the two connection strings shown in this tutorial. One will work with Identity Framework 2.0 directly, the other will be used for the rest of your application. Remember, these are two different kind of connection strings.

      7. Modify IdentityModels.cs to use the Identity Framework 2.0 specific connection string.

      8. Build and run your app. Register a new user. Check the database to ensure the data is valid.

      Conclusion

      This is very close to what you have to do for Database First. Hopefully this will help you. Just remember the differences between how Database First and Model First work and it should be a little easier. The least amount of code modified in this case, the better. Anything that edits auto-generated classes turns out to be a pain. Granted, there are ways around that but it still isn’t very elegant.

      Please if you have any questions please let me know. I’ll do the best I can to answer them for you and offer any help. I’m considering another tutorial to mirror your situation because it is a practical one.

      Thanks for sharing your problem and good luck.

  • Roberto Gallardo

    I successfully implement this, but there’s one thing i cannot do. In your 5 step last line you say: “At this point if you need to add any relationships to these tables you are welcome to do so but don’t modify any primary and foreign keys on them or you will surely run into problems.” how to make a relation, because in my project when i was trying to do a relation in the Identity classes they don’t recognize classes from the database first model e.g : declare in a navigation propriety from Application user to Workers table. Give some example to make that you don’t give a detail about that and its one of the main reasons why you would mixing db first with code first identity. At this moment in my project the relationship its only logical

    • In my experience I have been able to create relationships from some of my own tables to the Identity Framework 2.0 specific tables via foreign keys. I wouldn’t mess around too much with the Identity Framework 2.0 tables themselves because any change could mess up the Microsoft specific implementation.

      I did have a navigation property from one of my entities setup to work with one of the Identity Framework 2.0 entities and it didn’t have any issue at all. Again I believe that’s because it didn’t modify any of the POCO classes Identity Framework 2.0 uses.

      As of right now the least amount you mess with the Identity Framework 2.0 specific implementation, the better. I really hope this gets easier in the future.

  • Saurabh

    Thanks dear. It helped me a lot with really simple steps.

  • Here is yet another example of setting up ASP.NET Identity 2.0 with EF Db-First:

    https://github.com/kriasoft/AspNet-Server-Template

  • jjohansen

    This MVC5 stater template has it all setup with database first identity, sql scripts, and other nice additions. It saved me a lot of time.http://inovacode.com/MvcStarterKit.html

  • Brandon

    Excellent post! Very simple and easy to follow. Best of all, it finally let me integrate Identity 2.0 with my existing database project. On top of that, I understand why my previous attempts weren’t working.

  • Ryan

    Might be worth noting I had to include the edmx data access connection string in ‘simple’ format. e.g

    • Thanks for sharing Ryan. This is great information to be aware of under certain circumstances.

  • venkat

    Thank you for sharing this example. Do you have any blog or article, that explains on how to integrate this with a layered architecture. In my project, the Web Project cannot access the database directly. it has to interact with the Business Object layer.

    • It all depends on how your application talks to the database through your Business Object Layer. Are you using Entity Framework for your ORM to communicate with the database? If so, the logic defined in the Business Object Layer has to be working with some kind of DbContext setup to use the name of your database defined in the connection string via Web.config.

      Following this article, your application should be able to tie directly into Identity Framework. Everything will be setup to use Code First for that portion. In this case you’d just follow the official documentation for working with Identity Framework to use its functionality and you’ll be fine. All the work has been done to work with it and make it use your database for its tables. You wouldn’t be concerned with using the traditional means to use your database via the Business Object Layer for Identity Framework functionality because it wouldn’t be set to use that connection logic in the first place.

      Conversely, using the other functionality of your database will require a different connection string and thus a different DbContext. If your application uses Entity Framework then you’d just have to change the database context class derived from DbContext to use the name of your database defined in Web.config. The constructor takes a string parameter which calls into base. This parameter is the name of your database defined within the connection string.

      Following this article, you’d have IdentityDbContext for Identity Framework and then SampleAppDbContext. You’d use the SampleAppDbContext to communicate with your database for everything but Identity Framework. Thus, all I could see you doing to get things working is changing the logic for the database in your Business Object Layer to use the right name of your database defined in the connection string, such as SampleAppDbContext.

  • sarath rachuri

    Thanks a lot. I faced a lot of troubles in integrating Identity with an existing database. And your solution worked.

  • Mike

    OK, I’m a little lost on something. I followed your sample and I essentially ended up with 2 models like you mentioned. 1 to use with Identity and 1 to use with the “rest of the app”. What I was looking to do was combine the 2. For example, if I add a new profile property to the User table, I don’t want to have to update my “ApplicationUser” class and the classes generated from my EDMX. I want to just update the EDMX and have the “ApplicationUser” object have the same properties. I tried defining a partial class in the Sample.Data project and had it inheriting from IndentityUser. Then I had ApplicationUser inherit from that. When I did this it created a mess because essentially the ‘id’ property is duplicated on the IdentityUser and EDMX classes. Any help would be greatly appreciated!

    • I’ve had similar problems when attempting what you described and decided it wasn’t worth the effort. For the time being, I probably wouldn’t go this route. Certainly it’s not ideal but I’m confident that the new release of Identity Framework will solve some of these gripes.

      Certainly things aren’t perfect right now and integrating Identity Framework with an existing database isn’t the easiest thing to do. I’m sure if you spend enough time messing around you might be able to get things to work the way you are looking for but at what cost? Time is money.

      I’d just get something working even if it isn’t the sexiest solution. Then when Microsoft addresses these issues with a future release then migrating things over shouldn’t (in theory) be much of a problem. Certainly there is no guarantee how easy things will be in the future but I’m optimistic.

      • Mike

        Thanks for your response. Shortly after posting my question I was able to accomplish merging the 2 models. I ended up doing something similar to this: https://github.com/KriaSoft/AspNet.Identity/blob/master/docs/Database-First.md

        It is working well. The only issue I’m having is accessing the current user over the lifetime of the application. I don’t feel it is appropriate to get the user in my UserManager on every request/page. (Web form app) I’m looking at a way of propagating the user/profile (or at a minimum the dbcontext and usermanager) throughout the user’s “session”. I’m leaning towards something like this: http://blogs.msdn.com/b/webdev/archive/2014/02/12/per-request-lifetime-management-for-usermanager-class-in-asp-net-identity.aspx

        I’d appreciate your thoughts. I’ve just started looking into asp.net identity this week so I may be missing something obvious.

        -Mike

        • I think you are on the right track. The MSDN blog post you mentioned is what I’d follow and it’s likely that what you develop based on the article content will work with a future release of Identity Framework – hopefully with no or minimal changes to your code. Granted, in this field there is no way to know for sure.

          It’s good to see articles like that because it shows Microsoft is acknowledging the issues with the framework and working to address them while adding new and exciting features.

          I see a lot of good things coming from Microsoft with their new leadership and believe things are only going to get better for everything. Granted, no company is perfect but I’m optimistic.

  • Nick

    Thanks, very clear article. I really don’t understand why they’ve made it so difficult to use Database First with Identity 2.0 – not everybody likes using/can use code first Microsoft!

    • In most common situations an existing database already exists making Code First a less than attractive approach. Granted, you still can reverse engineer via Code First an existing database but using the Design Surface via Database First is more appealing – at least to me.

      For smaller projects or projects where everything is starting from scratch, some developers actually prefer Code First. It’s all just a matter of taste. That’s why it’s important to ensure everything works with all available options and I’m confident Microsoft will be able to put together a better solution with the next release of Identity Framework.

  • Plamen 태권도 Yovchev

    What about creating new table in the database and try to register new user or login with already existing.

    It says that there is a change in the model and should enable migrations. After enable migrations it sait that might be data loss…..

    • Generally when you see anything saying you need to enable migrations, it’s referring to Code First Migrations so it will update your database to match what you have in your model classes.

      If you followed the article step by step then you should be able to create a new user without any issues when running your app in the web browser.

      May I get some more context or details so as to better answer your question?

  • Jim

    Great article! But how do I update the connection string if I’m using an external SQL database (hosted on Azure) as my main database? Then I’m not just using an .mdf file.

    • You would just use the different connection string format that allows for a successful connection to that external SQL database. Still make sure you follow the proper connection string formatting since the connection string used by Identity Framework is slightly different than the one used for your existing database as shown in this article.

      • TVSlob

        Make a dummy MVC file without EF. Configure the Default connection. Then add EF, make it data first, it will ask to configure a connection (don’t call it Default.). Click “Test Connection” and you’re off and running. The EF you will add has the words “ADO.NET” in it go figure. You now have 2 connections strings in your Web.Config. Then do what Daniel the Eagle says!

        • Deafritz

          Could I ask you to elaborate on this a little bit, please? You say to configure the default connection…configure it to what? From what I’m understanding, the configuration process is to take a piece out of the EF connection string and duplicate it into your regular connection string, but you’re saying to do it before hand. I guess I’m just confused what you mean when you say to configure that (Let me know if I’m just missing something; I’ve been fighting with Identity 2.0 for a couple of weeks now and I’ve been getting tutorials and stuff mixed up)

          • TVSlob

            Haha. I had same problem. Still wrestling with something that should be built in. Let’s see, a connection string is a connection string. If it connects in one place, it will connect in another. The EF connection string has some slight differences. Right click on “Connection Strings” and choose “Make new”. Then make one kinda just like you would import or export stuff in SQL Server or an ODBC driver. Now you have the EF connection string and the one you made. (or do this in reverse. make a connection string and then add EF. You can use both strings) Compare them and you will see that Daniel is correct, one uses a slightly different provider type: “The difference lies in the provider type, System.Data.EntityClient or System.Data.SqlClient.” Sorry I can’t help you any more. It’s been several months now that I”ve got this to work and I’m on to other parts of this MVC mess from MS.

      • Sven Rodriguez

        Hey Daniel! I worked my way through the tutorial and it works just fine when using localdb! But as Jim wrote when trying to connect to an Azure db I get an error stating “windows logins are not supported in this version of SQL Server.” As it attempts to connect to my DB using windows authentication which I understand is the default? Any way around this? I’ve tried to tweak the connection string of Identity to no end but it either gives me that message or “The entity type ApplicationUser is not part of the model for the current context” even though my DB contains the same tables and columns as the local one. Any help is much appreciated!

      • Joe

        As Daniel says, Identity uses a different type of connection provider (System.Data.SqlClient) whereas Entity framework uses System.Data.EntityClient. When connecting Identity to SQL Azure use a connection string similar to the following in your web.config.

      • sunil951

        All Good But now after doing all How to modify / add new fields / access it on user table ( aspnetusers ) ? Please share some more information since no post on internet give clear information on this.

  • Jim

    Forget my last comment. It worked with just copying the connection string! Great article, thanks!

    • Sorry for my late reply as I have been traveling. I figured copying the connection string would work. However, I’m sure the formatting of the connection string will matter just as much as it when using localdb. Is that what you found out?

    • ChenChi13☯

      I had the similar problem since I was including the AttachDbFile into my connection string so it was giving me an error, I removed it from my CS and it worked! I’m glad you made this comment Jim, it helped me a lot! 😀 BTW Great solution for lazy programers who don’t feel like making their own Authentication code (why would we when we can use Identity Framework :D)… Cheers Mates!

      • I agree with you. Usually it’s best to use something else on the market that has been tested extensively, in this case Identity Framework. Rolling our own isn’t necessarily a bad thing but there is a lot of work ensuring our solution remains secure. Why not just use one that’s already gone to that length?

  • Dylan Hyslop

    Great tutorial. But what if im not using a local .mdf file? My connection string for my edmx model looks like so: `

    • Please see my response to Jim and let me know if you have any other questions.

  • Thank you Daniel for this article, it has been very helpful. A question I have is how would I go about
    completely removing the Code First from my Identity database but still use the ASP.NET
    Identity 2.0.

    Thanks,

    • It’s my understanding that you cannot switch the implementation of Identity Framework 2.0 to use Database First. It was built to use Code First which is why you have to do the tasks mentioned in this article.

      It’s my hope that with the next release of Identity Framework that we don’t have to do this kind of thing and completely implement it to use Database First. We’ll see what happens. I’ll certainly write about it.

      • TVSlob

        This blog fixed my problem. I had added EF later to an MVC project. It was database first version. It asks for and makes the other connection string.

  • Bayu Alvian

    maybe this could be helpful, you can just use your current dbcontext connection string instead of creating a new connection string, for example : public ApplicationDbContext(): base(new SampleAppDbContext().Database.Connection.ConnectionString)

    • I haven’t looked into this yet nor tried it. Have you tried it or has anyone else reading the comments tried it who can verify this works?

      • Bayu Alvian

        Yes, I have tried and it works. But it will be better if you try it yourself first before believing me 🙂

        • Leonel Jimenez

          I know this is a very old subject but I’m Using Visual Studio 2017 Community Edition with Web Forms and Identity Framework, none of the above worked at least for this scenario but this.

          I made a new project, added the Entity Data Model (edmx), from that file I got my new database context nam, did the change Bayu Alvian mentioned ,using my new connection of course and worked, tables were created automatically on my existing database.

          Hope this helps someone else.

  • Morten Kristensen

    Nice article. How do I publish the Identity connection string to Windows Azure using Publish in Visual Studio 2013 ? It seems that this is impossible. You can only use the System.Data.EntityClient provider and not the System.Data.SqlClient provider, which Identity 2.0 connection uses … please help me here

    • I’m sorry to say that I haven’t published to Azure yet. Everything I’ve worked on so far has been hosted on bare metal infrastructure. I eventually want to get on board with Azure and look forward to eventually working with it.

      I’d suggest reaching out to Microsoft’s Azure support and cite this article. Perhaps they can provide some insight on this. I’d certainly love to hear it.

  • Mike Clarke

    So we have to have two connection strings in MVC5 with a Database First appication?

    • Only if you decide to use Identity Framework. Quite frankly I’m excited about the end of this month as ASP.NET MVC 6 will be released and we’ll see how the new version of Identity Framework stacks up. I’m hearing great things.

      • TVSlob

        Yeah, but how much is it going to blow up? I’m barely hanging on by a thread as it is. I’m thinking MVC is getting less IT respect than MS Access. . . .

        • I’m sorry to hear that. When I made my original comment I didn’t realize that MVC 6 wasn’t going to be production ready. I think that’s going to be the case sometime this year instead. I’m anxious to see what changes (hopefully for the better).

  • Nate

    Fantastic article with a detailed steps and explanation. You, sir, are awesome!

  • Abhilash P A

    Executed all the scripts. But when I try to create user using asp.net identity 2, I get an error saying ‘Invalid column name ‘Discriminator’. What I miss?

    • I haven’t seen that error before so I’m really not sure. Perhaps Identity Framework was updated and now newer versions aren’t working with the method described in this post. I’d like to know if others are having the same issue.

  • Alex

    Hi Daniel, great article !!!! I had spent more than 10 hours trying to resolve this problem and I actually resolved it thanks to you. I’m already subscribed to your blog, please don’t stop to publish good things like this one. Regards

    • TVSlob

      10 hours? ha! that is peanuts. I lost all my hair trying to figure this mess out. Guess what? It’s not done! They are going to release ASP.NET Core 1.0 which I am too tired right now to explain.

  • Paola

    Thank you so much. Works perfect! I am using SQL Server Express, so with a variation of the connection string, all was good. . Great article! =)

  • thank you , and very helpful and working well , but I’ve to ask you about few things please :

    1-In the end , and after all that work , why you change the connection string to IdentityDbContext , while our database and other tables in SampleAppDbContext .

    2- when i tried step by step , finally it’s worked well , but the new user info added actually to the local database , and when i changed the IdentityModels.cs connection to my general database “the db we created and added the asp roles,,,,, tables to it” , when i compile it’s working well , but after i register user , it’s break and get a lot of error .

    what i’m tring to say is , i created as your steps and followed you , but i want all the info and users and identity tables in one data base , and that what i got from following ur steps , but when i run , the user info added to the local db , and i wont that , cause i want all my project use only one db .. have a look on this picture please . and thanks for everything

  • Deafritz

    You don’t cover connecting to Azure in this so I’ve been doing some experimenting. When I created the EF model from my database I was given 3 options for how to connect to my server. 2 of these (the first and last) state they can be used for connecting to azure.

    I tried both and (as far as I can tell) the connection strings and all files associated with my model are structurally identical except that the “other” option also generated a .cs file for each of my tables under “model.tt” while the Default selection does not (they both generate a model.cs file)

    What gives?

    • I haven’t actually pushed to Azure just yet. Everything I’ve worked on has been hosted internally and not outside our network. My understanding is that the connection string just has to be slightly modified to be compatible with Azure. I wish I could give you more feedback on this but unfortunately I cannot at this time.

      I do have plans to deploy a solution to Azure in the future and will report any of my findings at that time. In the meantime, I request anyone reading this comment who could provide some valuable insight to please do so. Much thanks.

  • sweng

    It worked on first attempt. Very straightforward. Thank you.

  • Robert Whitaker

    Hi, very useful article. I want to use an MSSQL DB instead of localdb and I am using individual account not windows authorisation. I have followed the instructions apart from the section where the EF is generated in a separate project. I created the model in the same project but under a different name. When logging on via Facebook the following error is displayed:

    “The entity type IdentityUserLogin is not part of the model for the current context”

    The problem is in ControllersAccountController.cs Line: 332

    var result = await SignInManager.ExternalSignInAsync(loginInfo, isPersistent: false);

    I cannot locate IdentityUserLogin in the solution.
    I have created another standard MVC application with localdb and the identity function works OK.
    I cannot fathom the issue. Is it with the connection string, the generated model, using individual account or something else??

    • Robert Whitaker

      Hi, Now managed to get it working. Very good article. Thank you.

      • Sorry for not being able to reply in a timely manner. I’ve been quite busy with personal things. I’m glad you got it working. Cheers.

  • Ali

    How can I use Identity for Database first in MVC 6, ASP.NET 5?

    • I haven’t worked with the newest stuff yet from Microsoft since it’s not production ready. In addition, I have been working with other technologies as of late. Regardless, I’m interested in knowing more about setting up Identity Framework with ASP.NET vNext, MVC 6, etc.

  • Mark DiNovo

    Daniel,

    Great article but everything seems to break when I try to add a prior passwords table to check prior passwords when a user changes their pw ref this article.

    https://blogs.msdn.microsoft.com/webdev/2014/01/06/implementing-custom-password-policy-using-asp-net-identity/

    I’m getting these errors:
    Invalid column name ‘ApplicationUser_Id’.
    Invalid column name ‘ApplicationUser_Id’.
    Invalid column name ‘AspNetUser_Id’.
    Invalid column name ‘ApplicationUser_Id’.

    Can anyone give any direction on why I am getting these invalid column name(s).

  • Kim Ngan Trinh

    My “SampleAppDbContext” does not have “attachdbfilename=|DataDirectory|SampleAppDb.mdf” part, so I just need copy the name of initial catalog to “IdentityDbContext” and remove its “attachdbfilename” part.

    It worked on first attempt. Great article! Thank you so much.

  • Gibran Shah

    Hi Dan, great article. I tried out your sample project and it works like a charm. Now if I could only get your tweaks to work in my project. I still seem to be getting that exception: “the entity type applicationuser…”. What I’m not sure about is how your sample application knows that ApplicationUser maps onto the entity AspNetUser. IOW, the exception kind of makes sense: ApplicationUser is NOT part of the data model. But somehow your sample application works anyway. How?

    Thanks!

  • bipin thapa

    sir, can we get video reference of this article, it found me hard to understand.

  • Sorun Yaratan Adam

    Hi, great article!

    I had error: Database ‘*/x.mdf’ already exists. Choose a different database name. Cannot attach the file ‘x.mdf’ as database x.mdf

    I solved with removing line in connectionString of “IdentityDbContext” :

    “AttachDbFilename=|DataDirectory|x.mdf”

  • Naveed Butt

    Very good article. Containing all the information in one place. I did not have to go to any of the links you mentioned in your post. Great work! 🙂

  • Manuel Valle

    I need to add more fields to the aspnetusers table.. How to handle this? with database first approach?

    • If you are seeking to customize the tables provided by Identity Framework, to the best of my knowledge this shouldn’t be a problem. As long as you don’t delete any of the existing fields Identity Framework needs then it should work.

      Granted, I haven’t personally tried this so I’d love to know how it goes for you. Please let me know.

      • sunil951

        No we can not use aspnetuser table for customizing in this approach. I tried but still facing lots of issues. Less info on internet also. Many suggestion to use another table to handle but how and working example no one sharing.

        • james donoghue

          The aspnetusers CAN be used. Follow the below instructions:

          Be very careful when handling the aspnetusers table, DO NOT DELETE OR MODIFY ANY EXISTING COLUMNS and test after every change to the database.

          1. Add your new column to the table (I used SMMS to insert an “Address” nvarchar)

          2. Update your entity models in the project (for example, I updated Entity Framework using “Update Model from Database”, then I inserted the below to my AppUser class):
          public class AppUser : IdentityUser
          {
          public string Address { get; set; }
          }

          3. You can now update your controllers and views to use the new field, creating/updating should work fine.

    • sunil951

      Are you able to access fields added ? Please share info. I am in big trouble.

  • Thanks, this article is a great help! Keep up the good work 🙂

  • ev vk

    man, congratulations! amazing article!
    I was crying all days trying to do this, because i want to use database first approach and you helped!
    Some notations to take into account:
    1. Guys/girls, do not forget to add references to the data project as stated in the article! or you will have problems like me.
    2. If you do not find the mdf file in the second connection string, just delete the mdf part of the first connection string, and change the initial catalog as the second string. In this case Initial Catalog=identitydbfirst (this is the database name you are using )

    example:

  • Grab City Info Co.

    Great tutorial

  • michaelreina

    I found a similar article. Little broken English, but it may shed further light and knowledge to those looking. http://dollygohil.blogspot.ca/2016/01/customize-users-profile-in-aspnet.html