Creating ASP.NET WebApplication/ASP.NET MVC Application Membership Database on SQL Server rather than LocalDb

I was asked this question from one of my blog reader: How can a DB be created in SQL Server rather than locally (LocalDb) which is default?

When you create new ASP.NET MVC Application (using regular template ‘Internet’) or ASP.NET WebApplication, you will notice the following connectionString in Web.config file:

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-MvcApplication1-20121005163323;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-MvcApplication1-20121005163323.mdf" providerName="System.Data.SqlClient" />

Actually this helps MVC Application or ASP.NET WebApplication to generate database locally (within project in App_Data folder) at run-time for accounts/login management (membership). If however you want to generate this database in SQL Server then you can make some quick changes in the above Web.config file to do that, so here is what you need to replace in the above db mapping.

<add name="DefaultConnection" connectionString="Data Source=ITORIAN-PC;Initial Catalog=ASPNETMembership;Integrated Security=True;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient" />

You probably have a different data sources so change it before running. Now, run the application and click on ‘Register’ link to create an account and then you are all done.


Open the SQL Server Management Studio and look at the generated DB for this application.

At this stage if you run your application you will get following error:

 Login failed for user IIS APPPOOL\*

 To fix this you need to update IIS Process Model Identity in Application Application Pool.

Hope this helps. Thanks.

Comments

  1. UnknownTuesday, October 16, 2012 2:52:00 am

    Nice 1

    ReplyDelete
  2. AnonymousSunday, November 04, 2012 12:46:00 am

    I was struggling to create the users in sqlexpress but it was being created in localDB by default. Your post helped me create the users profile and other membership tables in sql express.
    Many many thanks for your advice.

    ReplyDelete
  3. AnonymousTuesday, July 22, 2014 3:00:00 am

    In this application I registered and in the data base i added new table with the fields that contains some and we have created views like list,create,edit etc., if log in into the application then list view should display only my record not others and should we implement this

    ReplyDelete

Post a Comment

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity

Image
As you know ASP.NET Core Identity (table structure) is different from what we had earlier in ASP.NET Identity. Actually the identity system which we have today with .NET Core is very mature and continuously evolved be it ASP.NET Membership, ASP.NET Identity 1, ASP.NET Identity 2 and now ASP.NET Core Identity. Recently I had to migrate few application to ASP.NET Core and similar its identity database. Because the table schema is changed, i had to re-think and create migration script which I would like to share with you today. It is very simple and easy, just three step and I had everything ready: STEP 1 : Change name of existing tables STEP 2 : Create ASP.NET Core Identity tables STEP 3 : Migrate data from old tables (ASP.NET Identity) to new tables (ASP.NET Core Identity) Script:  https://gist.github.com/itorian/c699e8534b392a6c726ec66c48100072 You should also watch my video, where I demoed migration.
Read More »

Customize User's Profile in ASP.NET Identity System

Image
Note: You should read this post instead, I found below walkthrough will not work on ASP.NET Identity 2. I will re-work on this post soon. In this post you will learn how to customize User’s Profile and add some more fields like FirstName, LastName, EmailID etc with ASP.NET Identity System. In my last post I listed some of the great new features introduced with Identity system .
Read More »