Upgrade from SharePoint 2010 to SharePoint 2016

In this blog, I will go through the process of upgrading your existing SharePoint 2010 farm to SharePoint 2016. Just like it has been the case with the last 3 product release cycles, there are no In-Place upgrade paths for SharePoint 2016. You basically have to build a new SharePoint 2016 SharePoint Farm, and then bring your content databases over. Throughout this article, we will focus solely on upgrading the SharePoint 2010 Content Databases (data), and not the Service Applications.

Background Information

The first thing you need to be aware of if you are planning an upgrade from SharePoint 2010 to SharePoint 2016, is that there are no direct upgrade path: you will have to upgrade your SharePoint 2010 content databases to SharePoint 2013 before going to SharePoint 2016. However, you do not have to build a complete SharePoint 2013 farm in order to accomplish this. All you need is a standalone SharePoint 2013 server (Single-Server Farm), that will be used as a stepping stone to upgrade the schemas of your SharePoint 2010 content databases to SharePoint 2013, and then move on to your new SharePoint 2016 farm.

Conceptually, the diagram below shows what high-level infrastructure is required for such an upgrade. The SharePoint 2010 section, shows our existing SharePoint farm. For our example, the farm will be made up of two Web Front-Ends, one Application server, and a single SQL Server. Then if we look at the SharePoint 2013 section, we can see that we have a single server farm, that has both the SQL and the SharePoint 2013 bits installed on it. This server will act as our stepping stone to SharePoint 2016. Its sole purpose is to upgrade our SharePoint 2010 content databases to SharePoint 2013 so that we can then move them over to SharePoint 2016. Then, in the SharePoint 2016 section, we see that we have replicated our SharePoint 2010 infrastructure (with new server names to avoid conflicts).

Diagram

At this point you could have decided to add more capacity to your SharePoint 2016 farm, but to keep things simple we did a one-to-one replica. Another option, depending on how long you can afford to have downtime for your SharePoint farm, would have been to move the content databases to the SharePoint 2013 Single server and upgrade them, to recycle the SharePoint 2010 infrastructure to install SharePoint 2016 on it, and to move the content databases over the newly recycled infrastructure once ready.

Step 1 – Upgrade your SharePoint 2010 content to SharePoint 2013

The first step in our SharePoint 2010 to SharePoint 2016 upgrade process, is to upgrade to SharePoint 2013.

a) Backup your SharePoint 2010 Content Database

  • Launch SQL Server Management Studio
  • Right-click on your Content Databases and select Tasks > Back Up…
    Back Up...
  • In the dialog window that opens, leave the default settings and click OK.
    InitiateBackup
  • Upon receiving a message to let you know that the backup succeeded, click OK to close it.
    CloseBackup

b) Copy the Backup Files Over to the SharePoint 2013 Single Server Farm

  • By default, the content database will have been backed up in the default SQL installation folder, under the Backup repository. In my case it was located at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup.
    BackupFile
  • Take the .bak file and copy it locally onto the SharePoint 2013 Single server Farm.

c) Restore the SharePoint 2010 Content Database on the SharePoint 2013 server

  • Open SQL Server Management Server on the SharePoint 2013 Single Server Farm.
  • In the Object Explorer panel, right click on the Databases folder and select Restore Database…
    SelectRestoreDB
  • In the Restore Database dialog, select Device from the top radio choices and click on the “…”
    selectrestorebackup
  • Keep the “Backup media type” set to File, click on the Add button and browse for the .bak file we’ve copied over.
    SelectBackupFile
  • Click OK on both windows.
  • This will initiate the Database Restore operation. Once completed, you will get a confirmation letting you know that the restore operation succeeded. Click OK to close it.
    Successrestore

d) Create a New SharePoint 2013 Web Application

In order to upgrade our SharePoint 2010 content databases to SharePoint 2013, we need to associate them with a SharePoint 2013 Web Application.

  • Open Central Administration and navigate to Application Management > Manage web applications
  • In the ribbon, click on New
    NewwebAppl
  • Create a new Web Application on port 80, without a Host Header. By default, if this is the first Web Application you create on your SharePoint 2013 Single Server Farm, the creation interface should automatically default to a new Port 80 web application. Give the Web Application the name Stepping Stone and leave the default options there, and simply click on OK to initiate the creation of the new Web Application.
    NewWebApp

