Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 or SQL Server 2005

Scott Guthrie
Product Unit Manager, Web.NET

September 2005

Applies to:
   Microsoft ASP.NET 2.0
   Microsoft SQL Server 2005 Express Edition
   Microsoft SQL Server 2005
   Microsoft SQL Server 2000

Summary: Describes how to set up the new ASP.NET Membership, Role Management, and Personalization services to use a regular SQL Server instead of Microsoft SQL Server Express. (8 printed pages)

Note This article originally appeared on Scott's blog. Join in the discussion here.

Contents

Quick Review: What Are the New ASP.NET 2.0 Application Services?
Default SQL Express Providers
Conclusion

Quick Review: What Are the New ASP.NET 2.0 Application Services?

Microsoft ASP.NET 2.0 includes a number of built-in "building block" application services. We call them "building blocks" because they are useful core frameworks for enabling super-common scenarios with Web applications today—and as a result, they can provide significant productivity wins and time savings for developers.

They include:

• A membership API for managing usernames/passwords and secure credential management, and a roles API that supports mapping users into logical groups.
• A profile API for storing arbitrary properties about both authenticated and anonymous users visiting a Web site (for example, their zip code, gender, theme preference, and so on).
• A personalization API for storing control customization preferences (this is most often used with the WebPart features in ASP.NET 2.0).
• A health monitoring API that can track and collect information about the running state and any errors that occur within a Web application.
• A site navigation API for defining hierarchy within an application and for constructing navigation UI (menus, treeviews, bread-crumbs) that can be context specific, based on where the current incoming user is in the site.

The ASP.NET Application Service APIs are designed to be pluggable and implementation agnostic, which means that the APIs do not hardcode the details of where data is stored with them. Instead, the APIs call into "providers," which are classes that implement a specific "provider contract"—which is defined as an abstract class with a defined set of methods/properties that the API expects to be implemented.

ASP.NET 2.0 ships with a number of built-in providers, including:

• Microsoft SQL Server Express provider for going against local SQL Express Databases.
• Microsoft SQL Server 2000/2005 providers that work against full-blown SQL Servers.
• Active Directory Provider that can go against AD or ADAM implementations.
• XML provider that can bind against XML files on the file-system (used for Site Navigation).

The beauty of the model is that if you don't like the existing providers that ship in the box, or if you want to integrate these APIs against existing data-stores you are already using, then you can just implement a provider and plug it in. For example: you might already have an existing database storing usernames/passwords, or an existing LDAP system you need to integrate with. Just implement the MembershipProvider contract as a class and register it in your application's web.config file (details below), and all calls to the Membership API in ASP.NET will delegate to your code.

Default SQL Express Providers

Out of the box, most of the ASP.NET 2.0 application services are configured to use the built-in SQL Express provider. This provider will automatically create and provision a new database for you the first time you use one of these application services, and provides a pretty easy way to get started without a lot of setup hassles (just have SQL Express on the box and you are good to go). Note that SQL Express databases can also be upgraded to run in the context of full-blown SQL Server instances—so apps built using SQL Express for development can easily be upgraded into a high-volume, clustered, fail-over secure 8P SQL box when your app becomes wildly successful.

How Do I Change the Providers to Use SQL Server Instead of SQL Express?

If you want to use a full-blown SQL Server 2000 or SQL Server 2005 database instance instead of SQL Express, you can follow the below steps:

Step 1: Create or Obtain a Blank SQL Database Instance

In this step you'll want to create or obtain a connection string to a standard SQL database instance that is empty.

Step 2: Provision Your SQL Database with the ASP.NET Schemas

Open a command-line window on your system and run the aspnet_regsql.exe utility that is installed with ASP.NET 2.0 under your C:\WINDOWS\Microsoft.NET\Framework\v2.0.xyz directory.

Note that this utility can be run in either a GUI-based mode or with command-line switches (just add a -? flag to see all switch options).

Using this wizard, you can walk through creating the schema, tables, and sprocs for the built-in SQL providers that come with ASP.NET 2.0. Figures 1 through 5 show the step-by-step walkthrough of this.

Figure 1. SQL Server Setup Wizard, Welcome screen

Figure 2. SQL Server Setup Wizard, configuring application services

Figure 3. SQL Server Setup Wizard, selecting the server and database

Figure 4. SQL Server Setup Wizard, confirming your settings

Figure 5. SQL Server Setup Wizard complete

Once you have finished walking through the wizard, all the database schema and stored procedures to support the application services will have been installed and configured. (Note: If your DBA wants to see exactly what is going on behind the covers, we also ship the raw .sql files underneath the above framework directory, and your DBA can walk through them and/or run them manually to install the DB.)

Step 3: Point Your Web.config File at the New SQL Database

ASP.NET 2.0 now supports a new section in your web.config file called <connectionStrings>, which (not too surprisingly) is used to store connection strings. One nice thing from an administration perspective is that the new ASP.NET Admin MMC Snap-in now provides a GUI-based way to configure and manage these, as shown in Figure 6.

Figure 6. Connection strings in the ASP.NET administration pages (click the image for a larger picture)

ASP.NET 2.0 also now supports encrypting any section stored in web.config files—so you can also now securely store private data such as connection strings, without having to write any encryption code of your own.

ASP.NET 2.0 ships with a built-in connection string called LocalSqlServer, which by default is configured to use a SQL Express database, and which by default the Membership, Roles, Personalization, Profile and Health Monitoring services are configured to use.

The easiest way to have your application automatically take advantage of your newly created SQL database is to just replace the connection string value of this LocalSqlServer setting in your app's local web.config.

For example, if I created my database on the local machine in an appservicesdb database instance and was connecting using Windows Integrated Security, I would change my local web.config file to specify this.

<configuration>
    <connectionStrings>
        <remove name="LocalSqlServer"/>
        <add name="LocalSqlServer" 
         connectionString="Data Source=localhost;
           Initial Catalog=appservicesdb;
           Integrated Security=True" 
           providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

Hit save, and all of the built-in application services are now using your newly created and defined SQL Server database.

Note The one downside with this approach is that I'm re-using the LocalSqlServer connection string name—which will feel weird if/when I deploy my database on another machine. If I wanted to name it with my own connection string name, I could do this simply by adding a completely new connection string, and then pointing the existing providers to use the new connection string name in place of the default LocalSqlServer one.

Conclusion

While the application services provided by ASP.NET 2.0 default to using SQL Server 2005 Express, it is a fairly simple matter to change this and use SQL Server 2000 or 2005. Once this change is made, you can then enjoy the benefits of the features of these database engines.

About the author

Scott Guthrie co-founded the ASP.NET Team, and leads the design team responsible for architecting the product. His individual contributions include: ASP.NET Web Forms Page Architecture, ASP.NET Web Services Infrastructure, ASP.NET Compilation System, ASP.NET Distributed Session State Infrastructure, ASP.NET Deployment Architecture, ASP.NET Reliability System, and the ASP.NET HTTP Runtime Architecture. Prior to ASP.NET, Scott was a member of the IIS and Windows NT development teams.

© Microsoft Corporation. All rights reserved.