Configuring SharePoint 2013 Forms-Based Authentication with SQLMemberShipProvider

Post courtesy Partner Solution Consultant Priyo Lahiri


With SharePoint 2013, a lot of partners and customers are opening up their on premise deployment to their vendors and customers. While the way you would configure this is very similar to SharePoint 2010, things get a little tricky when you perform a real-world deployment spanned across multiple servers. This post is an end-to-end walkthrough of setting up Forms Based Authentication with SQLMemberShipProvider in a 3 tier SharePoint 2013 Deployment.


It would be whole lot easier if I had a single server environment with the same account running everything and that account is also a Domain Admin. However, I chose a different approach since most likely this is how your real-world deployment will be setup and the steps are little different when your farm is spanned across 3 servers. Here is my environment:

WFE01 – Web Server running Microsoft SharePoint Foundation Web Application. I am connecting to the SQL instance using an Alias. It’s a very smart move. If you have ever had to move your SharePoint databases across SQL Servers or decommission an aging SQL Server, you know that having a SQL Alias will save you from a lot of nightmares. If you are looking for a step by step, click here.

APP01 – Central Admin Server. Note: this is NOT running Microsoft SharePoint Foundation Web Application and is configured to be a “True” application server. This also means that the Web Application that we create will not reside on this server.

SQL01 – SQL Server running SQL Server 2012 with SP1

SharePoint 2013 server RTM and Windows Server 2012 RTM are used for this set up.

Tools to use

While the steps documented below can be done without these tools, they do make your life a whole lot easier.

1. FBA Configuration Manager for SharePoint 2013 – Author and Credit goes to Steve Peschka. The download comes with a ReadMe file. Please read it, since you need to register the WSP that comes with it.

2. SharePoint 2013 FBA Pack – Author and Credit goes to Chris Coulson. Here is the documentation that will tell you how to install/activate/work with it. This not only will this make usonly tested the user management er management a breeze, it has some very useful features like password reset and self-service account management.

NOTE: I have portion of the FBA Pack and didn’t have time to play with the rest of the features.

How it’s done

Step 1 – Create the Web Application

In this step we will be creating the web application with Windows Authentication (Claims) and Forms Based Authentication (FBA) on the same Zone. In SharePoint 2013, you can have multiple authentication providers without extending the web application. Having said that, at times, you might have to extend the web application depending on your scenario. More on that on a different post where I will show you how to use LDAPMemberShipProvider to talk to your AD.

From Central Administration, we will create a Web Application and call it and enable both Windows Auth and FBA. Note the names I am using: ASP.NET Membership Provider Name = SQL_Membership and ASP.NET Role manager name = SQL_Role. You can call them whatever you want, just ensure you use the same names everywhere.


We will create a new App Pool and use the Web App Pool account. Make a note of this since you would need to give this account permission in the next step in the ASPNET database.


Create the Web App and then the Site Collection, it doesn’t matter what template you choose. Once the Site Collection is created, visiting the site collection will take you to our default sign in page where you will be asked to choose an Authentication Provider to Sign In with. If you want your External Users only to have the option of FBA, you would want to set this default zone with Windows Auth and extend it and have the FBA on the extended web app. Obviously, the URL’s will then be different.

Your sign in page should look like this (make sure your DNS record (CNAME) point to the WFE01)


Do you want to see a custom sign in page with your company brand on it? Well, let’s defer that to a different post.

Step 2 – Verify Tools

Now that the web app is created, we will make sure FBA Pack and FBA Configuration manager is deployed as it should be. Go to Central Administration >> System Settings >> Manage Farm Solutions. Make sure fbaConfigFeature.wsp is globally deployed and visigo.sharepoint.formsbasedauthentication.wsp is deployed to See screenshot below. If the visigo.sharepoint.formsbasedauthentication.wsp is not deployed, click on the WSP and deploy it to your web application.


Login to the site collection created in the above step and activate the following feature:

Site Settings >> Site Collection Administration >> Site Collection Features >> Form based Authentication Management


Once the feature is activated, it should add the following to your Site Settings under User and Permissions


Step 3 – Creating the SQL Database for User Management