e) Mount the Content Databases to SharePoint 2013

  • Open a new PowerShell session as administrator
  • Run the following lines of PowerShell. Whis will actually initiate the Upgrade operation on your SharePoint 2010 Content Database.

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

    Mount-SPContentDatabase "Intranet-Content-1" -WebApplication "Stepping Stone"

    Where Intranet-Content-1 is the name of my SharePoint 2010 Content Database and Stepping Stone is the name of the SharePoint 2013 Web Application we’ve just created in the previous section

    SP2013Upgraded

  • At this point, you have successfully upgraded your SharePoint 2010 content to SharePoint 2013. In my case, my Content Databases contained 2 site collections, respectively located at /sites/TeamA and /sites/TeamB. Therefore, by navigating to http://<server name>/sites/TeamA I should be able to view my content.
    Sp2013-2010look

Step 2 – Upgrade the Experience to SharePoint 2013

By default, when upgrading content databases from SharePoint 2010 to SharePoint 2013, site collections will continue to use the SharePoint 2010 experience, meaning that the user interface will continue to look just like it was in SharePoint 2010, even though the engine under the cover is all SharePoint 2013. However, before upgrading to SharePoint 2016, the user experience has to be upgraded to the SharePoint 2013 experience. If you try to upgrade a SharePoint 2013 content database that contains site collections with the SharePoint 2010 experience to SharePoint 2016 the upgrade operation will fail and you will get the following error in the upgrade logs: ERROR Please upgrade sites using SharePoint 2010 experience in database Intranet-Content-1 to SharePoint 2013 before proceeding..

a) Automate the Upgrade Process

  • While you could manually upgrade all site collections to the SharePoint 2013 experience, we will automate the process with PowerShell. The following PowerShell script will loop through all Web Applications in the SharePoint 2013 Single server Farm and will upgrading all site collections to the SharePoint 2013 experience:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

    $webApps = Get-SPWebApplication
    foreach($webApp in $webApps)
    {
        $sites = $webApp.Sites
        foreach($site in $sites)
        {
            Upgrade-SPSite $site.RootWeb.Url -VersionUpgrade -Unthrottled
            $site.Dispose()
        }
    }

    * Executing this script will likely take a long time since it synchronously upgrades each site collection one at a time. If you wish to do it in an asynchronous fashion you can use the -QueueOnly switch which will simply put the upgrade request in a queue and will let the timer job get to it at some point in time.

  • To ensure that the experience was properly upgraded, you can navigate back to your site to ensure it now has the SharePoint 2013 look and feel applied to it.
    SP2013 look

Step 3 – Upgrade to SharePoint 2016

The only step remaining is now to migrate our SharePoint 2013 upgraded content to SharePoint 2016. To do so, we will simply repeat step 1, but this time we will bring the data from the SharePoint 2013 Single Server farm onto the newly built SharePoint 2016 farm. I will not be covering the step-by-step procedure to achieve this. Just know that it is exactly the same as what we did to get the content upgraded from SharePoint 2010 to SharePoint 2010. The one difference would be at Step 1-d, where I would off course not recommend creating a Web Application named “Stepping Stone” on your SharePoint 2016 server. You should be mounting your SharePoint 2013 content database against a Production ready Web Application (Step 1-e).

Once the upgrade to SharePoint 2016 is completed, you can navigate back to your site collection to ensure it is properly loading.

sp2016

Upgrade SharePoint 2010 Host Header Web Application to SharePoint 2013 Host-Named Site Collections

A customer of mine is upgrading their SharePoint 2010 farm to SharePoint 2013. As part of the upgrade process they also wish to convert their existing Host Header Web Application to Host-Named Site Collection. The client has 2 to 3 content databases per Web Application in their SharePoint 2010 environment. It is imperative that the URLs used to access the content do not change. The client also wants to keep the SharePoint 2010 look for the migrated sites, at least for a month after migration. Therefore the Host Header Web Appplication to Host Name Site Collection move is simply for administrative purposes.

