I spent the last week or so setting up Forms Authentication on Sharepoint 2007 (MOSS 2007) and the SQL Membership provider.  There are a couple of good blog posts out there, but judging from the number of comments, this configuration is still frought with potential errors.  Here's my summary, list of resources, and the new findings that weren't documented anywhere else.

1. Install the ASP.Net SQL Membership provider.  I used instructions here - step 3 has the basic info. (run aspnet_regsql.exe from .Net Framework 2.0 dir).

• I only had one instance of SQL Server on my server - the one installed by MOSS under the instance name ./OFFICESERVERS.  The aspnet_regsql.exe tool properly installed the membership database in this instance, but the connection string in machine.config was not correct, and pointed to ./SQLEXPRESS. 

2. Create users in the membership database that you want to use on your Sharepoint site.  There are no default tools to do this if you don't have VS installed on your SharePoint server (which I don't).  The article referenced in step 1 describes how to create a login page using the CreateUserWizard control that allows you to create users, which is what I did. 

CRITICAL STEP: In the web.config file Membership configuration, there is an attribute called 'applicationName', which is associated with any users you create from that web site. 

<membership defaultProvider="AspNetSqlMembershipProvider" >
  <providers>
    <clear/>
    <add name="AspNetSqlMembershipProvider"
             connectionStringName="MyLocalSQLServer"
             applicationName="MyAppName"
             type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  </providers>
</membership>

Since I had used 'MyAppName' as the applicationName in the sample, the users I created were mapped to this app name.  The Sharepoint samples use an applicationName of "/", so the users won't show up unless they are the same.

3. Configure the SharePoint site to use Forms authentication as described in the following resources:

TechNet SharePoint 2007 Authentication Samples

This first article describes the most basic configuration required, which is essentially to set the authentication mode of your SharePoint site to 'Forms' in Central Administration -> Application Management -> Authentication providers, and set the membership and role provider names.  The other articles tell you to put the configuration in the web.config files of both the Central Administration site and your SharePoint site.  After all my work I removed the web.config specific configuration and only left the machine.config entries, and everything still worked.  Lesson: if you use the right applicationName, you can save yourself a lot of work (you can ignore the articles below, but they provide good detail if you get stuck.

Nick Swan's Blog - SharePoint 2007 Forms Authentication
SharePoint Team Blog - Configuring Multiple Authentication Providers
SharePoint Unplugged - Forms Based Auth in MOSS

4. Map a user (from the membership db) to the SharePoint site as administrator.  Central Administration -> Application Management -> Site Collection Administrators (or Policy for Web Application)

The blogs above describe doing this in the two places mentioned, I found that only the 'Site Collection Administrator' setting is necessary.  I don't know the difference between the two, but since the policy section seems more oriented toward overriding or restricting specific user permissions, I'm going to stick with the site collection administrator setting.

5. Log in to the SharePoint site using the user account from step 4.  If all is well, you'll be in.  See references above if it doesn't work.

Error conditions:

• Login page simply refreshes, no error displayed.  This is what I continually saw when there were no users in my membership database mapped to the correct applicationName
• Login page has no stylesheet applied.  I saw this a couple of times while mucking with the configuration, but it went away after tweaking the web.config files some more.  I never had to resort to debugging this condition
• Central Administration displays 'No exact match found' errors when trying to add or search for users.  Again, the applicationName must match, as well as have the correct configuration, or the users won't be found.  The blogs state that wildcards don't work, but you can use the TSQL wildcard '%' in the search dialogs to search the sql membership database.

SharePoint Designer 2007 and Forms Based Auth

A note on using a site with Forms based Auth. SharePoint Designer.  There are two ways you can use this with Forms based auth.  The first (and what SharePoint Designer tells you to do) is to sign into the site first and select the 'Sign me in automatically' checkbox.  Alternatively, you can use the Intranet/Extranet scenario described in of the team blog above.  You can have the same SharePoint site accessible using different authentication schemes by 'extending' a site. (Central Administration -> Application Management -> Create or Extend Web Application.)  When you extend an existing site, you create a new Zone, and each zone can use a different authentication mechanism and membership provider.  I extended my SharePoint site by specifying a new web site/port combination, and left it in Windows auth mode.  I can access this site from SharePoint Designer directly.  I'm not sure if this will end up creating problems such as broken links, etc., but it works.