The first step is to create the SQL Database that would hold the Extranet Users

  • Browse to c:\Windows\Microsoft .NET\Framwork64\v4.0.30319
  • Run aspnet_regsql.exe
  • Click Next
  • Choose Configure SQL Server for Application Services >> Click Next
  • Enter your SQL Server Name , choose Windows Authentication and type in a Database Name


  • Click Next twice to provision the database
  • Now we need to add the Application Pool that runs the web application and give it required permission. In this case, the application pool name is waterfall\spweb. Perform the following steps:
    • Open up SQL Management Studio, Expand the database we created and expand Security
    • Right click Users and add a new User
    • User Type = Windows User
    • User name = choose <yourAppPoolAccountName>
    • Login name = browse and choose the login name (should be same as the app pool name above)


    • Click Owned Schemas and choose the following:
      • aspnet_Membership_FullAccess
      • aspnet_Persolalization_FullAccess
      • aspnet_Profile_FullAccess
      • aspnet_Roles_FullAccess
      • aspnet_WebEvent_FullAccess


Step 4 – Editing the web.config files

We need edit the following web.config files:

  • Web Application Web.config – WFE server
  • STS Application web.config – WFE server and Application Server
  • Central Admin web.config – CA Server
  • If you have more WFEs and App Servers, you need to edit them as well. A lot of people puts these in there machine.config file as well so that it gets inherited to the web.config file. I am not too keen on editing the machine.config file.

Let’s login to our WFE server and fire up FBAConfigMgr.exe. While you can get the code you need from here and edit web.config yourself, if you just let the tool run its course, it will create a Timer Job and do the task for you. In the FBAConfigMgr type in your application URL and from the sample configuration choose the following:

  • People Picker Wildcard
  • Connection String
  • Membership Provider
  • Role Provider

Here is what the screen looks like when default values are chosen:


We will modify the default values to reflect the following (highlighted items need modification per your environment):

  • Web Application URL -
  • People Picker Wildcard - <add key="SQL_Membership" value="%" />
  • Connection String -
    <add name="fbaSQL" connectionString="server=SQL01;database=Extranet_User_DB;Trusted_Connection=true" />
  • Membership Provider -
    <add connectionStringName="fbaSQL" applicationName="/"
    type="System.Web.Security.SqlMembershipProvider, System.Web,
    Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  • Role Provider -
    <add connectionStringName="fbaSQL" applicationName="/"
    name="SQL_Role" type="System.Web.Security.SqlRoleProvider, System.Web,
    Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>

The screen should now look like this:


It’s time to hit Apply Config. This will create a timer job to update your web.config files. Though it creates a backup, you should be proactive and take a backup of your web application web.config and sts web.config file. Here is how to back up the web.config file and here is how to find the STS web.config file.

Once you click Apply Config, the tool will tell you when it’s done. It might take a few mins before you see any changes, so wait for it (you should see a new backup file created for your web.config file with time stamp and _FBAConfigMgr in the end of the file). To verify that the job is done, open up the web.config for your web application and search for <membership. You should see the following:

<<Web Application web.config file>>


The ConnectionStrings gets added to the end of the file right above </configuration>


<<STS web.config file>>

Open up the STS Web.Config and you should see the following:


The ConnectionStrings gets added to the end of the file as well just like web.config of the web application.

<<Central Administration web.config file on App Server>>

If you go back to the application server and open up the web.config file for the Central Admin site, you will see there are no changes made there. So we will make that change manually. Create a backup of the file then open up the file and find <Machine. It should look like this:


We will add the following (copied from web.config file of web application or the code from FBAConfigMgr)

1. Search for <machineKey and paste the following under <rolemanager><providers>
<add connectionStringName="fbaSQL" applicationName="/" name="SQL_Role" type="System.Web.Security.SqlRoleProvider, System.Web,&#xD;&#xA; Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

2. Under <membership><providers> paste the following
<add connectionStringName="fbaSQL" applicationName="/" name="SQL_Membership" type="System.Web.Security.SqlMembershipProvider, System.Web,&#xD;&#xA; Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
The screen should now look like this:

3. Scroll to the end of the document and paste the following right before </configuration>

<add name="fbaSQL" connectionString="server=SQL01;database=Extranet_User_DB;Trusted_Connection=true" />



<<STS web.config file on App Server>>

Just like the Central Admin web.config make the same changes on this web.config as well. Just make sure you are pasting the information from RoleManager Providers and Membership Providers in the right place. Here is what the code looks like (you can use the code below are make changes to the highlighted areas to suit your environment):




<add connectionStringName="fbaSQL" applicationName="/" name="SQL_Membership" type="System.Web.Security.SqlMembershipProvider, System.Web,&#xD;&#xA; Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />





<add connectionStringName="fbaSQL" applicationName="/" name="SQL_Role" type="System.Web.Security.SqlRoleProvider, System.Web,&#xD;&#xA; Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />





<add name="fbaSQL" connectionString="server=SQL01;database= Extranet_User_DB;Trusted_Connection=true" />