Also, the client is not using the current root of their Host Header Web Application. So for example, there is no content if users were to browse to http://intranet.contoso.com. Content only exists in site collections under managed paths such as http://intranet.contoso.com/sites/TeamA. The present article covers the process you are required to follow if you wish to accomplish this migration.

Background Information

The current SharePoint 2010 farm hierarchy is as follow:

Web Application: http://intranet.contoso.com
Content Databases:

  • Intranet-Content-1
      Site Collections:

    • http://intranet.contoso.com/sites/Team A
    • http://intranet.contoso.com/sites/Team B
  • Intranet-Content-2
      Site Collections:

    • http://intranet.contoso.com/sites/Team C

In summary, the Host Header Web Application is located at “http://intranet.contoso.com”. This Web Application is servered by two content databases: Intranet-Content-1 which contains two site collections and Intranet-Content-2 which only contains 1 site collection.

***This article assumed you have a plain vanilla SharePoint 2013 server setup and ready to receive the 2010 content.

Step 1 – Create a Placeholder Web Application in SharePoint 2013

In order to bring our SharePoint 2010 Web Applications over SharePoint 2013 and convert them to Host-Named Site Collections, we first need to create a new Web Application without a Host Header that will act as a container for these Host Name Site Collections. This web application will not be serving any web request properly speaking, meaning that it’s root will never be accessed by our clients via the browser. We will also be creating a root site collection in this Web Application. This root site collection will never be used by users, it is simply there to ensure the requests to the server are properly processed. This “no host header” web application is also required for you to be able to properly run SharePoint add-ins (another topic for another day)..

The new Web application we will be creating will be running on port 80 and won’t be configured using a Host Header. Even if you have another Web Application running on port 80 in your SharePoint 2013 environment, that root Web Application has to be created without a host header and on port 80. I will be giving our new Web Application the name “Host Name Site Collections Container”.

To create our new Web Application, I will be using the following PowerShell line of code:
New-SPWebApplication -Name "Host Name Site Collections Container" -Port 80 -ApplicationPool "HNSC" -ApplicationPoolAccount (Get-SPManagedAccount "contoso\sp_farm")
container webapp

***Notice that our Web Application is created using Classic Authentication mode which is deprecated in SharePoint 2013. Do not worry, as part of our complete upgrade process, once all the Host Name Site Collections have been properly created, we will convert our Web Application to Claims Based Authentication. The convertion process will be covered in an upcoming blog post.

Step 2 – Migrate the SharePoint 2010 Content Databases to SharePoint 2013

The nextstep is to bring the SharePoint 2010 Content Databases over to your SharePoint 2013 server. In order to do this, we will be copying both the .MDF and .LDF files of our two content databases (Intranet-Content-1 and Intranet-Content-2) over the 2013 server. You can choose to copy a backup of the files, but in my case, I want to ensure no one can access the content from the SharePoint 2010 server while I’m in the process of doing the migration, so I will simply be dataching the databases from the SharePoint 2010 SQL server and closing all existing connections to it.

a) Detach the SharePoint 2010 Content Databases
Open SQL Server Management Studio and navigate to your Content Databases. Right click on the Intranet-Content-1 database and select Tasks > Detach.
DetachDB
When the dialog box appears, make sure you check the Drop Connections box, then click OK.
DBDrop
Repeat the process for all other content databases, in my case for Intranet-Content-2.

b) Copy the .MDF and .LDF Files
Now that our databases have been detached from our live SQL Server, we can move their associated files over to the SharePoint 2013 server. Find the path to your files, in my case they were located under “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA“. Grab both the .mdf and .ldf file for each content database.
SelectDBFiles
Copy the files over to the SharePoint 2013 SQL server (in my case to C:\Data).
Files Copied

c) Attach the SharePoint 2010 Content Databases to the SharePoint 2013 SQL Server
Now that the files have been moved over to the SharePoint 2013 SQL Server, we need to attach them to it. Open SQL Server Management Server on the SharePoint 2013 SQL Server. In the Object Explorer panel, right-click on the Databases folder, and select “Attach…”.
AttachDB
In the “Attach Database” window that pops-up, click on the Add… button and browse to the Intranet-Content-1.mdf file we’ve copied over in the previous step. Select the .mdf file and click OK.
selectattachdb
Repeat the same process for all content databases, in my case for Intranet-Content-2. Once completed, you should see the content databases listed in the “Databases to attach:” section of the “Attach Databases” window. Ensure all the proper databases are listed and click “OK”.List attachdb
You should now see the SharePoint 2010 databases listed in the Object Explorer panel.
DBListedOE

