Download the Code iconOne of the most attractive features of ASP.NET 2.0 is the ability to use a new set of login controls based on a SQL Server 2005 provider. These new controls dramatically simplify the creation of Web-application features. For example, a membership application might restrict site access to registered users (members) or provide members with special privileges not available to anonymous (unregistered) users. Visual Studio 2005 automatically enables login controls for use with a SQL Server Express database file located in a Web site.With this default outcome, each Web site has its own SQL Server Express database membership provider.You can optionally configure multiple Web sites' login controls to work with a single SQL Server 2005 database. Using one SQL Server 2005 database with multiple Web sites can centralize and minimize membership administration chores.This article shows you how to set up a SQL Server 2005 membership provider and demonstrates how to configure a Web site for use with the membership provider.

Configuring a Site for a SQL Server Database Provider

Three main steps let you configure a Web site to use a SQL Server database as a membership provider. First, you must create the database with an appropriate set of database objects for serving as a membership provider. Second, you must configure the web.config file so that the Web site can connect to the membership provider database. In the second step, you should also modify the web.config file by adding membership, roleManager, and authorization elements and by altering the default authentication element to permit forms authentication. Third, you can perform additional site configuration, such as adding users and roles, by using the ASP.NET Web Site Administration Tool.

Creating the membership provider database. You can create a membership provider database by using aspnet_regsql.exe, which resides on your Web server at drive:\WINDOWS\Microsoft .NET\Framework\(versionNumber), where versionNumber equals 2.0.50727.

The aspnet_regsql.exe tool that comes with ASP.NET runs in either graphical or command-line mode and can configure a site for several different types of providers. When an administrator simply wants a membership provider, command-line mode is more efficient. The following example syntax creates a SQL Server database as the provider for membership services.

Path_to_file/aspnet_regsql.exe -E
  -S database_server_name -A mr

In this statement, the -E argument specifies integrated security.The -S argument designates the name of the database server instance that will manage the membership provider database.The -A arguments (m and r) designate the setup of both member and role services in the database.

By default, the aspnet_regsql.exe tool creates a database named aspnetdb that includes a collection of tables, views, and stored procedures to facilitate the operation of the login controls. You can extend the built-in functionality of the login controls by using custom code to invoke the aspnetdb database's built-in stored procedures.You can also modify the aspnetdb database to include additional database objects that complement the automatically generated objects.

