How to configure FBA (Forms-Based Authentication) in SharePoint Server 2010 using IIS 7 and ASP .NET Membership Database (SQL) like in MOSS 2007!!!

After testing many times and configuring FBA in many Virtual environments, finally I think I wrote the easiest and clearest procedure and hope what I’m saying is true, please read carefully and do not skip any step.

To configure FBA using SQL Server with Windows Integrated Authentication (recommended) we’ll follow the following steps.


a)      Knowledge about SharePoint 2010, IIS 7.5 and SQL Server 2008

b)      SQL Server configured as Windows Integrated Authentication

c)       ASP.Net SQL Server Database (Membership database)

d)      Windows Domain user account (Can be SharePoint Farm Administrator)

e)      Make sure you backup web.config files for Central Administration Site, Security Token Service web service and also the Web Application you will use for FBA

Steps to configure FBA

  1. Configure SQL Server security settings to use Windows Integrated Authentication
  2. Add the Domain User Account to SQL Logins
  3. Create SQL FBA database to store non-Windows domain users
  4. Grant SQL Login (Domain user) permissions for the new SQL FBA database
  5. Configure SharePoint Central Administration
  6. Configure  SharePoint Security Token Service
  7. Create the new Web Application to use FBA and Site Collection
  8. Configure IIS Default providers for the new web application
  9. Create IIS Site to administer ASP.NET users

Configure SQL Server to use Windows Integrated Authentication

Basically when you install SQL Server by default Windows Integrated Authentication is already set, in case your DBA team configured SQL Server with Mixed-Mode you have to do the following:

  1. Go into the Management Studio of the instance of your SharePoint database server
  2. Right click on SQL Server Instance name and choose properties
  3. On the left pane go to Security
  4. On the right pane choose the option Windows Authentication mode

If you did the change you MUST restart SQL Server service or reboot the server

Add the Domain User Account to SQL Logins

Now we need to add the Domain user account to the SQL Logins in order to provide full access to the ASP.NET membership database we’ll create later.

Two things are important here, you have to decide if you will use the SharePoint Farm Administrator account you are already using or create a new Windows domain user account to manage the access to the ASP.NET membership database. I recommend using SharePoint Farm administrator, whatever you decide here are the steps. (Ask for your DBA team to help you creating the new login)

  1. Once you have decided which account to use open SQL Server Management Studio
  2. On the left pane expand the SQL Server instance and choose Security folder
  3. On Logins right click and choose New Login
  4. Look for your Windows domain user account and keep Windows authentication option marked
  5. Click OK


Create SQL FBA database to store non-Windows domain users

Open the ASP.Net wizard and creating the database which will store non-Windows domain users for your extranet access:

          Go to the path: C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

          Choose “Configure SQL Server for application services

          Type the SQL Server name you want. You don’t have to use the SharePoint database server

          Keep Windows Authentication as default

          Use “Default” or type the database name you will create at the end of the process

The first time you create the ASP.NET database you have to choose Windows Authentication and create or USE an existing database*

          Just confirm the settings and that’s it for this part

*You can create a new ASP.NET database to store new users or you ALSO use the one you already have in your MOSS2007, just make sure you are having the correct permissions over the migrated database to the new SQL Login you already created in steps before.

 Grant SQL Login (Domain user) permissions for the new SQL FBA database

          Go back to the SQL Server Management Studio

          Locate the ASP.NET membership database you already created or restored

          Right click and choose properties

          On the right pane click on the square with dots and look for your domain user account (Type domainname\username) and click OK

          Copy the name from the field Login name to the upper field named User name

          At the bottom of the window locate Database Role Membership section and mark db_owner and click OK

Configure SharePoint Central Administration

Now we have to create a Connection String, Role Provider, and Membership Provider in IIS Manager to get access to the ASP.NET membership database that we already created.

Let’s do this once for SharePoint Central Administration site and then repeat the steps for the Security Token service web service, the SharePoint Web Application and at the end for the IIS site that we will use to mange ASP .NET Memberships.

Do not forget to have a backup of web.config file, is very important in case you need to roll-back. Also use a test environment first

          Open IIS Manager and locate SharePoint Central Administration v4 site under Sites folder

          On the right pane double click on Connection Strings

          On the very right pane called Actions click on Add

          Type a name

          Type the name of the SQL Server

          Type the name of the database server you already created

          Keep selected the option Use Windows Integrated Security

          Click OK

          Go back to the IIS to create both providers

          Double click on Providers in IIS Features view and create Role Provider and Membership Provider

