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:
aspnetregsql

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.
aspnetdb
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

appPool

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"

CreateWebSitePowershell

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

WebApp

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.
Membershipprovider

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

webappport83

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.
dotnetusersicon

From the right panel, click on Add…
AddUser

A dialog will appear. Enter information for your user (in my case Nik).
NewUser
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.
Signin

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!

nikconnected

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)

Create New Site from Custom Web Template in Office 365 (SharePoint Online)

This week I am working for a customer who wants to develop a new solution that will allow users to create new SharePoint Online sites, based on a custom web Template, with a single click. After struggling for a few hours trying to find the proper way of achieving this using the SharePoint add-in model, I came up with a very simple solution that allows a user to automate the creation of SharePoint Online sites based on a custom web Template, using calls to the REST API.

We are all familiar with the default out-of-the-box templates (ex: STS#0), but custom web templates are a little different. The first thing you need to know when dealing with custom web templates, is that every one of them are provided a custom ID based on the following naming convention: <GUID>#<Template Name>. For example, assume you were to create a new site template and give it a title of “MasterTemplate”, the given Name for your Template could end up being something like “{2AA91D04-377B-431A-8D23-7424893F5CEB}#MasterTemplate”. The first part of the ID (before the ‘#’) is what we will need to pass to the REST method responsible for creating our new web.

Solution Overview

The solution we will be studying here is made up of two components. The first one will help us retrieve the actual ID of our custom Web Template. The second will be used to actually create the new site, using the retrieved custom Template. All of this will be achieved using a SharePoint-Hosted Add-In and by making REST calls using JavaScript.

For the purpose of this article, I went ahead in SharePoint Online and created a new site, which I’ve modified a bit so that it can be re-used over and over as a Template. I’ve cleaned all web parts from the landing page, and created two custom lists: a task list named “Team Tasks”, and an issue list named “Team Issues to Track”.
NewSite

I then went ahead and saved this site as a Template. If you don’t see this option in your site settings, make sure you have Scripting enabled for the given site collection (more info at https://support.office.com/en-us/article/Turn-scripting-capabilities-on-or-off-1f2c515f-5d7e-448a-9fd7-835da935584f). I named my Template (Help Desk Case).
SolutionGallery

Now that our custom Web Template is created and registered in our SharePoint Online Site Collection, we need to figure out what its ID is. To achieve this, I went ahead, opened Visual Studio, and created a new SharePoint Add-In. We will be using this Add-In to retrieve the ID of all of our existing Site Templates (including the custom ones), and display them to our users in a drop down list. The idea here is to query tthe following REST endpoint:
/_api/web/getavailablewebtemplates(lcid=1033, doincludecrosslanguage=true)

In the add-in default.aspx page, I have created a new empty DIV element with an ID of “divMain”. This empty div will be used to dynamically generate our drop down list of values contining information about all the available Web Templates. What our JavaScript code will do, is query the host web to retrieve the list of all available Web Templates, loop through each of them and add it to our dynamically generated Drop Down list. The option items in our drop down list will display the Title of each Web Template, but will have a value representing their internal ID.

SiteTmpl

The code used in the App.js file for our add-in to retrieve that list is the following:


'use strict';

ExecuteOrDelayUntilScriptLoaded(initializePage, "sp.js");

function initializePage()
{
var hostweburl;
var appweburl;
var __REQUESTDIGEST;

// This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model
$(document).ready(function () {
hostweburl = decodeURIComponent($.getUrlVar("SPHostUrl"));
appweburl = decodeURIComponent($.getUrlVar("SPAppWebUrl"));
var scriptbase = hostweburl + "/_layouts/15/";

// load the executor script, once completed set the ready variable to true so that
$.getScript(scriptbase + "SP.Runtime.js",
function () {
$.getScript(scriptbase + "SP.js",
function () { $.getScript(scriptbase + "SP.RequestExecutor.js", getWebTemplates); }
);
}
);
});
function getWebTemplates()
{
var requestURL = appweburl + "/_api/SP.AppContextSite(@target)/web/getavailablewebtemplates(lcid=1033, doincludecrosslanguage=true)?@target='" + hostweburl + "'";
var executor = new SP.RequestExecutor(appweburl);

executor.executeAsync({
url: requestURL,
type: "GET",
headers: {
"accept": "application/json;odata=verbose"
},
success: function (data) {
var jsonObject = JSON.parse(data.body);
var results = jsonObject.d.results;
var s = $('<select id="ddlTemplate" />');
for(var i = 0; i < results.length; i++) { $('<option />', { value: results[i].Name, text: results[i].Title }).appendTo(s); } s.appendTo('#divMain'); }, error: function (xhr, status, error) { alert(JSON.stringify(xhr)); } }); } } jQuery.extend({ getUrlVars: function () { var vars = [], hash; var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&'); for (var i = 0; i < hashes.length; i++) { hash = hashes[i].split('='); vars.push(hash[0]); vars[hash[0]] = hash[1]; } return vars; }, getUrlVar: function (name) { return jQuery.getUrlVars()[name]; } });

Now that we managed to retrieve all site templates for our SharePoint Online Site Collection, we need to work on the piece of our Add-in's code that will actually go and create the site based on the web Template we've selected from our drop down list. To achieve this, we will modify the default.aspx page of our Add-in to include a text box allowing the users to enter a title for their new site, and a button to initiate the site's creation. The default.aspx code for my solution looks like the following:


...
<asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">

<strong>Title: </strong><input type="text" id="siteTitle" /><br />
<strong>Site Template: </strong>
<div id="divMain">

</div>
<input type="button" id="btnCreate" value ="Create Site" onclick="createSite" />

</asp:Content>
...

Now that the visuals are in place, we actually need to connect our button to the action that will create the new site. Based on the .NET markup above, we can see that my button is trying to call a JavaScript function named "createSite". One very important thing: When calling the REST API to initiate the creation of the new site, you should only pass the associated Web Template ID's prefix (what is before the '#' sign). For example, in my case, the ID of my Help Desk Case web template is In order to have the onClick event trigger, we need to add the following logic in our App.js file:


function createSite() {
var requestURL = appweburl + "/_api/SP.AppContextSite(@target)/web/webinfos/Add?@target='" + hostweburl + "'";
var siteTitle = $('#siteTitle').val();
var siteUrl = $('#siteTitle').val().replace(" ", "");
var templateID = $("#ddlTemplate").val().split('#')[0];
var jsonData = "{ 'parameters': { '__metadata': { 'type': 'SP.WebInfoCreationInformation' }, 'Title': '" + siteTitle + "', 'Url': '" + siteUrl + "', 'WebTemplate': '" + templateID + "'} }";

$.ajax({
url: requestURL,
type: "POST",
data: jsonData,
headers: {
"accept": "application/json;odata=verbose",
"content-type": "application/json;odata=verbose",
"X-RequestDigest": $('#__REQUESTDIGEST').val()
},
success: function () { alert("site Created"); },
error: function (xhr, status, error) {
alert(JSON.stringify(xhr));
}
});
}

Let's now compile and deploy our add-in. the user running your add-in should now be presented with a form similar to the picutre below, allowing them to select both out-of-the-box and custom web templates in SharePoint Online, and create new sites with a simple click. Once the site has been successfully created, the user will get a prompt. Off course there is a lot of validation stuff you should take care off yourself if you'd ever want to implement such a solution into production (check that URL doesn't have characters, etc.).
addin
My Case 1

You can get a copy of the files used in this article Here

Creating Custom Search Refiners in Office 365 using Term Sets

In this article we will cover how you can, in Office 365, create a custom column on a Document Library and use its values to define additional refiners in your search results. The idea for this blog post came after a client engagement of mine, where the organization I was working with wanted to tag their documents with a new managed metadata column named “Branch” which contained the name of the branch within their organization that was the owner of the document.

Step 1 – Define your Term Set
a) Navigate to the Office 365 Admin Center
Waffle

b) Navigate to the SharePoint Online Admin Center
SPAdminCenter

c) Navigate to the Term Store page
termstore

d) Click on the Taxonomy Home, and select New Group
NewGroup

e) Name your new group “Organization”
OrgTermMMS

f) Create a new term set and name it “Branches”
NewTermSet

