SharePoint Form-Based Authentication with SQL using PowerShell

I have a lot of requests from clients lately who want to use custom authentication for their users against a custom SQL database. In SharePoint, form-based authentication allows you to redirect users to a login page, asking them for their username and password before authenticating them against your SharePoint farm. Users are prompted to provide their username (be it whatever you decide), and their account’s password. It doesn’t authenticate against Active Directory, and therefore allows external users to authenticate against their environments. While configuring Form-Based authentication for SharePoint may sound complex, it is actually a fairly straight forward process. Throughout this article, I will guide you to the process of automating the process of configuring a SharePoint 2013 environment for Form-Based authenticaton using PowerShell.

1 – Create the Custom SQL Authentication Database
Our first step is to get a new custom SQL database created to store our user’s information. The various user’s account will be stored within it, and SharePoint will use custom Membership and Role providers to authenticate. We could always go ad manually create and configure this SQL database along with all the proper tables and permissions, but luckily for us the .NET framework provide a utility to take care of that automatically. Within the .NET Framework installation folder is an executable named aspnet_regsql.exe. Using PowerShell, you can determine the location of this utility by using the following lines of code. To code provided will also ensure the executable is prsent, and will throw an error if it is not.


$dotNetInstallationPath = [System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory()
$executableName = "aspnet_regsql.exe"
$fullPath = $dotNetInstallationPath + $executableName
if(!(Test-Path $fullPath))
{
throw [System.IO.FileNotFoundException] "Executable not found."
}


Now that we have obtain the path to our executable, we will use PowerShell to automate its execution. If you were to execute the Graphical Interface of the executable, it would ask you the name of the SQL server on which you wish to create you SQL database. In my case, the name of my Virtual Environment is WIN-6O74I1M7M69 (but I will be using ‘.’ to indicate a local instance. It will also ask you how you wish to connect against your database (windows auth or SQL auth). In my case, the account with which I will be executing the PowerShell script has Full Access to my local SQL server, therefore I will be using Windows Authentication to connect to the SQL server. The executable will require us to pass it four parameters: ‘-S’ which is the name of the SQL server, ‘-A all’ to specify that we want all features to be added to our database, ‘-E’ which specifies that we want to use Windows Authentication to connect to it, and ‘-Q’ to specify that we want to run in Quiet mode. If the -Q parameter is not passed, the Graphical User Interface will be displayed, and you will be forced to click on the various “Next” button to complete the process. Because we wish to create an unattended automated configuration script, the -Q parameter is required. The following lines of PowerShell will be used to automate the creation of our Authentication SQL database.


Start-Process $fullPath -ArgumentList '-S "."', '-A all', '-E', '-Q'


While the database is getting created on your SQL server, the following prompt will display:

The overall creation process should take less than 30 seconds. Once it completes, you can navigate to your SQL server using the SQL Server Management Studio (SSMS). Take a look at the new database that just got created.

Once you confirm the database was successfully created, we are ready to move on to the next step of our confirmation.

2 – Configure the Membership Provider
Now that our database has been created, we are ready to have it create the foundation that will allow us to populate some user accounts. Unfortunately for us, SharePoint 2013 doesn’t let us edit users directly from within IIS for Web Applications that are controlled by it. What we will do here is actually cheat the system. We will work with IIS to create a “bogus” Web Application and will use it to register our users against it (and down into our membership table within our database).

Our first step is to create anew Application Pool in IIS that will be used to run our Membership Web Application. In my case, the new application pool will be called “MembershipWebApp”, and it will run under my administrator’s credentials. Note that for best practice you should not use the administrator account. In my case I am only using it to simplify the content of this article and give you an 10,000 feet overview of how Form-Based authentication works. The following lines of PowerShell will be used to create our application pool:


Import-Module WebAdministration
$appPool = New-Item IIS:\AppPools\MembershipWebApp
$appPool.ProcessModel.UserName = "contoso\administrator"
$appPool.ProcessModel.Password = "pass@word1"
$appPool.ProcessModel.IdentityType = "SpecificUser"
$appPool | Set-Item


We now need to go an create a new IIS Web Site. In my case, I will create the web application under C:\inetpub\wwwroot\MembershipWebApp and will have it run on port 93. Using PowerShell, this can be achieved with the following lines of code:


$physicalPath = "C:\inetpub\wwwroot\MembershipWebApp"
New-Item -Type "Directory" -Path $physicalPath
New-Website -Name "MembershipWebApp" -Port 93 -PhysicalPath $physicalPath -ApplicationPool "MembershipWebApp"


Upon completing its execution, you can open up IIS to verify that our new Web Site has been properly created.

Now that the piping for our new Membership Web Application has been done in IIS, we need to go and connect it to our SQL database. In order to do this, we will need to declare a new Connection String within our Web Application. In the back-end what will really happen is that an entry for our connection string will be made in our Web Application’s web.config. The recommendation, if you wish to keep things simple, is to simply add the values in the machine.config and not add them at the Web Application level, but to keep things clear, secure, and to follow best practices, we will add them only to what is required. In order to achieve this, run the following PowerShell lines of code:


Add-WebConfiguration -filter '/connectionStrings' -PSPath IIS:\sites\MembershipWebApp -value @{Name="FormBasedAuth"; ConnectionString="Server=WIN-6O74I1M7M69;Database=aspnetdb;Integrated Security=true";}


The next step is to go an actually create our Membership provider, which will be used to access user’s information from our SQL database. Our custom Membership provider will be leveraging the connection string we just created to connect to our database. In order to create the Membership provider with PowerShell, you’ll need to run the following lines of code:


Add-WebConfiguration -filter '/system.web/membership/providers' -PSPath IIS:\sites\MembershipWebApp -value @{Name="FBAMembershipProvider"; Type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

$xml = (get-content "C:\inetpub\wwwroot\MembershipWebApp\web.config")
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//system.web/membership")
$node.SetAttribute("defaultProvider", "FBAMembershipProvider")
$node = $root.SelectSingleNode("//system.web/membership/providers/add[@name='FBAMembershipProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$xml.Save("C:\inetpub\wwwroot\MembershipWebApp\web.config")


To confirm that the provider has been properly created, open IIS, navigate to your Web Application, in the “Features View” panel click on the Providers icon, change the feature drop down to .NET Users, and ensure that our custom provider (FBAMembership) is listed.

The last thing required is to create a custom Role Provider for our SharePoint environment. This will allow us to specify SharePoint roles and assign them to our custom users. This can be acheived in a way that is very similar to how we created the Membership Provider with PowerShell:

Add-WebConfiguration -filter '/system.web/roleManager/providers' -PSPath IIS:\sites\MembershipWebApp -value @{Name="FBARoleProvider"; Type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

$xml = (get-content "C:\inetpub\wwwroot\MembershipWebApp\web.config")
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//system.web/roleManager/providers/add[@name='FBARoleProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$xml.Save("C:\inetpub\wwwroot\MembershipWebApp\web.config")


3 – Configure a SharePoint Web Application for Form-Based Authentication
We are now getting to the meat of this article: how can we configure SharePoint to leverage our custom Membership Provider to authenticate our users? The first thing we will need to take care of is to ensure our SharePoint environment is able to connect to our SQL back-end. In order to make this happen, we will need to add a configuration entry for our Connection String in every SharePoint web application that requires access to our custom Membership Provider. By default, you’ll need to add your connection string information to both the Central Administration and the Security Token Service web.config files. On top of this, you’ll also need to add it to the web.config file of every web application in your environment that will be leveraging our custom Membership Provider. To simplify things for this article, I will simply modify the machine.config file and add the required information in it. By default, this will make my values available to all other Web Applications and services on the server.

Let us start by creating a new SharePoint we application and have it configured to use our Membership Provider. In my case, the Web Application wil be created on port 83. The following lines of PowerShell will take care of configuring it accordingly:


Add-PSSnapin Microsoft.SharePoint.PowerShell
$customProvider = New-SPAuthenticationProvider -ASPNETMembershipProvider "FBAMembershipProvider" -ASPNETRoleProviderName "FBARoleProvider"
$webApp = New-SPWebApplication -ApplicationPool "SharePoint - 83" -ApplicationPoolAccount "contoso\administrator" -Name "SharePoint - 83" -AuthenticationProvider $customProvider -Port 83


Now that our web application has been created, let’s automatically add the connection string information to our web.config files using PowerShell:

$sitesNames = @("SharePoint - 83", "SharePoint Central Administration v4")

foreach($site in $sitesNames)
{
$website = Get-WebSite $site
$psPath = "IIS:/sites/$site"

Add-WebConfiguration -filter "/connectionStrings" -PSPath $psPath -value @{Name="FormBasedAuth"; ConnectionString="Server=WIN-6O74I1M7M69;Database=aspnetdb;Integrated Security=true";}

Add-WebConfiguration -filter '/system.web/roleManager/providers' -PSPath $psPath -value @{Name="FBARoleProvider"; Type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

Add-WebConfiguration -filter '/system.web/membership/providers' -PSPath $psPath -value @{Name="FBAMembershipProvider"; Type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

$webConfigPath = $website.physicalPath + "\web.config"
$xml = (get-content $webConfigPath)
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//system.web/membership/providers/add[@name='FBAMembershipProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$node = $root.SelectSingleNode("//system.web/roleManager/providers/add[@name='FBARoleProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$xml.Save($webConfigPath)
}

# SecurityTokenService
$sts = Get-WebApplication "SecurityTokenServiceApplication"
$sts.physicalPath
$webConfigPath = $sts.physicalPath + "\web.config"
$xml = (get-content $webConfigPath)

$newElement = $xml.CreateElement('connectionStrings')
$newElement.InnerXML = ''
$xml.configuration.AppendChild($newElement) | Out-Null

$newElement = $xml.CreateElement('system.web')
$newElement.InnerXML = ''
$xml.configuration.AppendChild($newElement) | Out-Null

$xml.Save($webConfigPath)

And we are all set! The environment is now ready to authenticate users against our Form-Based SQL authentication Provider.

4 – Add Users
Now that all the ground work has been done, we need to add users to our authentication dataase. To do so, open IIS, navigate to our MembershipWebApp web application, and click on .NET Users.

From the right panel, click on Add…

A dialog will appear. Enter information for your user (in my case Nik).

Save your user.
5 – Create a Site Collection
You can now create a new site collection using your user’s credentials.

New-SPSite -Url "http://win-6o74i1m7m69:83" -OwnerAlias "i:0#.f|fbamembershipprovider|nik" -Template "STS#0"


That’s it! we are now ready to navigate to our site and authenticate via Form-Based authentication. Upon accessing your newly created site collection, you will be prompted with a login screen like the following.

Enter your user’s credentials (in my case Nik’s credentials), and you will be automatically authenticated against your SharePoint environment using a custom SQL database!

6 – Putting it all together
Here is the resulting script:

$dotNetInstallationPath = [System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory()
$executableName = "aspnet_regsql.exe"
$fullPath = $dotNetInstallationPath + $executableName
if(!(Test-Path $fullPath))
{
throw [System.IO.FileNotFoundException] "Executable not found."
}

Start-Process $fullPath -ArgumentList '-S "."', '-A all', '-E', '-Q'

Import-Module WebAdministration
$appPool = New-Item IIS:\AppPools\MembershipWebApp
$appPool.ProcessModel.UserName = "contoso\administrator"
$appPool.ProcessModel.Password = "pass@word1"
$appPool.ProcessModel.IdentityType = "SpecificUser"
$appPool | Set-Item

$physicalPath = "C:\inetpub\wwwroot\MembershipWebApp"
New-Item -Type "Directory" -Path $physicalPath
New-Website -Name "MembershipWebApp" -Port 93 -PhysicalPath $physicalPath -ApplicationPool "MembershipWebApp"

Add-WebConfiguration -filter '/connectionStrings' -PSPath IIS:\sites\MembershipWebApp -value @{Name="FormBasedAuth"; ConnectionString="Server=WIN-6O74I1M7M69;Database=aspnetdb;Integrated Security=true";}

Add-WebConfiguration -filter '/system.web/membership/providers' -PSPath IIS:\sites\MembershipWebApp -value @{Name="FBAMembershipProvider"; Type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

$xml = (get-content "C:\inetpub\wwwroot\MembershipWebApp\web.config")
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//system.web/membership")
$node.SetAttribute("defaultProvider", "FBAMembershipProvider")
$node = $root.SelectSingleNode("//system.web/membership/providers/add[@name='FBAMembershipProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$xml.Save("C:\inetpub\wwwroot\MembershipWebApp\web.config")

Add-WebConfiguration -filter ‘/system.web/roleManager/providers’ -PSPath IIS:\sites\MembershipWebApp -value @{Name=”FBARoleProvider”; Type=”System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”;}

$xml = (get-content "C:\inetpub\wwwroot\MembershipWebApp\web.config")
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//system.web/roleManager/providers/add[@name='FBARoleProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$xml.Save("C:\inetpub\wwwroot\MembershipWebApp\web.config")

Add-PSSnapin Microsoft.SharePoint.PowerShell
$customProvider = New-SPAuthenticationProvider -ASPNETMembershipProvider "FBAMembershipProvider" -ASPNETRoleProviderName "FBARoleProvider"
$webApp = New-SPWebApplication -ApplicationPool "SharePoint - 83" -ApplicationPoolAccount "contoso\administrator" -Name "SharePoint - 83" -AuthenticationProvider $customProvider -Port 83

$sitesNames = @("SharePoint - 83", "SharePoint Central Administration v4")

foreach($site in $sitesNames)
{
$website = Get-WebSite $site
$psPath = "IIS:/sites/$site"

Add-WebConfiguration -filter "/connectionStrings" -PSPath $psPath -value @{Name="FormBasedAuth"; ConnectionString="Server=WIN-6O74I1M7M69;Database=aspnetdb;Integrated Security=true";}

Add-WebConfiguration -filter '/system.web/roleManager/providers' -PSPath $psPath -value @{Name="FBARoleProvider"; Type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

Add-WebConfiguration -filter '/system.web/membership/providers' -PSPath $psPath -value @{Name="FBAMembershipProvider"; Type="System.Web.Security.SqlMembershipProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a";}

$webConfigPath = $website.physicalPath + "\web.config"
$xml = (get-content $webConfigPath)
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//system.web/membership/providers/add[@name='FBAMembershipProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$node = $root.SelectSingleNode("//system.web/roleManager/providers/add[@name='FBARoleProvider']")
$node.SetAttribute("connectionStringName", "FormBasedAuth")
$xml.Save($webConfigPath)
}

# SecurityTokenService
$sts = Get-WebApplication "SecurityTokenServiceApplication"
$webConfigPath = $sts.physicalPath + "\web.config"
$xml = (get-content $webConfigPath)

$newElement = $xml.CreateElement('connectionStrings')
$newElement.InnerXML = ''
$xml.configuration.AppendChild($newElement) | Out-Null

$newElement = $xml.CreateElement('connectionStrings')
$newElement.InnerXML = ''
$xml.configuration.AppendChild($newElement) | Out-Null

$newElement = $xml.CreateElement('system.web')
$newElement.InnerXML = ''
$xml.configuration.AppendChild($newElement) | Out-Null

$xml.Save($webConfigPath)