d) Upgrade the SharePoint 2010 Content Databases to SharePoint 2013
Simply attaching the content databases to SQL Server is not enough fopr SharePoint to recognize these as content databases. We need to do a mount operation on our content databases in order for SharePoint to upgrade their schema to SharePoint 2013 and associate them with our temporary Web Application created at Step 1 above. To mount a content database onto a SharePoint 2013 farm, we need to use the following line of PowerShell code. Running the command will take a few minutes to complete, and PowerShell will display the upgrade percentage as it does the upgrade of the Database schema.

Mount-SPContentDatabase -Name "Intranet-Content-1" -WebApplication "Host Name Site Collections Container"

mountpercentage

Once the mounting process has completed, you’ll need to run the above PowerShell line of code for all other Content Databases, in my case for Intranet-Content-2.
UpgradedSites

Once completed, we end up with an upgraded SharePoint Web Application. Our site collections are available by navigating under our “Container” web application. For my environment, site collections can be accessed following this link: http://sp2013/sites/TeamA

However, these are not Host Name Site Collections, and more importantly, the URL to access them is not the same as it was in SharePoint 2010 which was one of our requirement for the upgrade.

3 – Create Root Site for our Host Name Site Collection

Remember that the client is never accessing the root of what used to be its SharePoint 2010 Host Header Web Application (http://intranet.contoso.com). In the 2013 world however, we need to create yet another “empty container”, this time a site collection, that will be a Host Name Site Collection serving the URL that used to belong to our Web Application http://intranet.contoso.com. This new site collection will be created directly at the following url: http://intranet.contoso.com

To create this new empty Host Name Site Collection, we will execute the following PowerShell lines of code:

$webApp = Get-SPWebApplication "Host Name Site Collections Container"
New-SPSite -Url "http://intranet.contoso.com" -HostHeaderWebApplication $webApp -OwnerAlias "contoso\sp_farm"

2010lookNew-HNSC

You may be wondering why is it you need to have a Site Collection created at the root of the http://intranet.contoso.com if I mentionned earlier that the client will never browse to this location. The reason for this empty site collection to exist is to be able to properly serve server resources to sites collections located under one of its managed path (e.g. http://intranet.contoso.com/sites/TeamsA). If this site does not exist, you will encounter an error that mentions that a site has to exist at the root when trying to create “sub-site collections” (under /sites).
NeedRoot

4 – Convert the Upgraded Site Collections to Host-Named Site Collections

a) Rename the Site Collection
Back in February 2015, Microsoft released a Cummulative Update for SharePoint 2013 that modifies the behavior of the SPSite.Rename method within the object model. This method can now be used to change the URL of a site collection to a host-header one. In order to be able to leverage this new change, your SharePoint 2013 requires you to be at least on build 15.0.4693.1001. For more information regarding this change, you can read the following Knowledge Base article https://support.microsoft.com/en-us/kb/2910928 (thanks to my colleague Roger Cormier for the info).

Now that we’ve made sure we have the proper patch level applied to our farm, we can go ahead and rename our site’s URL from being http://sp2013/sites/TeamA to being http://intranet.contoso.com/sites/TeamA. In order to achieve this, we will use the following lines of PowerShell code:


$site = Get-SPSite "http://sp2013/sites/TeamA"
$site.Rename("http://intranet.contoso.com/sites/TeamA")
IISReset

*** Note that the code above will cause an outage. Ideally, if you have multiple sites to rename at once you can proceed with the renaming and then simply run IISReset once.

a-2) Backup Path Based Site Collections
Another, less prefered option is to convert the upgraded site collections to Host Name Site Collection by doing a backup our site, and then importing it back in as a Host-Named Site Collection. To backup the sites, I will be using the Backup-SPSite PowerShell cmdlets and will be backing up my data in the c:\Data folder of my server.
Backup-SPSite http://sp2013/sites/TeamA -Path C:\Data\TeamsA.bak
Backup

