With version 2.0, ASP.NET has allowed developers to create their own custom providers that plug into the website framework. These providers cover the areas of Membership, Roles, Site Map, Session State, Profile, Web Event, and Web Parts.
The first option is to base your custom version on the built-in versions. Fortunately Microsoft has released the complete source code for the SQL Providers as well as extensive documentation. Scott Guthrie has collected the key resources to get you started.
Not all developers are satisfied with this option. In theory it is possible to modify the SQL Providers to work with your existing infrastructure. But as Michal Valášek says,
[The] database structure used by these providers is pretty complicated and almost impossible to interconnect with your own tables etc. Drop the “almost” for profile provider.
To address this, he has released the Altairis Simple ASP.NET SQL Providers through CodePlex. They provide the minimum functionality needed to work with the framework and a very simple table structure. This makes them ideal for grafting onto your existing infrastructure. Currently providers for Membership, Role, and Profile are available. They are written in C# and only require one or two tables each.
While in theory a developer can swap out any provider, sometimes there are internal dependencies that have to be considered. For example, the Hao Kung's Table Profile Provider is coupled to the default Membership provider.
For those working with database other than SQL Server, there are options. Rakotomalala Andriniaina has offered source code for Membership and Role providers for MySQL.