g) Under the newly created “Branches” term set, enter several terms that would represent the names of the various branches in your organization
Branches

Step 2 – Create the new column in your Document Library
a) Create a new document library in any of your SharePoint Online sites and name it “Important Documents”
ImportantDocs

b) From the View All Site Contents page, click on the “…” icon for the newly created document library and click on Settings
ImpDocsSettings

c) Scroll down on the document library settings page and click on the “Create column” link
Createcolumn

d) Name the new column “Branch” and set its type to “Managed Metadata”
BranchField

e) Scroll down to the “Term Set Settings” section on the new column page. In the Term Picker window, navigate to the “Branches” term set we created at step 1-f above. Select it, and click ok to create the new column.
SelectFieldMMS

f) Upload a new document into the newly created “Important Documents” Library, and set it “Branch” value. In my case I assigned it the value “Finance”.
SetBranch

Step 3 – Wait for the SharePoint Online Search to pick up our value
a) The SharePoint Online Search engine is set to Continuous crawl by default. The new column should be picked up within the next 15 to 60 minutes, and since our column is of type “Managed Metadata”, the search engine will automatically create a managed property and associate it with the automaticaly generated crawled property. Wait for the indexer to pick up our new column.

4 – Map the “Branch” Crawled Property to a Refinable Managed Property
a) Navigate back to the SharePoint Online Admin Center (see step 1-b above);
b) Navigate to the Search Schema page
ManageSchema
c) Confirm that the Crawled property was automatically created. In the top navigation bar, click on the “Crawled Properties” link.
CarledPropButton
d) In the Filters section, within the “Crawled properties” textbox, type in “Branch” and click on the green arrow button. You should see that you have at least 3 results coming back. If you do, then that means that the search indexer managed to automatically crawl your new column.
crawledresults
e) In the top navigation, click on the “Managed Properties” link
ManagedPropButton
f) In the ‘Managed property” textbox enter “RefinableString00” and click on the green arrow button. You should have only one result back
refinablestringfound
g) In this article, we will be re-using a predefined refinable managed property to map to our newly created crawled property. By default, SharePoint online defined 100 refinable “text” properties (RefinableString00 to RefinableString99). These are not mapped to any crawled properties by default.
Click on the arrow beside the RefinableString00 result, and select “Edit/Map Property”
EditMapProp
h) On the next page, scroll down to the “Mappings to crawled properties” section near the bottom. Click on the “Add Mapping” button.
AddMapping
i) We now need to map it to our automatically created crawled property. In the “Search for a crawled property name:” textbox enter “Branch” and click on the “Find” button. The 3 results we got at step 4-d should be returned
3results
j) The property we are interested in here is the one that is formatted like “ows_” (in my case ows_Branch). Select the property from the list by clicking on it, scroll to the bottom of the dialog window and click on “OK”.
selectCrawledProper
k) Once the dialog Windows closes, scroll down to the bottom of the page and click on “OK”. We now have a refinable managed property that is mapped to our crawled property Branch. We are now ready to go and use this refinable property as a refiner on our search page.
5 – Modify the Search Results Page to Include our Refiner
a) Navigate back to your search center. Please note that page to be editable, you need to create your own Enterprise Search center. By default SharePoint Online creates one at https://.SharePoint.com/Search. If you navigate to your search page and don’t see the option to edit the page (assuming you should have the proper rights to edit it) it is most likely because you are trying to edit the default SharePoint OSSSearch page. This page is not customizable, and you should make sure you have a proper Enterprise Search site created. To figure out if the search page you are looking at is the OSSSearch page, simply have a look at the URL. If it looks like the following: https:.sharepoint.com/_layouts/15/osssearchresults.aspx, then you search is not pointing to a customized Search center and you will not be able to edit that page.