b-2) Restore the Site Collections as a Host Name Site Collection
We are now down to the last step of our migration process, restoring the backed up site collection as host-named site collections. This is achieve by calling the following line of PowerShell code:

Restore-SPSite http://intranet.contoso.com/sites/TeamA -Path "C:\Data\TeamsA.bak" -HostHeaderWebApplication http://sp2013
Restore

c) Navigate to your New Host-Named Site Collection
You are now done. Open your browser and navigate to your new Host-Named Site Collection to ensure everything is working as expected.
Browse

Grant Access to a Document within a SharePoint Document Set

Assume the following scenario: “You wish to break inheritance on a document contained within a SharePoint document set and grant access to it to a specific user”. In other words, the user you are trying to grant access to the document to does not have access at the site level, at the library level, nor at the Document Set level, but is granted explicit contribute rights at the document level. So in this case, if the user tries to access the document directly by its URL he should have access to view and edit the document.

However, if you have the SharePoint server Publishing Infrastructure site collection feature activated, you may run into a case where the user receives an access denied error, even though he’s been granted implicit access to the document. This is because by default, the moment you activate the Publishing Infrastructure feature, SharePoint also activates a second site collection feature named Limited-access user permission lockdown mode.

LALD

This feature serves a specific purpose: Prevents anonymous users or users having Limited Access from accessing a folder on your site. This makes sense since the Publishing Infrastructure feature is used whenever you are creating a public internet site using SharePoint. Basically, whenever this feature is activated, users will get an access denied the moment they hit a folder on which they have Limited Access in the URL. Let me clarify, assume the path to your document within the document set is the following:

    http://prod.contoso.com/sites/Technology/DocLibrary/MyDocSet/MyDocument.docx

The user has limited access to every part of the URL that is underlined above (site, the document library and the document set). The moment SharePoint tries to resolve a part of the URL to which the user has Limited Access, it will return an access denied error.

The solution to this is to simply disable the Limited-access user permission lockdown mode feature at the site collection level. You however need to understand the possible consequences of turning this off if your SharePoint site allows anonymous access, you potentially open the dorr for them to access application pages for your lists and document libraries, which you probably don’t want to have happen.

For more information on the Limited-access user permission lockdown mode, please refer to the following Office Support article (scroll down to the last section).

SharePoint Catalog Connections

Can’t Create a SharePoint Publishing Catalog Connection

I encountered a weird issue while working at a client’s site this week, where we were trying to update the catalog url of some SharePoint Publishing Catalog Connections after we restored the content database from a different farm. The client in question has three SharePoint environments: a Dev one, a Quality Assurance (QA) one and off course, a Production one. Each of these environments had been given a different url. Ex:

Development http://dev.sharepoint.contoso.com
Quality Assurance http://qa.sharepoint.contoso.com
Production http://prod.sharepoint.contoso.com

The client’s SharePoint environment is entirely based on Cross-Site Publishing to display content. They wanted to refresh their Dev environment with a copy of the content from production. So we went ahead and proceeded to refreshing their content database and their Managed Metadata Service database. Because the content was taken from a different environment, the Catalog connections listed still exhibited a Url that pointed to http://prod.sharepoint.contoso.com, so we needed to change this to reflect the dev url.

Connections

So to keep things simple, we decided to simply go and delete all existing Connections, and recreate them using PowerShell, but this time having them pointing to the dev.sharepoint.contoso.com url. Upon executing our PowerShell we got the following weird error:

Exception calling “Update” with “0” argument(s): “The object you are trying to create or modify has the same name as another object.

This error seemed to indicate that somehow there was already a catalog connection that existed and that had the same name as the one we were trying to create. However, there were no connections showing up on the Manage Connection page.

One thing I’ve learned from troubleshooting this issue is that SharePoint automatically creates a Search Results Source for every Catalog Connection you define. These Result Sources will be given the name of your Catalog Connection, followed by the word “Results”.
ResultSources

Normally, if you delete a catalog connection, its associated Result Source is also automatically deleted. However, in our case, when we brought back a copy of the production database, somehow a link got broken in the background, and when we deleted the Catalog Connection, the Result Source was still existing. So the solution to our problem was to go ahead and to delete the orphan Result Source at the Site Collection level using PowerShell.