–           On Features drop down menu click .Net Roles first to configure role provider, then click on .Net Users to configure Membership provider


          This is it for Central Administration site


Configure SharePoint Security Token Service

Repeat the steps just before to configure Security Token service web service. Use same Connection String,  Role Provider and Membership Provider names


Create the new Web Application to use FBA and Site Collection

Open SharePoint Central Administration Site and let’s create the new Web Application that you will use to configure Forms-Based Authentication

Remember in SharePoint 2010 the only way to configure FBA is creating web applications with Authentication option “Claims Based Authentication” otherwise you won’t be able to do it.

          So open Central Administration web site

          Go to Application Management category and click on Manage Web Application

          On the ribbon click on New

          Choose Claims Based Authentication authentication option in order to enable Forms Based Authentication options

          It is important for you to keep Enable Windows Authentication with NTLM check box marked for two things:

  • Crawl works for that content database
  • Active Directory users will use Authentication page to get the content

          Now check Enable Forms Based Authentication (FBA) and type both Membership Provider and Role Provider

          Now we have to create a Site Collection, you may use the one for Teams or any other, it’s up to you

          At this point the Site Collection Administrator MUST be the SharePoint Farm Administrator since our ASP.NET SQL database is empty

          Create the Connection String, Role Provider, and Membership Provider following the same steps like we did for Central Administration v4 Site

SharePoint Server 2010 has to use “i” as default Membership Provider and “c” as default Role Provider since “I” provider is linked to: Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider. (


Create IIS Site to administer ASP.NET users

Create an IIS site to manage roles and user account that will live in our ASP.NET membership database

          Create the IIS Site from IIS Manager

          Review again the steps we performed for Central Administration v4 site in order to configure:

  • Connection String
  • Role Provider
  • Membership Provider

          For the IIS Site you should not revert the default provider to “i” or “c”, this time default providers will be FBARoleProvider & FBAMembershipProvider


          Test time, log in to the new SharePoint site configured with FBA

          First use Windows Integrated Authentication, successful?

          Now choose sign in with a different user, successful? I’m sure not since you have not granted permissions for FBA users yet

          Go back to IIS Manager and create FBA roles and users

          Ok, now we can add FBA users to SharePoint sites, login back with SharePoint Farm Administrator

          Open Site Actions > Site Permissions

          Pick a group you want to add the new FBA user

          Click New

          Click the address book or type the name of the FBA user


I wish you good luck and remember that feedback is very important for us, please let me know if this post is really useful and if it was easy to configure FBA in SharePoint 2010 as I wrote

Comments (7)

  1. Anonymous says:

    Did it help?

  2. Anonymous says:

    Hi Counie,

    You can create your own application to manage users on SQL Database for FBA, the easiest way to me is creating an IIS Site from IIS Manager and connect that site to the Connection String already created and add the Membership and Role Provider also already created, then you MUST set default providers to FBAMembershipProvider and FBARoleProvider respectively like in this example, these 3 steps will connect the IIS Site to the SQL database storing users and then using IIS Manager Add or Delete users as you need. Click on the IIS Site, on the main or central panel click .NET Users and over the action panel you can add or delete the users, you can also Edit accounts but I'm not really sure if you can reset a password. The role who can handle users is the Windows Administrator or any other role that can manage IIS Manager.


  3. Anonymous says:

    Carlos – Seguramente existe algún redireccionamiento incorrecto en tu configuración de SharePoint, si tienes un balanceador confirma que esté apuntando correctamente a los servidores con las ip correctas, valida la configuración de bindings de IIS para el sitio que usa FBA. Me parece extraño lo que comentas porque al final FBA es un proveedor de autenticación y los usuarios que no son del dominio se alojan en una base de datos que vive en un servidor que asumo no se le cambió la IP

  4. Great article says:

    Great article

  5. Counie says:

    Hi Raymond.

    First of all Thanks for posting this article.

    FBA is already configured in my environment ( by someone else) but I was curious to know how to set up the IIS site to manage FBA users.

    The details on how to set up the IIS site are not clear to me.

    Then it jumps straight to "go back to IIS manager…"

    Is IIS manager the place to manage these FBA accounts ? OR is their a website provided whereby FBA users can manage their own accounts/passwords etc. ?

  6. Carlos says:

    Que tal ! me puedes ayudar con la siguiente consulta.

    Cambiamos de ip el servidor que aloja la intranet, pero al cambiar esto el FBA me busca los usuarios de registros de la anterior ip y solo los usuarios creados anteriormente acceden mas no así los nuevos, he visto y es porque se conecta a la ip anterior..

    Que puedo hacer?

Skip to main content