Configuring Forms Based Authentication in SharePoint 2013 – Part 1 – Creating the Membership Database

Configuring forms based authentication (FBA) in SharePoint 2013 is very similar to SharePoint 2010, but there are some differences due to SharePoint 2013 using .Net 4.0. The web.config entries entries are slightly different. As well, IIS doesn’t support editing .Net 4.0 membership provider configuration through the IIS interface, so all of the configuration has to be done directly in the .config files. I’ll go through all of the steps required to setup FBA for SharePoint 2013, from start to finish.  I’ve broken down the steps into 4 sections, so if you already have an existing membership database setup from a previous version of SharePoint, feel free to skip forward to Part 3.

Part 1 – Creating the Membership Database

Part 2 – Adding Users to the Membership Database

Part 3 – Editing the Web.Config Files

Part 4 –  Configuring SharePoint

Part 1 – Creating the Membership Database

The first thing you need when configuring FBA for SharePoint is a place to keep all of the usernames and passwords. ASP.Net comes with a tool that we’ll use to create a membership database to store the logon information.

  • Navigate to c:\windows\Microsoft.NET\Framework64\v4.0.30319\
  • Run “aspnet_regsql.exe”sharepoint_2013_fba_1
  • A welcome screen will appear. Click Next.sharepoint_2013_fba_2
  • Select “Configure SQL Server for application services” and click Next.sharepoint_2013_fba_3
  • Enter the name of your server and your authentication information.  In this case SQL Server is installed on the same server as SharePoint 2013 and I am logged in as an administrator and have full access to SQL Server, so I choose Windows Authentication.For the database name, I just leave it as <default>, which creates a database called “aspnetdb”.sharepoint_2013_fba_4
  • A Confirm Your Settings screen will appear. Click Next.sharepoint_2013_fba_5
  • A “database has been created or modified” screen will appear. Click finish and the wizard will close.sharepoint_2013_fba_6
  • Now that the database has been created, we’ll have to give SharePoint permissions to read and write to it. We’re going to connect to the database with Windows Authentication, so we’re going to have to give those permissions to the service account that is being used to run SharePoint.First, let’s find out the service account that’s being used to run SharePoint. Open IIS, go to “Application Pools”. Take a look at the “Identity” that is being used to run the SharePoint application pools. On my test server, it happens to be my administrator account that is being used, but it will probably be different on your machine. Make note of the identity used.sharepoint_2013_fba_7
  • Now that we know what account is being used to run SharePoint, we can assign it the appropriate permissions to the membership database we created.  Open up SQL Server Management Studio and log in as an administrator.sharepoint_2013_fba_8
  • Under Security/Logins find the user that SharePoint runs as.  Assuming this is the same database server that SharePoint was installed on, the user should already exist.Right click on the user and click ‘Properties’.sharepoint_2013_fba_9
  • Go to the “User Mapping” Page. Check the “Map” checkbox for the aspnetdb database. With the aspnetdb database selected, check the “db_owner” role membership and click OK. This user should now have full permissions to read and write to the aspnetdb membership database.sharepoint_2013_fba_10

Continue to Part 2 – Adding Users to the Membership Database.

