How to Configure ASP.NET Membership Using SQLMembershipProvider

Introduction

The objective of this article is to configure ASP.NET Membership to store user information in an external SQL Server database. We will use SQLMembershipProvider for this task.

ASP.NET Membership

ASP.NET Membership enables us to validate and store user information. It creates all the necessary tables and stored procedures to store and manage user credentials. It provides an API that can be used to manage user's information programmatically.

ASP.NET Membership provides two types of Membership providers:

1. SQLMembershipProvider : It is used to store user information in a SQL Server database
2. ActiveDirectoryMembershipProvider : It is used to store user information in an Active Directory.

To configure SQLMembershipProvider, first you need to configure your database.

Open Visual Studio Command Prompt (All Programs -> Microsoft Visual Studio 2008 -> Visual Studio Tools -> Visual Studio 2008 Command Prompt)

Type command : aspnet_regsql and press Enter

This will launch ASP.NET SQL Server Setup Wizard



Click Next

Leave the default selection "Configure SQL Server for application services" and click Next again.

Enter the server name, Select SQL Server authentication and enter user id and password as in the following figure. Select the database you want to configure. Here, I am using Employee database.

Click Next



Click Next and then Finish.

Now, you can see the new tables created in the Employee database for storing user credentials. These table names start with "aspnet_". Similarly, you can also see new Stored Procedures created in the database with "aspnet_" prefix.



Now, we have configured our Employee database. Our next task is to enable SQLMembershipProvider in a Web application. Follow these steps to do that:

• Create a new ASP.NET Web Application
• Add <authentication mode="Forms"/> in the Web.Config file. ASP.NET Membership works with only Forms Authentication
• Add the following to enable SQLMembershipProvider:
  
  <membership defaultProvider="MyMembershipProvider" >
  <providers>
  <add name="MyMembershipProvider"

  type="System.Web.Security.SqlMembershipProvider"
  connectionStringName="ConString" />
  </providers>
  </membership>
  

• Add a connection string with the name "ConString" to connect with the Employee database
  
  <connectionStrings>
  <add name="SqlConString" connectionString="Data Source=localhost\SQL2005EXPRESS; User ID=youruserid; Password=yourpassword;
  Initial Catalog=Employee;" />
  </connectionStrings>

Now, we are ready to use ASP.NET Membership to create a new user using CreateUserWizard control and authenticate users using Login control.