Those who prefer reading this site through RSS now can also subscribe for updates to .NET Tips & Tricks Community.
You want to configure ASP.NET 2.0 to store your Membership/Role Management/Profile data within a remote SQL database. You decide to register a new provider within your web.config file like below:
<membership>
<providers>
<add name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="MyDatabase"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
requiresUniqueEmail="false"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1"
passwordAttemptWindow="10"
passwordStrengthRegularExpression=""
applicationName="/"
/>
</providers>
</membership>
When you run your application on a machine without SQL Express, though, you see some weird behavior. You might get a SQL error like so:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
The root cause of the above problem rests in how the new provider was registered within the web.config file.
The <providers> section within the web.config file is implemented as a collection, and so it is possible to register multiple providers at the same time (this is useful when you want to have some users authenticated using one Membership store, and others authenticated using a separate Membership store).
By default ASP.NET 2.0 registers a set of default SQL Express providers within the root web.config file on your machine that create a SQL Express database within your /app_data directory to store/manage membership/role/profile data when you first access it. Because this is registered at the machine-wide level, all provider collections by default inherit this registration. Unless you explictly <clear/> or override the inherited value, your application will have this default membership/role/profile provider registered.
Because the above web.config file simply added a new provider -- and didn't clear or replace the default provider registration -- the above application now has two Membership providers configured. When you do a Membership.CreateUser() call in your code, ASP.NET will attempt to create the user in both membership databases. If you don't have SQL Express installed on your system, the create-user attempt will fail for this database - which leads to the errors and/or weird behavior above.
To fix this problem add an explicit <clear/> directive before your <add/> statements within your web.config file:
<clear/>
Note that you must do this for each provider declaration that you register. So if you are adding providers for <roles> and <profile>, make sure you add the <clear/> directive in their providers section as well.