Early last year, I posted a couple of articles on how to setup Forms Based Authentication (FBA) in Windows SharePoint Services (WSS v3).
Here are the links:
FBA Walkthrough Part 1
FBA with MySites Walkthrough Part 2
Many people have used this to setup FBA in their environments successfully. One of the most common issues that come up with many people is that they are not able to resolve users even though the web.config files are configured correctly. The primary reason ends up being that the Application Pool account for either Central Administration or the Web Application in question has not been granted the appropriate permissions inside of SQL Server to access the membership information. I referred to this step as the magic step in my earlier posts, but never documented it. Well the time has come to document that step, so here it is.
I am documenting this for SQL Server 2005. The same thing can be accomplished in SQL Server 2000, albeit the steps and screen shots will be different (obviously).
As mentioned in my previous post, you will need to run aspnet_regsql to setup the membership database. This is documented nicely in FBA Walkthrough Part 1.
The previous article stops there however and did not discuss permissions. In the following steps, I assume that the database you created is called "AspNetDb_www.yourwebapplication.com".
Open up SQL Server Management Studio. When presented with the Connect to Server dialog box, make sure the Server type drop down is set to Database Engine as indicated in the following screen shot.
Expand the Databases node and locate your membership database. Then expand your membership database node, then the Security node within it.
Right click on the Users folder and select New User from the context menu.
The Database User - New dialog will appear. Click the Browse button next to the Login name text box.
In the Select Login dialog, enter the object name you wish to grant access to. Let's begin with the Central Administration application pool account. Keep in mind, your account name will most likely be different than mine. In my example, I browsed for and located my Central Administration application pool user named moss\ossservice. Click OK after confirming it via the Check Names button.
Enter a User name that will map to the NT Login name. I use the same name as NT minus the domain portion. Next, in the Database role membership section, check all of the of the Role Members prefixed with aspnet_. It is possible that all of these role members are not needed, but I have not taken the time to determine the minimum set of permissions required for this to work. If someone has tested this and would like to share that information, please do. This is still a whole lot better than making the application pool account a db_owner!
Repeat the process with the application pool account for your web application.
Keep in mind that on a development machine these may very well be the same account, but in production, I would hope that they are not. That would be bad, very bad.
I hope this helps clear up some of the confusion around my previous post.
Happy FBA'ing!