Configuring a web.config file. To make a Web site use the login controls with the aspnetdb database, you'll need to modify the site's web.config file. If you're starting with the web.config file for a new Web site, you can copy and replace the default web.config file with the contents of the WebDot-Config.txt file that Listing 1 shows. You can download the complete file using the "Download the Code" link at the top of this article. The XML code in Listing 1 omits comment sections from WebDotConfig.txt file to shorten its length. Several elements are especially noteworthy.

  • At callout A, the connectionStrings element within the configuration element contains an add element for a connection string pointing at the aspnetdb database. In the database-creation syntax above, replace database_server_name with the name of the server that the aspnet_regsql.exe tool designates as the argument for ?S. ASP.NET is automatically configured to use the name SqlServices for the connection string pointing at the aspnetdb database.
  • At callout B, the authentication element within the system.web element of the configuration element contains three attribute settings:
  1. The mode attribute designates Forms authentication instead of the default Windows authentication for an ASP .NET web.config file.This setting lets the login controls and the membership provider manage site membership services.
  2. The loginURL attribute specifies the name and, optionally, the location of the page for logging into a Web site. The setting isn't strictly necessary in this example because the example specifies the default name and location, but including the attribute shows the syntax for designating a nondefault login.aspx page. If you want to specify a non-default path and file, you can replace the setting for loginURL.
  3. The name attribute designates the HTTP cookie name to use for authentication.
  • At callout C, the allow element within the authorization element of the system .web element lets anonymous visitors connect to the site. Changing the allow element to a deny element that has the same users attribute setting prohibits Web access by anonymous visitors.
  • At callout D, the membership element within the system.web element specifies parameters for a site's membership services.The applicationName attribute within the add element of the membership element designates an identifier value for all of a Web site's membership data. If you want two Web sites to share the same membership data, assign the same value to the applicationName attribute for both Web sites.
  • At callout E, the roleManager element within the system.web element specifies the parameters for a site's roles.To synchronize membership data between the roleManager and membership elements, you must ensure that both elements share the same applicationName attribute value.
  • Using the ASP.NET Web Site Administration Tool. The ASP.NET Web Site Administration Tool is an easy-to-use graphical Web application for configuring an ASP.NET web site.You can open the tool from Visual Studio 2005 by choosing Website,ASP.NET Configuration. Besides a Home tab, the tool includes three tabs labeled Security, Application, and Provider.The Security tab facilitates creating and managing users, roles, and folder-access rules such as which users and roles can access designated Web-site folders. The other two tabs help you manage Website capabilities such as the ability to send users email messages that contain new replacement passwords.

    From the tool's Home tab, you can invoke a Security Setup Wizard to help launch membership security services. The wizard walks you through the process of configuring a Web site for login controls.The Wizard lets you set up Web registration so that you can collect essential information from users. Then, for example, if a user forgets his password, he can use the RecoverPassword control on a Web page to have a new password mailed to his email address. Before creating a new password to send to the user, built-in code will ask the user a security question. If the user replies with the correct security answer, the process completes.

    The Security Setup Wizard provides no way to assign users to roles. Instead, from the Security tab in the ASP.NET Web Site Administration Tool, you click the Manage users link, which does allow the assignment of users to roles.You can also use this page to edit user settings and delete users. A link below the list of users opens a page similar to the one in the Security Setup Wizard for adding a new user.

    Using Login Controls

    The main purpose of the ASP.NET login controls is to let registered users log in to a Web site.The controls also help you manage the registered users at a site. For example, you can track the identity of a current user, determine whether the user is logged in, add users to roles, and keep track of the roles a user belongs to. You have a total of seven login controls that you can drag onto a Web-form page. Table 1 explains the functions of the controls.

    Visual Studio 2005 lets you drag login controls from the Toolbox to the Design view of a Web form. Figure 1 shows a Web-form page named Login.aspx as it looks right after the login control has been dragged to it. Immediately after dragging any Login control to a Web form, you'll see a smart tasks pane containing common actions that designers are likely to use with a login control. Clicking the Auto Format link in the smart task pane opens a link that lets you choose from a collection of pre-formatted designs for the login control.You can close the smart tasks pane by clicking the left-facing arrowhead glyph.To reopen the smart tasks pane, click its closed glyph, which appears as a right-facing arrowhead.

    Figure 1: Working with the smart tasks pane and properties

    The Properties window in Visual Studio provides more granular design-time control for the Login control (and all ASP.NET controls). Selecting the setting for any property causes Visual Studio to provide a short description of the property at the bottom of the Properties window.The first property in the Appearance group that Figure 1 shows lets you select from a preset selection of custom,Web, and system colors for the control's background.

    In addition to the Login control, two other controls are especially interesting because they allow different views for different users. The LoginStatus control can show one of two views depending on the status of the user currently viewing a page. Figure 2 shows a LoginStatus control above the LoginView control, LoginView1. As Table 1 explains, the LoginStatus control presents a distinct link in each of its views. The layout in Figure 2 shows the Logged out view, which has a custom setting for its LoginText property that reminds the viewer to log in for extra privileges.

    Figure 2: Customizing page content for different users

    The LoginView control below the Login-Status control can present different views for logged-in and anonymous users and for registered users who belong to different roles.The control has built-in templates for anonymous and logged-in users.You can select a template that shows content for anonymous or logged-in users. Before specifying content for the users in a role, you need to configure templates for the roles and name them after roles that you created with the ASP.NET Web Site Administration Tool.

    You'll discover many other out-of-the-box uses for login controls. To learn more about how to work with login controls, you can read "User Controls, Master Pages, and Login Controls," the bonus chapter for my most recent book, Beginning SQL Server 2005 Express Database Applications with Visual Basic Express and Visual Web Developer Express: From Novice to Professional (Apress, 2005). The chapter includes a section that describes the basic functionality of nearly all the login controls.

    Programming Membership Providers with ADO.NET

    The aspnet_regsql.exe tool populates the membership provider database with a collection of stored procedures,tables,and views for programmatically managing membership services at a Web site. By using standard ADO.NET and ASP.NET development techniques, you can invoke these stored procedures to survey and manipulate the users and roles at a site. Although Microsoft recommends using the built-in Membership objects that are part of ASP.NET, DBAs who do some ASP.NET development might find it helpful or instructive to directly access the automatically generated stored procedures, tables, and views within the membership provider database.

    Figure 3 shows a simple example Web form, Users_and_Roles.aspx, that automatically lists the users and roles at a site when the page opens. In the second list box, which has a preceding label of All Roles, if a user selects a role, the code behind the Web form populates the third list box with the names of the users in that role. If in the first list box that enumerates site users, the user selects an item and clicks Add user to role, the code behind the form adds the selected user in the first list box to the role selected in the second list box and updates the items in the third list box to show the new user in the role. Figure 3 shows what the third list box looks like after the addition of the supervisor1 user to the Administrator role.

    Figure 3: The Users_and_Roles.aspx web form

    The code for the Users_and_Roles.aspx application is relatively simple if you already know ADO.NET. However, the correct syntax for referencing the connection string for the aspnetdb database in the web.config file is different than in previous ADO.NET versions. The following Dim statement for css1 references the ConnectionStrings element within the ConfigurationManager element of the web.config file in Listing 1.

    Dim css1 AS _
     ConnectionStringSettings
       Collection = _
     ConfigurationManager.
       ConnectionStrings

    The following Dim statement for the cnn1 SqlConnection object references the SqlServices connection within the Connection-Strings element in Listing 1.

    Dim cnn1 AS NEW
      System.DATA.SqlClient. _
      SqlConnection(css1
        ("SqlServices").ToString

    You can put these Dim statements in any convenient location for code that needs the cnn1 SqlConnection object, such as at the module level when two or more procedures in a module need to reference cnn1.

    When a user selects a role name in the second list box in the Users_and_Rules.aspx application, the user fires the SelectedIndexChanged event procedure (which you see at callout A in Listing 2) for the lbAllRoles ListBox control. This event procedure, which adds the users in the selected role to the lbUsersIn-Role ListBox control, collects the name of the selected role and passes it as a parameter to the PopulateUsersInRole procedure at callout B in Listing 2.

    The code for the PopulateUsersInRole procedure at callout B uses a SqlCommand object (cmd1) to reference the aspnet_UsersInRoles_GetUsersInRoles stored procedure within the aspnetdb database. The aspnet_regsql.exe tool automatically populates the database with the stored procedure. The stored procedure requires two parameters, one for the name of the application represented by cstAppName and the other for the name of a role, which is passed to the procedure from the selection in the lbAllRoles ListBox control. The PopulateUsersInRole procedure recovers the usernames in the selected role by iterating through the rows of a SqlDataReader based on the cmd1 SqlCommand. These names are added to the Items collection of the lbUsersInRole ListBox control within a While...End While statement.

    The other ListBox controls on the Web form page in Figure 3 are populated with code similar to Listing 2. Of course, the names of the referenced stored procedures change. Some of these stored procedures simply return values, but others let you add and delete users and roles.The code to add a user to a role uses the aspnet_UsersIn-Roles_AddUsersToRoles stored procedure. You can explore all the stored procedures in the aspnetdb database with the help of Server Explorer in Visual Studio 2005 or SQL Server Management Studio.

    Why Use Login Controls?

    Login controls offer powerful, easy-to-use features for managing membership and role services at an ASP.NET Web site. In many cases, you can achieve useful functionality just by dragging one or more login controls to a Web form. In other cases, simple ADO.NET code lets you take advantage of automatically created stored procedures for managing membership services.

    ASP.NET automatically uses a SQL Server Express database file in a Web site to manage membership services in coordination with login controls. Configuring one or more Web sites to use a single SQL Server database simplifies the management of membership services.