Can You Migrate SharePoint FBA SQL External User Accounts and Passwords to Office 365?

(Update as of August 2018:  This article is still accurate.  However, a new option for migrating on-premises FBA\SQL external user accounts is now available - ExCM Extranet Online.  Businesses with significant investments in on-premises SharePoint extranets now have the option  to use Office 365 for Intranet purposes and ExCM Extranet Online for extranet purposes.  ExCM Extranet Online supports seamless migration of SharePoint on-premises external user accounts and content.)

The short answer is: there is no way to do this and likely never will be.  Read on if you are interested in more details.

Background on SharePoint FBA and SQL External User Accounts

Using SharePoint’s forms-based authentication feature along with the SQL Membership Provider feature has been a very popular way to use SharePoint as an extranet platform for many years.  In many cases, after a company got their Intranet up and running on SharePoint, the next logical step was to provision a new Web Application for use as an extranet for collaborating with customers, vendors, etc.


Most companies do not want to store external user accounts in AD, so the built-in SQL-based account store that has been part of ASP.NET since .NET 2.0 is a great way to store the external user accounts in a standard SQL Server database.  SharePoint, FBA and the SQL Membership Provider have proven to be a very secure and industrial-strength way of providing business this capability.  (See our white paper on why this approach works so well for businesses).

When you set up the SQL Membership Provider in your SharePoint Farm and hook it up to SharePoint as an authentication provider, there are a number of settings you can configure in the web.config file for the SharePoint web application that you want to use the SQL user accounts with.  You can see some of them in use in a sample web.config file shown on MSDN here:  https://msdn.microsoft.com/en-us/library/system.web.security.sqlmembershipprovider(v=vs.110).aspx#Anchor_7

A couple of them are really important for purposes of this discussion:  passwordFormat and enablePasswordRetrieval.
 
passwordFormat can have its value set to Clear, Encrypted or Hashed.  Hashed is considered the best practice as it will cause the passwords to be stored using a hashing algorithm that takes a randomly generated “salt” value to do the hashing.  This approach makes the storage of the passwords in SQL Server very secure.

enablePasswordRetrieval accepts a value of True or False, but when the passwordFormat is set to Hashed, this value is ignored.  Hashed passwords can never be retrieved from the database in their unhashed form by man or software.

This level of security provided in the SQL Membership Provider is awesome when it is used in conjunction with SharePoint FBA for storing external user accounts.  And, like I already mentioned, this has long been considered the best practice way of setting up SharePoint FBA\SQL extranets and many, many companies around the world have done it this way.  (Again, our white paper on the subject is an excellent resource.)

Background on Office 365 Subscriber Accounts and External Sharing Accounts

In contrast, all Office 365 User Accounts are stored in Azure AD.  While Microsoft has leveraged the term “AD” for marketing purposes, Azure AD and on-premise Windows AD are not the same technologies at all.  Azure AD is a relatively new cloud-oriented directory service that is run 100% as a service by Microsoft.  Office 365 simply leverages Azure AD for all its user account authentication needs.  So, if you can successfully sign in to Office 365 its because you have a Azure AD account, although the terminology you know that account by might be “my Office 365 account”, or “my Microsoft Online account”, or “my Live ID account”.  Now days those are all one and the same – an Azure AD account.

Of note is the fact that Office 365 differentiates the status of an Azure AD account depending on what content is being shared and by whom.  An Azure AD account is treated as an Office 365 subscriber account when a user is working on content within their paid instance of Office 365.  Conversely, when something is shared with that user from someone else’s Office 365 instance, Office 365 treats the first user’s account as an external sharing account. 

We wrote a recent blog post on what is different for the user when their account is treated by Office 365 as an external sharing account:

http://sharepointsolutions.blogspot.com/2015/09/what-external-users-cannot-do-in-office.html

The Problem(s) with Migrating SharePoint FBA SQL Accounts to Office 365 External Sharing Accounts

Hopefully, reading the previous two sections has given you a little more understanding of how external user accounts work in on-premise SharePoint vs. Office 365.  Now let’s look at the specific problems that will prevent you from being able to find a way to migrate your existing external user accounts from the former to the latter - if your company is planning to migrate from on-premise SharePoint to Office 365 for extranet purposes:

Problem 1:  The existing external user passwords cannot be retrieved in order to be migrated.

Because the SQL Membership Provider uses hashing and salting to store the external user passwords in its database, there is never going to be a way to migrate those passwords to Office 365 or anywhere else.  The only options is for each external user to manually reset their password in Office 365, or whatever the target system is.
This would be a huge issue for many companies because their on-premise SQL Membership Provider database stores accounts for thousands or tens of thousands of external users.  Some of the ones that come immediately to mind are those at very large law firms and very large accounting firms.  They can have huge external user account databases!

Problem 2:  Office 365 does not allow you to create\maintain accounts for your external users, only the external users themselves can do this.

Even if you could get the external user passwords out of the SQL Membership Provider database, it would be impossible for you, or any tool, to create the equivalent external user account in Office 365.  This is because the Office 365 approach to external user accounts is fundamentally different.  With Office 365, you no longer have any control over the accounts of your external users.  You can’t create them, or maintain them.  Only the external user can create them and maintain them.  Your choice is simply whether you want to share content with an external user who already has created his\her Office 365 account.  This recent blog post talks about this in more detail:

http://sharepointsolutions.blogspot.com/2015/10/should-business-extranet-applications-run-on-cloud-service.html

Problem 3:  If you are migrating externally shared content, all of the sharing permissions will break, regardless of the content migration tool that you use.

Finally, remember that in SharePoint, accounts and permissions are two different things.  When you give a user’s account (whether an AD account or SQL FBA account) permissions to something in SharePoint (a site, library, document or item), SharePoint stores that granted permission inside the SharePoint content database.  If someone removes the user’s account from AD or the SQL FBA database, you end up with what amounts to one or more “orphaned” permissions records in the SharePoint content database.  The permissions information for the object points to a user account that is no longer present in the SQL or AD account database.

So, even if you are able to successfully migrate all of your existing on-premise extranet content to Office 365, all of your permissions granted to external user accounts will be “orphaned” and there will be no way to re-link these permissions to valid Office 365 external sharing accounts, even if you could find those accounts.

Conclusion

(Update as of August 2018:  A new option for migrating on-premises FBA\SQL external user accounts is now available - ExCM Extranet Online.  Businesses with significant investments in on-premises SharePoint extranets now have the option  to use Office 365 for Intranet purposes and ExCM Extranet Online for extranet purposes.  ExCM Extranet Online supports seamless migration of SharePoint on-premises external user accounts and content.)

If you have made a significant investment in an on-premise SharePoint Extranet application that uses FBA and the SQL Membership Provider, you are best served to keep it on-premise if it is important to you to be able to retain all of your external user accounts and passwords, and the content permissions that your user’s have granted.
 
If your company has mandated that “everything must move to the Cloud”, then your options are to A) have your external users start over from scratch with new Office 365 external sharing accounts and then manually re-share everything with them, or B) move your existing on-premise SharePoint Extranet server(s) to run as Azure or Amazon virtual machines so they are “in the Cloud”.
 
With Option B, you can “check the box” that your extranet runs in the Cloud, but continue to use the same SQL Membership Provider account database that you have been using all along and not impact your external users in any way :).  For a brief overview of how this would work, check out this article we wrote a while back:

http://sharepointsolutions.blogspot.com/2014/05/sharepoint-extranet-on-office-365-part-2.html