Configuring Forms Based Authentication in SharePoint 2016, SharePoint 2019 and SharePoint Subscription Edition – Part 1 – Creating the Membership Database

Configuring forms based authentication (FBA) in SharePoint 2016, SharePoint 2019 and SharePoint Subscription Edition is exactly the same process as configuring it for SharePoint 2013.  I’ve recreated the SharePoint 2013 FBA tutorial specifically for SharePoint 2016, SharePoint 2019 and SharePoint Subscription Edition, using screenshots from SharePoint 2016 and Windows Server 2012 R2.  I have changed the tutorial to use the SharePoint FBA Pack to create the FBA users, but otherwise it remains the same and can be used interchangeably between SharePoint 2013 and SharePoint 2016/2019/Subscription Edition.

I’ll go through all of the steps required to setup FBA for SharePoint 2016, 2019 and Subscription Edition, 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 2.

Part 1 – Creating the Membership Database

Part 2 – Editing the Web.Config Files

Part 3 –  Configuring SharePoint

Part 4 – Adding Users to the Membership Database

You can also watch a video of the whole process on YouTube: Configuring Forms Based Authentication in SharePoint 2016 and SharePoint 2019.

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”aspnet_regsql.exe file
  • A welcome screen will appear. Click Next.aspnet_regsql wizard welcome
  • Select “Configure SQL Server for application services” and click Next.aspnet_regsql wizard task
  • Enter the name of your server and your authentication information.  In this case SQL Server is installed on the same server as SharePoint 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”.aspnet_regsql wizard select db
  • A Confirm Your Settings screen will appear. Click Next.aspnet_regsql wizard confirm
  • A “database has been created or modified” screen will appear. Click finish and the wizard will close.aspnet_regsql wizard finish
  • 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.IIS Application Pool
  • 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.SQL Server Management Studio
  • 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’.SQL Server Select Login
  • 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.SQL Server Edit User Mapping

Continue to Part 2 – Editing the Web.Config Files.

Comments

18 responses to “Configuring Forms Based Authentication in SharePoint 2016, SharePoint 2019 and SharePoint Subscription Edition – Part 1 – Creating the Membership Database”

  1. Scott Avatar
    Scott

    Is there a setting somewhere we can change that will allow the creation of multiple FBA accounts using the same email address? It looks like that worked in SP 2010 but not for SP 2016.

    1. Chris Coulson Avatar

      Yeah – it’s actually a setting in the config file – ‘requiresUniqueEmail’. See part 2.

      1. Scott Avatar
        Scott

        Appreciate it!!!

  2. vas Avatar
    vas

    Hi Chris,
    I followed your instructions, and the FBA is up and running for almost 3 years now for SP 2016, thank you for your contributions!
    Now, we are planning to migrate to SP 2016, do i need to follow the steps again to make changes in config files and SQL or the sites should adapt to SP 2016?

    1. Chris Coulson Avatar

      If you’re upgrading in place on the same server then you should be good – though you’ll want to check that the securetokenservice config isn’t overwritten. If you’re setting up a new server, then you’ll at least have to make the .config file changes to it. The 2013 and 2016 setup are identical, so no need to create a new database, you can just reuse the existing.

      1. Amardeep Avatar
        Amardeep

        Hello,

        I have confgiured FBA with AD Auth and able to login to share point with FBA.
        But I can allow only one OU in all web.config. I have multiple OU and I want all those access FBA.

        Please help how to add multiple OU.

        Role Manager-

        1. Bikram Avatar
          Bikram

          Hey Amardeep, how did you use FBA with AD. Did you make any additional database for that? thanks

  3. Lon Ramsey Avatar
    Lon Ramsey

    We transferred the database from SP2010 to SP2016, but the user’s Full Name appears to have been lost. Where is the Full Name stored?

    Thank you,
    Lon

    1. Chris Coulson Avatar

      The full name is stored in the user’s user profile in the SharePoint site collection. If you upgrade an existing site collection from SP2010 to SP2016, these user profiles should upgrade along with it (though any new site collection will not have the user profile information).

      For an existing site collection, one possibility could be that when the user is logging in they are being identified as a new user instead of an existing user, so are getting a new user profile. SharePoint’s full internal username is a combination of the membership provider name + the username – so if the name of the membership provider changed between 2010 and 2016, all users will be treated as new users until you change the membership provider name back to what it was in 2010 (you can also perform a user ‘migration’ to change the internal name of the users to match the new membership provider name – but it’s much easier just to change the membership provider name).

  4. Alex Avatar

    Hi,
    We are migrating from SP 2013 on a domain to 2019 domainless, whilst trying to use Azure SQL MI. SharePoint 2019 is working using Azure SQL MI, however I cannot get forms authentication working. After performing the install i then change the webconfig however then i get an page runtime error, I believe this is due to the webconfig connection string and SQL authentication (as there is no windows authentication with Azure SQL MI).
    Using the connection string from Azure would be:
    Server=tcp:example01.asddef38aw60e.database.windows.net,1433;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
    so example of string i am trying is:

    Do you know if this is correct or point me in right direction.

    1. Chris Coulson Avatar

      If you’re getting a page runtime error my guess is that there’s a typo somewhere in your config file – maybe not having a matching closing tag or missing quotations or something like that. Though it’s probably best to find out what the error actually is. I’d take a look at the windows event log, I expect it will give you a hint as to what is happening (In this case, I think the error will be in the windows event log, though potentially it is in the sharepoint log file instead).

      If you want to test your actual database connection string and membership config values, you can use IIS to test directly with it’s .Net Profiles, Users and Roles pages.

  5. Tim Avatar
    Tim

    Hi, is it possible to have FBA users create their own mysites?

Leave a Reply

Your email address will not be published. Required fields are marked *