Here is a screenshot


Step 5 - Use FBA Pack to add and manage users

Our configurations are done. We will now go to our site collection and use the FBA Pack to add / manage users and Roles

Go to Site Settings and click on FBA User Management >> Click New User and create a dummy user and add him to the contributor group


Step 6 – Verify Forms user

Now open up IE in InPrivate mode and visit your site collection and this time choose Forms Authentication and enter the account information you just created to log in. You’re done!


Click on the user name and My Settings, you will see the account information coming from SQL Membership Provider


If you go to a document library and try and add the user there, you will see it resolves from your SQL database



How to create SQL Alias for SharePoint

Follow the steps below to create a SQL Alias on all your SharePoint Servers:

TechNet Reference:

1. Perform this on the Application Server that is hosting Central Administration

a. Stop all SharePoint Services

b. Open CLICONFIG.exe from C:\Windows\System32\cliconfg.exe (64 bit version of cliconfig.exe)

c. Enable TCP/IP under general tab

d. Click on Alias Tab

e. Type Current SQL Server Name in the Alias Name field

f. Type Current SQL Server Name in the Server field (see screenshot below. In your case SQL Alias and SQL Server name is the same)

g. Validate SQL Alias

i. Create a new text file on SharePoint Server and name it “TestDBConnection.udl”

ii. Double click to open the file and enter your SQL Server Alias name

iii. Use Windows Integrated Security

iv. You should be able to see all your SharePoint databases when you click on “Select the database on the Server”

h. Start all services for SharePoint Server / Reboot SharePoint Server

i. Perform the steps above on all other SharePoint servers

How to backup web.config file

To back up web.config file, perform the following:

· From IIS Manager (start >> Run > inetmgr)

· Right click on the web site and click Explore

· Copy the web.config file somewhere else, or the in the same location with a different name


Where is the STS web.config file?

· On your WFE open up IIS Manager and expand SharePoint Web Services

· Right click on SecurityTockenServiceApplication and click Explore


