This blog post summarizes – Usability of <clear/> when adding providers.
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.
Use <clear/> before adding new provider.
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=126.96.36.199, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Hope this helps.