Friday, June 13, 2014

How to: Create ASP.NET MVC Authentication tables in an existing database

Hi All,

I am writing this blog post to show how to migrate or move the standard MVC authentication tables into your existing database in your asp.net web application.

Background
When you create a MVC web application in VS 2013, VS embed a sql server database in your project, and this project is being used as a source of asp.net membership. Since you will not use this database as an embedded file and you want to have it as part of your existing database in SQL Server instance. The file is created under App_Data folder. To see the .mdf file click on Show all files in the solution explorer.
You can see the contents of this database and all tables in the server explorer window.

How to create those tables in SQL server ?

First, you can delete the .mdf file in App_Data folder. Since we don't need any of these tables.Then, we need to update the default connection string in the web.config to point to our database.

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=SERVER\INSTANCENAME;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>


Third, Open Nuget Package Manager and write the following commands:

Enable-Migrations
Add-Migration Init
Update-Database

Check out your database, all ASP.NET membership tables with Prefix Asp have been create and then you can test it out by running your application and execute membership actions such as Signing up or Signing in to your application.

Created tables after running above commands:
AspNetRoles
AspNetUserClaims
AspNetUserLogins
AspNetUserRoles
AspNetUsers
__MigrationHistory


Hope this helps.

Thanks

-- ME

No comments: