One question I’ve seen asked a few times by people over the last few weeks is “how do I setup the new ASP.NET Membership, Role Management, and Personalization services to use a regular SQL Server instead of SQL Express?” This blog entry walks you though a few simple steps on how to-do this.
Quick Review: What are the new ASP.NET 2.0 Application Services?
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 can provide significant productivity wins and time-savings for developers.
They include: a membership API for managing usernames/passwords and secure credential management, a roles API that supports mapping users into logical groups, a profileAPI for storing arbitrary properties about both authenticated and anonymous users visiting a web site (for example: their zipcode, gender, theme preference, etc), a personalizationAPI 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, and a site navigation API for defining hierarchy within an application and 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: a SQL Express provider for going against local SQL Express Databases, SQL 2000/2005 providers that work against full-blown SQL Servers, an Active Directory Provider that can go against AD or ADAM implementations, and in the case of site navigation an XML provider that can bind against XML files on the file-system.
The beauty of the model is that if you don’t like the existing providers that ship in the box, or 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 in 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 walkthrough creating the schema, tables and sprocs for the built-in SQL providers that come with ASP.NET 2.0. The below screens show the step-by-step walkthrough of this:
Once you have finished walking through the wizard, all the database schema + sprocs 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 walkthrough 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) are 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:
ASP.NET 2.0 also now supports encrypting any section stored in web.config files -- so you can also now securely store private data like connectionstrings 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 connectionstring 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 the above 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.
Hope this helps,