$site = Get-SPSite "http://dev.contoso.com/"
$ssa = Get-SPEnterpriseSearchServiceApplication;
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.Search")

$fedManager = New-Object Microsoft.Office.Server.Search.Administration.Query.FederationManager($ssa)
$owner = New-Object Microsoft.Office.Server.Search.Administration.SearchObjectOwner([Microsoft.Office.Server.Search.Administration.SearchObjectLevel]::SPSite, $site.RootWeb)
$resultSource = $fedManager.GetSourceByName("Home - Products Results",$owner)
$fedManager.RemoveSource($resultSource)

* Credits for this PowerShell snippet goes to Sathish Nadarajan

Once the Result Source was properly deleted, we managed to execute our PowerShell to recreate the Catalog Connections without a problem.

Create a New Custom Membership Provider for SharePoint 2013

Building up on last week’s article where I explained how you can use PowerShell to automate the configuration of a SharePoint Web Application that uses Form-Based Authentication, this article aims at giving you the steps to develop your own custom Membership provider. The requirement to build a custom membership provider came up this week while I was in Vancouver at a customer’s site. Basically the customer already had a custom database where they stored users’ credentials for some of their Line of Business Applications, and they wanted to reuse this information instead of recreating the aspnetdb database as described in my previous article.

The scenario here is that we want to allow the organization to create user accounts in a custom database table (SQL Server, Oracle, etc.), and have SharePoint authenticate against these custom credentials. The example given in my blog post assumes that you have such a database already created in your organization’s environment. In my case, my database is located locally (I am using a Single Server Farm for demo purposes). My table is simply made off 4 columns:

  • UserId: the unique identifier of the user’s record
  • UserName
  • Password
  • UserEmailAddress

SQLOM

Yes, you are correct, I am storing my passwords in Clear Text in my database….what is wrong with that?!? Just kidding, of course in a production environment you’ll want to encrypt your passwords so that they are not directly exposed to anybody having access to your environment. However, in order to keep my example simple, I went ahead and simply stored them in clear text to better illustrate how the process works.

Create the Custom Membership Provider’s Assembly

The first thing you need to do is open Visual Studio and create a new Class Library project. In my case the project will be named NikCustomProvider, and I will be building it on top of the .NET 2.0 Framework (to keep thing simply when it comes to the GAC).
ClassLibrary
In my case, I will be renaming the Class1.cs file that gets created by default in Visual Studio to “MembershipProvider”. In your project, add a reference to the System.Web, System.Web.ApplicationServices and to the System.Configuration .NET Framework assemblies.
SystemWebRef
Specify that your class will be using the System.Web.Security Namespace in your class’s headers.
Using
The next step is to declare that your class will be inheriting from the SQLMembershipClass.
InheritSystemWebSecurity
Inheriting this class will allow you to override 6 very important methods:

  • CreateUser: Which allows you to add new users into your custom SQL database through IIS or any other method that allows you to leverage your Custom Membership Provider. Note:: if you have a custom application that takes care of creating your users in your custom database, you may not need to override this method at all in your code. In our current example we will simply implement this method by throwing out a NotImplementedException;
  • ValidateUser: This method takes in a username and a password, and ensures that the user exists in the custom database and that the credentials are valid;
  • GetAllUsers: Allows you to retrieve all users from your custom database;
  • GetUser: Which gets a specific user from the database;
  • UpdateUser: Which updates a specific user record in your custom Database. Note: will simply return a NotImplementedException in our case.
  • DeleteUser: Which deletes a specific user record in your custom Database. Note: will simply return a NotImplementedException in our case.

Override the GetAllUsers Method

As mentioned above, the GetAllUsers method is the method that will allow your custom Membership provider to retrieve a list of all users. Be it for IIS trying to display a list of them under the .NET Users module, or SharePoint trying to retrieve a full list of users available when you are trying to grant permissions to items, this method is very important. This method allows you to do pagination if you need to obtain users back from the database in smaller batches (instead of getting them all back at once which can impact performance). Again, for the sake of simplicity, we will simply ignore this option and get a full list of all user back. Your method needs to return a MembershipUserCollection object, and requires you to set the totalRecords variable before returning this value. That variable represents the total number of user entries that are about to be returned in your MembershipUserCollection object. If you use pagination, make sure you don’t have this variable set to reflect the total number of users in your Database, but rather the number you have in the batch returned.

