Using SQL Membership with ASP.NET Application

• Introduction

Most ASP.NET projects implement features like Login, Security, Password encryption, User Roles, Access rights, etc. Lot of code and logic goes into implementing all of this stuff. SQL Membership resolves all these concerns just by a few clicks and configurations and above all (which I like the most) without a single line of code. :)

• Background

In this article, I will provide a complete walkthrough about using SQL Membership in ASP.NET projects. I hope you find this stuff beneficial.

• Using the Code

SQL Membership is a wonderful way of interaction between ASP.NET and SQL Server. SQL Membership provides 2 kinds of Authentication methods:

1. Using SQL Server Membership database (Kind of Form Authentication)
2. Using Local System authentication (Windows Authentication)

The process is relatively easy to implement. I will go step by step to show the implementation. Let's consider that you are going to develop an application from scratch, so here we go:

1. First, we need to setup the SQL Membership database. In order to create that database, please follow the steps:
1. Go to Windows Start menu>>All Programs>>Microsoft Visual Studio 2005/2008>>Visual Studio Tools and click Visual Studio 2008 Command Prompt
2. Type aspnet_regsql and press Enter key from the keyboard.
   
3. This will launch SQL Membership Wizard as follows. Click Next button to continue.
   
4. On the next screen select the first option to "Configure SQL Server for application services". Click Next to continue.
   
5. This screen is important as you will mention the Database information here. On the basis of that, table structures and other things will be created. You can use an existing database or create a new one.
   
6. After filling all the information click next and Finish. :) You guessed right, your SQL Membership database is ready now.
2. Now let's connect our ASP.NET Website application to this database. For this, first create an ASP.NET website in Visual Studio 2005/2008.
3. Now open the Web.Config file for the project and add a well qualified Connection String for the SQL Server Membership database you are going to use.
   Collapse 

   <connectionstrings>
       <add name="abcdCS" connectionstring="
       Data Source=myserver\sqlexpress;Initial Catalog=abcd;
       User ID=sa;Password=pass" 
       providername="System.Data.SqlClient" />
   </connectionstrings>

   This Connection String will be used by the ASP.NET Configuration for connection with the database.
   
4. After that, click on VS 2005/2008 Project menu>>ASP.NET Configuration
   
5. This will launch the ASP.NET Web Site Administration Tool. Here, you will test the database connection, manage users, roles, permissions, etc.
6. First click on the Provider tab.
   Here you will be testing the database connection. Click on both the links one by one to test the connectivity with database.
   
7. Clicking the above links will show screen as mentioned below:
   
   Click the "Test" link in each screen to check the connection status.
   
8. If your connection is successful, you will get a screen like this:
   

So far so good. :) We are done with the connection part of SQL Membership database with the ASP.NET website application.
Now in order to select authentication types, create users, manage roles, and access rules, you need to see the Security tab.

In order to setup the SMTP settings, Application Settings, Debugging and Tracing settings, and default error page, you need to visit the Application tab.