Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 or SQL Server 2005
Product Unit Manager, Web.NET
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.
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.
- 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.
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.
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.