My method will simply go, query my custom SQL database table and return all results back from it (SELECT * type of query). Here is the full code for my method:

public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server=WIN-6O74I1M7M69;Database=Demos;Integrated Security=true");
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Users", conn);
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

MembershipUserCollection members = new MembershipUserCollection();
int totalUsers = 0;
while (reader.Read())
{
members.Add(new MembershipUser(providerName: "NikCustomProvider",
name: reader["UserName"].ToString(),
providerUserKey: null,
email: reader["UserEmailAddress"].ToString(),
passwordQuestion: "",
comment: "",
isApproved: true,
isLockedOut: false,
creationDate: DateTime.UtcNow,
lastLoginDate: DateTime.UtcNow,
lastActivityDate: DateTime.UtcNow,
lastPasswordChangedDate: DateTime.UtcNow,
lastLockoutDate: DateTime.UtcNow));
totalUsers++;
}
totalRecords = totalUsers;

conn.Close();
return members;
}

Override the GetUser Method

The second method we need to implement is the method that will allow us to retrieve a specific user out of our database. Now, when dealing with SharePoint, this method is called whenever you try to search for a user in the people picker or people directory control. What you really want to do here to implement some sort of a wild card search query back to your custom Database. What I mean by this is that if you were to simple declare a SELECT statement and return object that would be an exact match on the username, the user will need to type in the username exactly as it is in the database in order to retrieve any users. For example, if my username was “Nikolas”, and in the people picker I’d type in “Nik”, my user would never be retrieved back. SharePoint wouldn’t even be able to retrieve suggestions that may match my query. Instead what you really wish to do is implement your SQL query using a LIKE statement to allow SharePoint to retrieve users out of your custom SQL Database based on username patterns.

My code simply uses the received username (or username pattern, remember “Nik” vs “Nikolas”) and will return a MembershipUser object back. Here is the full code for my method:


public override MembershipUser GetUser(string username, bool userIsOnline)
{
try
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server=WIN-6O74I1M7M69;Database=Demos;Integrated Security=true");
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Users Where UserName LIKE '" + username + "'", conn);
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
MembershipUser user = null;
if(reader.Read())
{
user = new MembershipUser(
providerName: "NikCustomProvider",
name: username,
providerUserKey: null,
email: reader["UserEmailAddress"].ToString(),
passwordQuestion: "",
comment: "",
isApproved: true,
isLockedOut: false,
creationDate: DateTime.UtcNow,
lastLoginDate: DateTime.UtcNow,
lastActivityDate: DateTime.UtcNow,
lastPasswordChangedDate: DateTime.UtcNow,
lastLockoutDate: DateTime.UtcNow);
}
conn.Close();
return user;
}
catch {}
return null;
}

Override the ValidateUser Method

This method is where all the actual security checks happen. It is responsible to receive the username and password entered by the user on the Form-Based login form and to ensure they match the records in our custom Database. If you were to be bold and simply want your users to connect with their usernames without having to pass in any password whatsoever, you could set this method to simply return true and that way users will always be granted access to your environment, doesn’t matter if they entered a valid password or not. Not sure this is really what we want on a production system right? So my method here will simply do a select query against my custom Database using both the received username and password in my where clause. If the query returns an entry, then my method will return true, otherwise it will return false. Now, you may be asking yourself “What about if the database returns more than one entry?”. My answer to you is that you probably screwed up somewhere in your custom Database. Unless you have a very good business rational to use a custom Database that allows for multiple entries using the same username for authentication, the system you use to manage the credentials stored in your database should ensure that the user name are unique. Think of your username as a Primary Key. It is the identifier for your records, and you should not allow two entries to have the same key.

Here is the full code for my method:


public override bool ValidateUser(string username, string password)
{
try
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Server=WIN-6O74I1M7M69;Database=Demos;Integrated Security=true");
conn.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Users Where UserName = '" + username + "' AND Password='" + password + "'", conn);
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
return true;
}
conn.Close();
}
catch { }
return false;
}