25 Responses to “Configuring Forms Based Authentication in SharePoint 2013 – Part 1 – Creating the Membership Database”

  1. tony says:

    why do people always say “from start to finish” and leave out the most critical component like what servers these things need to happen on.

    • Gero says:

      I agree. SharePoint has its own DB which I never use for anything and that’s where I would want the FBA installed since that is also where the “Identity” is already configured.

      • The aspnetdb membership database can be installed on any SQL Server that your SharePoint environment has access to, including the SQL Server that hosts your SharePoint databases.

        Note that you should create a separate database for the membership installation, and not use an existing SharePoint database. Modifying your SharePoint databases directly is not supported by Microsoft.

        • dries says:

          Under Security/Logins find the user that SharePoint runs as. Assuming this is the same database server that SharePoint was installed on, the user should already exist.Right click on the user and click ‘Properties’.

          –> What if the user does not exist? it said a different user. And when I try using local to connect to the database it doesn’t work, so I tried .\Sharepoint to access it like I usually did to connect to my sharepoint database.

          • Right – although my SQL Server installation is just at (local), you do have to point it to wherever the SQL Server you’re going to use resides.

            If you’re installing aspnetdb on the same SQL Server as SharePoint, I really would expect the app pool user to exist in it (Otherwise, what is SharePoint using to connect with?). If it’s a separate database though, you will have to add the app pool user using the ‘New Login…’ menu item.

  2. Gero says:

    Thanks for answering Chris. SharePoint actually creates its own “Server” and stores multiple Data Bases in there to use for Central Admin, and any other Web Applications so you wouldn’t be modifying any of the SharePoint databases but just placing a new one on the same server. Just the way I see it. But still your instructions are the best I’v seen anywhere and they are great help, just adding my 2 cents.

  3. Felix Zhang says:

    Hi,
    I configured in SP 2013 and in Site settings I can get the users in aspnetdb by peoplepicker, also I can get the users I added information from website, but when I use this user name to login, it failed.
    By SQL Server Profiler, when I add the user, I found it will record the request from website(search users like “test%” with SQL). But when I login, there is no record in SQL Server Profiler, the login function did not search users information from aspnetdb?
    I do not find what happen, do you have any ideas?
    Thanks

  4. Aravindh says:

    We have an asp.net web application which maintains a table with user information, passwords and roles. I am trying to import this information to a Membership database and ultimately use them for Form Based Authentication in a Sharepoint 2013 web application. I also noticed that the Membership database which I created does not store passwords. At this point I am a bit confused as to how to proceed with creating a FBA for my sharepoint site using the same member credentials and roles from my existing table. I am a newbie and any suggestions would be appreciated.

    • It does actually store passwords. You probably have it configured for ‘hashed’ – so you can’t actually see the passwords in the table – only a 1 way hash of the passwords. If you set the passwordFormat to Clear, the passwords will be in plain text within the db.

      That being said, for security I do suggest you use Hashed. Do a search on google on how to hash your existing passwords so they can be put in the db in the correct format. But if you want to get it up and running quickly, use Clear and then Hash them in the future once you’ve got everything working.

  5. tom says:

    Im running SP server 2013 with FBA enabled on web app. I have been trying for 3 days to get FBA working according to this blog and a few similar ones. I have yet to get this working correctly. I have edited the machine.config and the STS web.config.. tried to edit all the web.configs the old school way, installed and deployed the fbapack which only gives me errors telling me the membership provider is not configured correctly when I know it is all correct. I even removed all the whitespace in the config files to be sure. I am running win server 2008 r2 sp1 and SQL Server 2008 R2. What is it that I am missing??? I am at the end of the rope with this..

    • tom says:

      BTW I am using an spInstaller acct for the web app and SQL Server,DB login. It is the same as my App Pool running SharePoint -80.

      • My guess would be permission issues with SQL Server, but it sounds like you’ve got everything using the same account. Are you sure that you gave spInstaller permissions on the aspnetdb database?

        Can you add users to the database using IIS as described in step 2?

        If you really can’t get FBA setup, we do offer support/consulting services where we can take a look at your system using TeamViewer and get you sorted out. See here:

        http://www.visigo.com/purchase.html

  6. vishal goyal says:

    How to create Membership Database With Powershell?

  7. Fiqa says:

    Hello,

    Thank you so much for this very useful tutorial.
    I have successful configure FBA for my site.

    But i have problem opening the site in Sharepoint designer.
    anyone face same problem with me?

  8. Daniel Collier says:

    Hi Chris,

    Thanks for this. It’s been invaluable.

    I’m now looking to provide similar functionality to the FBA pack on the server (adding users, reset/change password etc), but via my client side .NET application which is using the SharePoint Client Side Object Model. I’m a bit of a newbie at this and slowly finding my way.

    Would you be able to point me at code that does this sort of thing, or particular tech that I should be investigating?

    Thanks

Leave a Response

Current day month ye@r *

Trackbacks

  1. [...] I’m trying to setup an external portal for our SharePoint 2013 site on server 2012 so external suppliers can access content. I’ve gotten FBA setup with the following guide: http://blogs.visigo.com/chriscoulson/configuring-forms-based-authentication-in-sharepoint-2013-part-… [...]