Comments (42)
  1. Durval Ramos says:

    Good job. Thanks for sharing

  2. Nice post, i am able to configure FBA following this. Thanks

  3. Anonymous says:

    Reflecting back on 2013, I just dived (dove?) into the logs for the PTS Blog for 2013, and thought I

  4. Shawn Miller says:

    Great walkthrough! I for some reason also had to add the app pool user (windows service account) to SQL Server’s Logins (under Security) and grant the user db_owner membership under the User Mappings of the user to get the FBA Pack to work right so I could add in new users.

  5. tom says:

    Still cant get it working right… I am able to manage users in iis 7 via a "blank website" running on appPool 2.0 so I can manage users.. I have FBAPACK installed and deployed globally. When in Site Settinges > Manage Forms Based Authentication Users:
    I still have the error "membership provider has not been config correctly.. all my configs are setup correctly, and are able to be read via iis "feature view". Not sure what else is left..? All accts are same for install, SQL, and web app. Please help..

  6. Muzaffar says:

    I am getting an error ‘A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.’

    I did double check but couldn’t find any difference.

    Please help.

  7. KENT says:

    THANKS SO MUCH! I have been trying to do this for over a week – your blog is the only one that got me through to completion.

  8. Jason says:

    I am having this same issue with the ‘A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.’ error when I click on FBA User Mgmt or FBA Role Mgmt under site settings. Everything else seems to work correctly including FBA authentication itself.

  9. Amjad Majeed says:

    Please check that database user has correct “User Mapping”, it seems it is due to mapping.

  10. Amjad Majeed says:

    Please check that database user has correct “User Mapping”, it seems it is due to mapping.

  11. Kunu says:

    Under Step 3, "User name = choose ", do I get yourAppPoolAccountName from my network admin? Sorry, but I’m very new to this.

  12. Sniper says:

    I’m having the following error <

  13. Sniper says:

    I’m having the following error: could not load file or assembly ‘FBAConfigWriter ……
    when i click Apply Config in FBA Configuration Manager

  14. Afzal says:

    What is the benefit of having FBA running on SharePoint site if FB user can’t execute the workflows?

  15. jorge says:

    Hey !! i have The resource object with key ‘UserMgmt_Title’ was not found. ……….because

    1. Aravinda Rao says:

      Did you find the fix for this error? I am facing the same issue

  16. jorge says:

    Hey !! i have The resource object with key ‘UserMgmt_Title’ was not found. ……….because

  17. aaron says:

    I’ve looked for hours for a post such as this that covers how to configure fba from scratch. Excellent post!

  18. Srinivas says:

    I am getting the following error , please help
    A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.

  19. azeesdinu says:

    Hi All, Please share your thoughts for this.. We are working with Sharepoint 2013 public website with Form based authentication. Anyone can register and start using our site. We are storing all the user related data in Active directory. As of now we are
    having around 25000 users in our portal . We are planning to move all external user information from AD to SQL membership table. Could you please suggest me to benefit of using SQL membership instead AD

  20. Kenny Bright says:

    For those having this problem:
    ‘A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.’

    In sql server rant the appPool user membership role as well.

  21. Parjinder Singh says:


    I am getting the following error:-
    ‘A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.’

    All my web config files are correct as I used the FBAConfigMgr.exe file to change the web config’s.

  22. Parjinder Singh says:

    Please help how to resolve this issue


  23. Eric Mao says:

    I had same problem and solved by matching the account of STS and the new web app you just created in application pool . And map the user to db_owner of the database you just created in database.

  24. webconfig file says:

    Hi Please share webconfig / WSP file

  25. Marcel Balcarek says:


    Maybe you need to use the version of the membership provider for SharePoint 2013.

  26. Denis says:

    Success! Thank you very much for this excellent walk through!

  27. abdul says:

    I am working with SharePoint 2013 public facing site. As of now we are using AD to store all the users record. I proposed to user information in to save SQL membership table. We have around 30 thousand users in our portal. Could you please confirm SQL
    membership will be useful for when compare with AD

  28. moud says:

    Actually , i have no access via FBA User management , it shows up error

  29. Ben says:

    Error: ‘A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.’

    Solution: Adding db_owner role on Extranet_User_DB for the user I configure access for.

  30. Selvakumar says:

    Excellent documentation, easy to follow and it works all the time

  31. Shafaqat says:

    I have enabled only FBA while extending the web application not showing the default login form, it is still authenticating using windows authentication.

  32. sudhanshu says:

    very nice detailed article…

  33. Baso says:

    Hi Sean,
    Thank you for the detailed article that helps make confiuring FBA very simple, and I have applied the same on my webApplication in SPFarm 2013, so everything goes well, but I faced a problem to access provider-hosted App using the FBA and get the clientcontextwithaccesstoken()
    that is necessary access the webapp or host app.
    unlike running SharePoint-hosted App that works smoothly with FBA credentials…
    can you suggest any solution to that.
    N.B: the AD users are able to access the same provider-hosted app with their credentials 🙂
    thanks in adnavce

  34. lilbiggy24 says:

    However, when I go into User Management, and I click on a user to reset password, I am getting an error that

    ‘Your current membershipprovider settings prevent a user’s password from being reset. To allow for resetting of a password by an administrator, you must have enablePasswordReset="true" and requiresQuestionAndAnswer="false" in your membership provider settings
    in your web.config’

    I used the FBA configuration Manager to update the web.config files, like I stated above.

    But to enable the password reset, what are the steps do I take to enable this?

  35. wie says:

    If anyone is still getting this message: ‘A Membership Provider has not been configured correctly. Check the web.config setttings for this web application.’ when trying to access the FBA User Management.

    Try granting db_owner role to the FBA database for the web app’s app pool account

  36. William Dias says:

    when i try and change password from the change password webpart it prompts that minimum characters should be 7 and should contain atleast 1 alpha numeric character. how do i change these settings

  37. PatRick says:

    Very great tutorial! Thanks a lot.
    I only have one problem left. Everytime I like to login via FBA (by choosing Form based.. on the login page, I get forwarded to _forms/default.aspx, which does not exist and I get a 404.

    Does anyone know, which one I have to choose?

    Thanks a lot

  38. Rahul_Dagar says:

    Great walkthrough

  39. Test says:

    Nice Article

    1. null12345 says:

      I’m having the same issue that others report where I get this message: “A Membership Provider has not been configured correctly. Check the web.config settings for this web application.” when trying to access the FBA User Management in my site collection site settings page. I’ve tried setting my app pool account as db_owner on the newly created membership database and have ensured that the appropriate role and membership provider settings have been applied to all of my web.config files (wfe, central admin, sts). One thing I did notice was that the assembly version for System.Web.Security.SqlRoleProvider in the above examples is 2.0.3600.0 whereas the version in my machine.config file is Changing the version numbers in my web.config files and running IIS reset did not remove the FBA User Management error message.

      Any help here is appreciated!

      web.config member and rolemanager settings:

      1. null12345 says:

        Please ignore/delete my last comment. I got everything working…issue was between my keyboard and chair. Thanks for the detailed post!

Comments are closed.

Skip to main content