Ensure your Assembly has a Strong Name

In order to be able to deploy our assembly in the Global Assembly Cache (GAC), it requires a strong name. This can be done by simply signing the assembly. Right click on your project in Solution explorer (project not solution) and go properties. In the left, click on the “Signing” tab, and toward the bottom of the panel, check the “Sign the assembly” checkbox.

SignAssembly

Under the checkbox, click on the “Choose a strong name key file” drop down and select “New…”
New

A pop-up will appear asking you to provide a key file name. You can put in whatever you feel like. Make sure you uncheck the option to use a password to protect the key file and click OK.
SigningAss

Add your Custom Membership Provider Assembly to the GAC

Make sure you use Visual Studio to compile your assembly and that you compile it using the .NET 2.0 framework. Note that compiling it to .NET 4.0 would work as well, but you’ll need to use a different approach to GAC you DLL than the one I am using here which is simple to call the gacutil.exe tool. Open a new PowerShell session as administrator and run the following lines of PowerShell, making sure to update the paths to reflect your environment. In my case, in order to be able to get the gacutil.exe tool, I needed to download the Windows 7 SDK tools (https://www.microsoft.com/en-ca/download/details.aspx?id=8279).


set-location "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin"
.\gacutil.exe /u "NikCustomProvider" /f
.\gacutil.exe /i "C:\Users\Nik\Documents\Visual Studio 2015\Projects\NikCustomProvider\NikCustomProvider\bin\Debug\NikCustomProvider.dll" /f
iisreset

Configure a Trusted Provider

Now that we have built our custom Membership Provider and that it has been made available globally in the Global Assembly Cache, we need to declare it as a trusted provider in the application.config file of the .NET framework in order to be able to consume it in our SharePoint Web Applications. In order to do this, you need to go and edit the application.config file found at %systemroot%\System32\inetsrv\config. Go ahead and open the file in notepad. Search for the following section:

<trustedProviders allowUntrustedProviders=”false”>

Within this section, simply add the following tag to register your custom assembly. Ensure you replace the namespace, class name, and token by yours:
<add type=”NikCustomProvider.MembershipProvider, NikCustomProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6f548d97bd0e3c37″ />

Save and close the application.config file. You are now allowed to use it as a custom provider within your web applications.

Modify your SharePoint web.config Files to Register the Custom Provider

To get more details on the “how” and “why”, please refer to my previous blog post SharePoint Form-Based Authentication with SQL using PowerShell. Basically what we need to do at this stage is modify the web.config of the following three SharePoint Web Application/Services to ensure they are given access to our custom Membership provider:

  • Your SharePoint Web Application that will be using Form-Based Authentication to connect;
  • Central Administration;
  • Secure Token Service (STS).

For each web.config, find the following section:

<membership defaultProvider=”i”>
<providers>

Within it, add the following entry to register your custom provider (note that my provider will use a Connection String named “Form Based Auth”, which I defined in my previous article):

<add name=”NikCustomProvider” type=”NikCustomProvider.MembershipProvider, NikCustomProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=6f548d97bd0e3c37″ applicationName=”/” connectionStringName=”FormBasedAuth” enablePasswordReset=”false” enablePasswordRetrieval=”false” passwordFormat=”Clear” requiresQuestionAndAnswer=”false” requiresUniqueEmail=”true” />

Save each web.config once the modifications have been completed.

Configure your SharePoint Web Application’s Authentication Provider

The last step of our configuration involves changing the value of our Web Application’s Authentication Provider to point to our newly created custom Membership provider. Open Central Administration and navigate to the Manage Web Applications page. Select the Web Application you want to enable Form-Based Authentication on, and click on the “Authentication Providers” button in the ribbon.
AuthProvider

You will be presented with a dialog. Select the Zone on which you wish to enable FBA. In my case the “Default” zone.

Defaultauth

Scroll down to the FBA section and enter the name of the provider (e.g. NikCustomProvider)

cacustomprov

Click on the Save button to persist the changes.

Test your changes

Make sure your FBA users are given access to your site collection, and navigate to it in the browser. In my case, I will navigate to my FBA enabled Web Application “http://localhost:83”. When prompted to login, enter the credentials of a user stored in your custom Membership database.

Bob