Assuming you are all good to go and have created your own Enterprise search center, navigate to the search result page, and edit the page.
EditPage
b) in the left web part zone, find the the “Refiner” web part. Put your mouse over the top right corner of the web part, click on the downward arrow icon and select “Edit Web Part” from the menu.
EditWebPart
c) The web part property panel will appear on the right hand side of the page. From there, take a look at the “Properties for Search Refinement” section. Make sure the “Choose refiners in the Web Part” radio button is selected and click on the “Choose Refiners…” button.
chooserefiner
d) From the list of available Managed properties in the left column, find the “RefinableString00” property we just mapped to our “Branch” column. Click on it to select it in the left column and click on the “Add” button to have it added to the list of active refiners in the right column.
PickedRefiner
e) In the Configuration section at the bottom of the dialog window, enter a friendly display name for the property (otheriwse it will show up as RefinableString00 to the users). In the “Display Name” textbox, enter your friendly name (in my case “Branch”).
DisplayNameforRefinable
f) Scroll down to the bottom of the current dialog box and click on the “OK” button.
g) In the Web Part properties panel on the right, click on the “OK” button.
WPOk
h) In the ribbon at the top of the page, click on the “Save” button.
SavePageAftermodif
6 – Perform a Search
a) After completing section 5 above and clicking the “Save” button on the Search Results page, you should be back on the results page. In the Search textbox at the top of the page, enter a keyword that will allow you to retrieve the document we uploaded at step 2-f. In my case, I will search for keyword “Financial”. enter the search keyword in the textbox and click on the maginfying glass icon to the right of it.
FinancialSearch
b) Once the results come back you should be able to see your newly added refiner in the left column
searchresultfound

Voilà! We managed to create a new column and configure it as a refiner for users, improving their search experience and allowing them to easily find the documents they were looking for!