ASP.NET - Add Membership Schema to Existing Database

Summary
This tutorial shows how to add all the membership stuff to an existing data and use it in an ASP.NET Web Application.

Description
The goal here is to easily add all the ASP.NET membership tables and related info to an existing Microsoft SQL Server 2008 R2 database and use this database in an ASP.NET Web Application.  Plainly, this means updating an existing MS SQL 2008 R2 database with stuff needed to allow users of the ASP.NET application to do things like login. 

Before we begin
For the tutorial, you'll need the following:

Visual Studio 2010 Web Developer or Professional - When we start a new ASP.NET Web Application project it will be in C#. 

Microsoft SQL Server 2008 R2 - You'll need at least the express edition installed, a database created, and a user.  The database can be a normal new database.  The user must use "SQL Server Authentication".  If you want to know how to create a new database, new user, and assign the user a role from the new database then see the previous tuorial here. 

Microsoft .NET Framework 4.0 - This is good to have, find it here and install it.  This tutorial is based on .NET 4.0.

Steps

1. Find the "ASP.NET SQL Server Setup Wizard"
2. Run the Wizard
3. Start a new ASP.NET Web Application
4. Open the Web.config file
5. Modify the Connection String
6. Done!!  Hit F5, make a new user, Login, Logout, Login
7. Not convinced?  Check the database.
8. Done, for reals.

1.  Find the "ASP.NET SQL Server Setup Wizard" - Open your C drive (or find your windows directory).  For me, the location is "C:\Windows\Microsoft.NET\Framework\v4.0.30319".  In the picture below, you'll see the other versions of .NET.  We want to choose "v4.0" because our ASP.NET Web Application project will use the .NET 4.0 Framework (Figure 1.1).  Within this folder find a file named "aspnet_regsql.exe" (Figure 1.2).



Figure 1.1

Figure 1.2

2.  Run the Wizard - Open the file from above, "aspnet_regsql.exe".  The wizard will start and the first page comes up.  Read it or not but click the "Next" button.  The next page says "What database task do you want to perform?".  On this page select the radio button next to "Configure SQL Server for application services" (Figure 2.1) and click Next. 

Figure 2.1

For the next page, we'll need to specify the Server, authentication type, user name, password, and select a Database.   The goal of this page is to find the existing database.  If you're lost here, I recommend going through the previous post here.  I'm assuming you went through the previous post.  This information we use here (Figure 2.2) should be the same we used to test the new user in the last step of the previous post.  NOTE:  Fill out the username and password before selecting a Database.  The username will be "mytestuser", the password will be "password", and select "TestDatabase" from the drop-down box for our database.   Just to compare the last step of the previous post to this one, here's the login information we used for our test server (Figure 2.3)

Figure 2.2

Figure 2.3 - Compare with 2.2

After the settings are filled in, click Next.  If you're sure click Next again. When it's finished, click Finished.  That's it, for the first part.

3.  Start a new ASP.NET Web Application Project - Open Visual Studio and start a new project.  Start a new ASP.NET Web Application, for the name use "LoginApplicationDemo" (Figure 3.1).  Do not select the "ASP.NET Empty Web Application",  we will be using the awesome default login setup with master pages.  Select the OK button.

Figure 3.1

4.  In the or new project, open the Web.config file. In the Web.config file, the "connectionStrings" tag we want to work with next is at the top (Figure 4.1).

Figure 4.1

5.  Modify the Connection String - We will add the following to replace the default connection tag in step 4 (Figure 4.1).  We are replacing this with a connection string that points to the existing database (TestDatabase) that we are going to use with this project (LoginApplicationDemo).  Our new connection string looks like this:

<conectionStrings>
  <add name="ApplicationServices"
connectionString="Data Source=DUTCHESS;Initial Catalog=TestDatabase;User Id=mytestuser;Password=password;"
           providerName="System.Data.SqlClient" />
</connectionStrings>

  You can see the new connection string in place in Figure 5.1.  What does this connection string mean?  Well, it's all relative to the other steps.  The "Data Source" was known as "Server Name".  The "Initial Catalog" was known as the name of the "Database".  The "User Id" was "Login" or "Username".  The "Password" is... well just compare the login information of Figure 5.1, Figure 5.2, and Figure 5.3.  Also, our new Web.config file is shown in Figure 5.1.

Figure 5.1

Figure 5.2

Figure 5.3

6.  Done!! Hit F5, make a new user, Login, Logout, Login - That was it.  (Figure 6.1, 6.2, and 6.3) Debug > Start Debugging (F5).  When you site comes up in your favorite browser, create a new user, logout, and login again.  You can even close the browser, stop debugging in Visual Studio.  To prove it's still in the database, select Start debugging (F5) again, and log into the site (Figure 6.1 and 6.3)



7.  Not convinced? Check the database. - Before completing this step, make sure you have completed step 6.  We're going to check the database (TestDatabase) for the new user created in step 6.  Open Microsoft SQL Server Management Studio, log in as "mytestuser" (Figure 7.1).  Click on "New Query" (Figure 7.2).  From the available databases drop-down list (currently says "master"), select "TestDatabase".  In the query area write "select * from aspnet_Membership;" and "Go;" (Figure 7.3).   Finally, click the "Execute" button and see your results towards the bottom of the page.  Scroll through this result, it is the user that you registered in step 6.

Figure 7.1

Figure 7.2

Figure 7.3

That is all.  I hope this tutorial has been helpful to you.

Comments/Questions?
I'm always wanting to improve the quality of the content here. If you have questions, want to see a better explanation, a better picture, let me know. Thanks - Sean