Professional ASP.NET 3.5 Security, Membership, and Role Management with C# and VB

12.3. Working with SQL Server Express

Sometimes folks think that there is a separate set of providers for SQL Server 2005 Express different from the regular SKUs of SQL Server. SqlMembershipProvider as well as all the other SQL-based providers in ASP.NET 2.0 and ASP.NET 3.5 work equally well against the Express and non-Express versions of SQL Server 2005. However, there are some differences in how the database schema is installed when using SQL Server Express.

SQL Server Express (SSE) is the low-end SKU of SQL Server 2005. It normally installs on a machine as a named instance: SQLEXPRESS. As a result, you can install SSE on machines running SQL Server 2000 or other versions of SQL Server 2005 without interfering with these installations. There is also a special mode of operation supported by SSE called user instancing. The internal database code shared across all the ASP.NET SQL-based providers includes special logic in the event a provider runs against SSE that has user instancing enabled.

The idea behind user instancing is that the central SSE named instance (identified in a connection string as server=.\SQLEXPRESS) can be used to spawn additional instances of the SQL Server worker process. These spawned instances are referred to as user instances. They are referred to as "user" instances because the SQLEXPRESS named instance spawns these extra worker processes to run with the account credentials of a user—specifically, the Windows user credentials that opened an ADO.NET connection ...