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.

66 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

  9. PJ says:

    Can we use this pack with AD as the provider? The requirement is that we need users to authenticate to this site via a login page- hence the choice to use FBA. But we also want to have SharePoint admins approve account creations and allow users to reset their passwords.

    • I haven’t tried it myself, but several other users have reported that it does not work. The FBA Pack is built to work against any standard membership provider, so it likely doesn’t work because the AD providers haven’t fully implemented the membership provider interface.

      If you wanted to get it working with an AD provider, you’d likely have to extend the existing AD provider and code the functionality that is missing.

  10. Kev Houston says:

    Hi –

    Any ideas why I would get the following once selecting the ‘new user’ within the Manager Forms Based Authentication Users SC settings page?

    System.NullReferenceException: Object reference not set to an instance of an object. at Visigo.Sharepoint.FormsBasedAuthentication.UserNew.OnLoad

  11. Moraine Lake says:

    Hi,

    I have a doubt about FBA user Management.
    I’ve created portal community and I would like that every community had your own users created by FBA user management admin.
    I do not want that FBA user management had access to all users, to all communities as it is now.
    How can I solve it without having to create web application to each community. Thanks a lot.

    My infrastructure is:

    http://mywebapplication/portal
    http://mywebapplication/community1
    http://mywebapplication/community2
    http://mywebapplication/community3

    Moraine.

    • Unfortunately, I believe you’ll have to create a separate web application for each community – as SharePoint manages the available users at the web application level.

      You can definitely keep it under one web application and manage permissions so certain users only belong to certain sites – but when using the people picker or the FBA User Management pages, it will show all available users in the web application.

      You may be able to create a custom membership provider that filters what users it returns based on the current site – but I don’t know of anything out of the box that does this.

    • Tony says:

      Hi Morlaine,

      The easiest way is for you to create FBA ROLES/SharePoint Groups for each community. That would give you nice user seperation, and can be managed directly in sharepoint.

      Thanks,
      Tony.

  12. Tony says:

    Hey Chris,

    is it possible to detach/attach the aspnetdb database to a new server? If not, what migration approach would you recommend?

    I am migrating from DEV to UAT 2013 Server.

    thanks,
    Tony.

    • Yes, absolutely. There’s nothing “linking” the aspnetdb to a specific sharepoint server – it’s really just a list of usernames and passwords. You can copy the db and attach it to your new server, or you can even reference the existing database with the connection string from your UAT environment, if you want the two environments to share the same database.

      If you are also migrating Sharepoint user profiles, permissions and content (created by fields etc…), then make sure that you use the same Membership Provider name in your .config files – as that is used as part of the user id in SharePoint. (Otherwise you’ll be able to log in with the credentials in the aspnetdb, but they will create new user profiles and essentially be new users in SharePoint.)

      • PvM says:

        Hi Chris, I have 2013 and always on configured. If I fail over I get ” A Membership Provider has not been configured correctly. Check the web.config setting for this web application ” it looks like the config is pointing to one DB server. Is there a config file or settings file that I can check and edit to point to my listener?

        • If you want to edit the connection string for where to point the db server, it will be in the machine.config (if you are following these instructions to setup FBA). See part 2 for details.

  13. Rajashri Thorat says:

    Hi,

    I tried to Installing and Configuring the SharePoint 2013 FBA Pack but when I am adding the web part to the page it gives the error “A Membership Provider has not been configured correctly. Check the web.config settings for this web application. ” please guide me what steps i need to follow

    Thank you,

    • This means that there is some sort of issue with your FBA configuration on the SharePoint server. Essentially it can’t communicate with the membership database. A common issue is that the application pool user doesn’t have DBOwner permissions on the membership database, so you might want to check that. Otherwise, i’d review all of the steps in this guide to ensure that everything’s setup properly.

      • Rajashri Thorat says:

        Hi ,
        I had given the DBOwner permissions to the application pool user on the membership database also done the changes in web.config files still it shows the same message on page

        Thank you,

        • Then i’d go through and double check each step of the guide. You might want to also take a look at your SharePoint log files to see if there is an error in there that gives a clue as to what the issue could be.

  14. Kajal Chaudhari says:

    Hi Chris,

    Please guide I have double checked all the steps also I have checked the user in DB but I am not able to login with the user we have created in the step 2. It just dont accept the username and password but it allows me to login with Window auth.

    • What’s the error message you receive when you login? If it’s ‘incorrect username/password’, then there’s something wrong with your FBA configuration – i’d double check your SecurityTokenService web.config (Part 3 of the guide), as that’s the actual config file used for authenticating to SharePoint.

      If the error message you get is ‘Access Denied’ – then this means that you are actually authenticating correctly, but the user doesn’t have access to the site. If that’s the case, login with windows auth and add the user to a group with permissions to access the site.

  15. Mahi says:

    Hi, I have exactly configured the way you did it. However I get an error that Membership provider has not been configured correctly. Check the web.config settings for this web application error.(on Site Settings-Manage FBA Users

    • The message means that it can’t communicate with the membership database. So that could be a problem in your .config files or a permission problem. Usually it’s permissions. Make sure that the App Pool user for the SharePoint web application has dbo permissions on the membership database.

  16. Pete says:

    I was wondering if anyone had implemented this solution with SSL yet? I would appreciate any discussion or information where someone had done it. Thanks in advance!

    • I’ve done it many times. Using SSL is definitely the recommended way to do it, otherwise any passwords and other information is sent in clear text over the wire. All you have to do is change the web application in iis to use SSL, and then add an https alternate access mapping for the web application in Central Admin.

  17. vinod says:

    In “User Mapping” Page SharePoint boxes are not showing can you guide me how to display those boxes

    • The user mapping page should show all databases on the sql server you are connected to. So if SharePoint is installed onto the same sql server, they should show. SharePoint could be installed on a different sql server though. The only database you need to worry about is the aspnetdb database, which is the membership database. I happened to install sharepoint on the same sql server using the same user account, which is why they are checked as well.

  18. George says:

    Does fba work on sharepoint Foundation

  19. Vishwanath Mishra says:

    Awsome post, tons of thanks. I have followed step-by-step all parts 1-IV and able to implement FBA successfully in Sharepoint 2013 Foundation setup.

    Internal users are AD users and I am keeping guest users in SQL server database as suggested in this post.

    Now, my requirement is that I want, to give users(internal/external) a common
    login page instead of giving options to choose from “Windows Authentication” OR “Form Authentication”, Is it possible to handle it using Custom code? OR any OOTB feature exists? Please guide.

    • It is definitely possible to handle with custom code, I’ve created several similar solutions for my customers. What I usually do is take the provided SharePoint forms based login page as a starting point and use it to create a custom login page. I add a link to this forms login page titled “Windows Login” or something similar and link it to the built in windows login page. I also create Register and Forgot Password application pages and link to them from the custom login page as well. Then I just set the web application to use my custom login page.

  20. Vishwanath Mishra says:

    Is it possible to keep common login page for both FBA and Windows login?

  21. Andy Wijaya says:

    Hi Chris,

    I’am currently working with your SharePoint 2013 FBA Pack, which is amazing and works great. But is it possible if I hide the FBA configurations from my subsites and just show the configurations on a specific site?

    For example, if I have a site collection at http://mysite.com, and a subsite called http://mysite.com/subsite1, is it possible to hide the configurations from Site Settings of subsite1?

    Thanks.

    • Not out of the box. However they can only see if it they are a site collection administrator, and if you don’t want the user to see this i’d say it’s also unlikely that they are a site collection administrator.

Leave a Response

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-… […]

  2. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  3. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  4. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  5. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  6. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  7. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  8. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  9. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]

  10. […] 2016 is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, using screenshots from SharePoint 2016 and Windows Server 2012 […]