Default Provider Configuration targets Sql Server Express
In an attempt to solve some of the basic problems with getting the Membership and Roles providers to work out of the box, I'd like to take a minute to talk about their default setup.
The default Membership and Roles providers are Sql based and target a local Sql Server Express (SSE) install. If your first attempt to load a page which does a Membership or Roles operation results in:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)
It's likely that SSE is not running. Maybe you didn't choose to install it during the product install. You can easily check if it's available (and running) by trying to start the service. Run the following from a command shell:
net start MSSQL$SQLEXPRESS
Ok, SSE is now running. Great. How are the providers configured to target it?
The providers' default configurations point them to use the connection string named "LocalSqlServer" which is defined in the "machine.config" file which lives in the framework config directory. In this file, you'll see the following in the connection strings section:
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" .../>
This connection string defines a "data source" value that points to a local SSE instance. From this connection string, you can also see the default database name is going to be "aspnetdb.mdf". The "|DataDirectory|" notation is special and ADO.NET replaces this value with the the app domain value for the "DataDirectory" variable. For ASP.NET site's, this value, by default, is "App_Data". I won't go into the significance of the "User Instance" value in this post since it's a bit beyond what I'm trying to address here.
So that's some basic info around the provider's default configurations and their use of SSE. Hope this helps at least a few people get the providers running right out of the box which was our goal.
