sprocsSchemaVersion must not be null – SharePoint 2016 Upgrade

You may run across this error.  I inadvertently did.  It happens when the content database upgrade process fails expectantly and then the Mount-ContentDatabase won't execute the upgrade again because it thinks it is already upgraded. Unfortunately, this is a catastrophic failure and will require you to restore your Content database and rerun the content database upgrade. 

How did I run into this?  Well, I opened many powershell windows to be "multi-threaded" in my upgrade and the upgrade code didn't like that at all.  It complained about a shared log file and killed off half the threads.  I guess you shouldn't fire that off more than one instance like you could in 2013 and 2010!

Be sure you always backup your databases before you upgrade in case you need to rollback!
Chris 

Action 16.1.1.0 of Microsoft.Office.Project.Server.Database.Extension.Upgrade.PDEUpgradeSequence failed – SharePoint 2016 Upgrade

What a crazy error. 

There are a few posts about this but very little that tell you what is going on.  When you attempt to upgrade the farm with the following command (after applying a public update or even after the 2016 upgrade):

PSConfig.exe -cmd upgrade -inplace b2b -wait -force -cmd applicationcontent -install -cmd installfeatures -cmd secureresources

You could get the above error in one of the final upgrade stepsactions.  Psconfig.exe is attempting to upgrade all your service application and content databases to your current binary level.  This particular error will show up if someone in your organization was "smart" enough to decided to make a content database a project server database.  You will see this by looking at the tables in the database.  If you see any that are related to Project Server, then you know someone did something really dumb.

You can get your farm to upgrade by simple removing the offending database (aka detach it), then run the command to upgrade everything else.  

Resolution would be to remove all the project server based tables (make a backup of course) and then try your upgrade again. You could also attempt to tell SharePoint that the database is a "Project Server" database and attempt to upgrade it via Project Server but no guarantee that will work especially if the databases are from 2007/2010 days.  You would need a Project Server environment to upgrade all the way from the old version to the latest.

Enjoy, 
Chris 

Data Loss Prevention (DLP) Architecture – Deep Dive

One of the hot features of Office 365 and "by inheritance" in SharePoint 2016 is Data Loss Prevention (DLP).  DLP allows you to execute search queries on your content to find possible "Sensitive Data Types".  You can find many blog post on how to setup DLP queries and how to find data, but that is at the high level.  In this blog post, I get into the nitty gritty of how it actually works under the covers.

High Level Steps:

  • UserApp uploads a document
  • Search gather runs (likely every 15 minutes)
  • Search content indexes the content
    • Ceres engine executes flows, one such Flow is called "Data Loss Prevention"
      • DLP Flow runs
      • DLP Flow looks for keywords
      • DLP Flow looks for regular expression matches
      • DLP excludes invalid "test" values
      • DLP Flow adds "SensitiveType" crawl property data
  • Ceres adds data to the index
  • User queries for DLP data using the "SensitiveType" managed proprerty

Deep Level Steps:

Most of the major work happens in the Ceres Flow.  It uses a "configuration" file of sorts from the "Microsoft.Ceres.DataLossPrevention" .NET Assembly in the GAC.  You will find a "defaultCLassificationRules" resource file in this assembly.  This file is called a "RulePackage" and contains Rule Packs. Currently for SharePoint 2016, there is only one rule pack, Office 365 may have more. Opening this file you will find the following:

  • Entities
    • High level container of a particular "end user" data type
      • US Driver's License Number – there are different in almost every state
      • Canada Driver's License Number – different in each of the provinces
    • Made up of patterns
  • Patterns
    • Made up of IDMatch'es, and Keyword Matches
    • Patterns have confidence levels.  Most expect a confidence level of 75% or higher.
    • Pattern can enforce minMatches (must have at least one keyword, etc)
  • RegEx Expressions
  • Keywords
    • Keywords may be case sensitive – most are not
  • Localization strings – these are used for the rendering and matching of keywords

Patterns:

  • Credit Card Number
  • EU Debit Card Number
  • US Social Security Number
  • US Individual Taxpayer Id Number (ITIN)
  • Canada Social Insurance Number
  • UK NINO
  • UK Driver's License
  • German Driver's License Number
  • German Passport Number
  • UK NHS Number
  • France INSEE
  • France Driver's License
  • Canada Driver's License
  • US Driver's License
  • Japan Driver's License
  • Japan Resident Registration
  • Japan Social Insurance Number
  • Japan Passport Number
  • Japan Bank Account Number
  • France Passport Number
  • US/ UK Passport Number
  • SWIFT Code
  • US Bank Account Number
  • ABA Routing Number
  • DEA Number
  • Australia Medical Account Number
  • Australia Tax File Number
  • Israel National ID Number
  • New Zealand Health Number
  • Spain SSN
  • Sweden National ID
  • Australia Bank Account Number
  • Australia Passport Number
  • Canada Bank Account Number
  • Canada Passport Number
  • Canada PHIN
  • Canada Health Service Number
  • France CNI
  • IP Address
  • IBAN
  • Israel Bank Account Number
  • Italy Driver's license Number
  • Saudi Arabia National ID
  • Sweden Passport Number
  • U.K. Electoral Number
  • Finnish National ID
  • Taiwanese National ID
  • Poland National ID (PESEL)
  • Poland Identity Card
  • Poland Passport Number

Regular Expressions:

Here are some of the regular expressions that DLP flow is looking for:

  • <Regex id="Regex_france_cni">(^|s)(d{12})($|s|.s)</Regex>
  • <Regex id="Regex_uk_electoral">(^|s)([a-zA-Z]{2}d{1,4})($|s|.s)</Regex>
  • <Regex id="Regex_canada_health_service_number">(^|s)(d{10})($|s|.s)</Regex>
  • <Regex id="Regex_canada_phin">(^|s)(d{9})($|s|.s)</Regex>
  • <Regex id="Regex_canada_passport_number">(^|s)(D{2})(d{6})($|s|.s)</Regex>
  • <Regex id="Regex_canada_bank_account_number">(^|s)(d{7})($|s|.s)</Regex>
  • <Regex id="Regex_australia_passport_number">(^|s)([A-Za-z]d{7})($|s|.s)</Regex>
  • <Regex id="Regex_australia_drivers_license_number">(?ix)(?:^|s)(?=(?:[A-Zd]{2}d{2}[A-Zd]{5})(?:$|s|.s))(?=(?:[A-Z]{0,2}d){4,9})(?!(?:d{0,9}[A-Z]){3,9})[A-Zd]{9}</Regex>
  • <Regex id="Regex_australia_bank_account_number">(^|s)([0-9]{6,10})($|s|.s)</Regex>
  • <Regex id="Regex_sweden_passport_number">(^|s)(d{8})($|s|.s)</Regex>
  • <Regex id="Regex_italy_drivers_license_number">(^|s)(D{1}[^b-uw-zB-UW-Z])((w{7})(D))($|s|.s)</Regex>
  • <Regex id="Regex_ipv4_address">(^|s)((?:[0-9].)|(?:[0-9]))(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)(?!(?:.[0-9])|(?:[0-9]))($|s|.s)</Regex>
  • <Regex id="Regex_ipv6_address">(^|s)((?:[A-F0-9]{1,4}:){7}[A-F0-9]{1,4})($|s|.s)</Regex>
  • <Regex id="Regex_israel_bank_account_number">(^|s)(d{2}-d{3}-d{8}|d{13})($|s|.s)</Regex>
  • <Regex id="Regex_saudi_arabia_national_id">(^|s)(d{10})($|s|.s)</Regex>
  • <Regex id="Regex_usa_bank_account_number">(^|s)(d{4,17})($|s|.s)</Regex>

Keywords:

Keywords are important for deciding the confidence level of a particular pattern.  If a keyword is found, then the confidence goes up.  Here are some examples:

  •  UK Nino contains:
    • national insurance number,national insurance contributions,protection act,insurance,social security number,insurance application,medical application,social insurance,medical attention,social security,great britain,insurance
  • UK Driver's License:
    • DVLA,light vans,quadbikes,motor cars,125cc,sidecar,tricycles,motorcycles,photocard licence,learner drivers,licence holder,licence holders,driving licences,driving licence,dual control car

Keep in mind that your SharePoint on-premises environment will always have a static rule pack unless you download and apply updates.  Office 365 will continually get updated rule packs to enhance the DLP engine features so you will be more protected against sensitive data "leaks".

Enjoy!
Chris

Diff of SharePoint 2013 and 2016 codebase

Hi all,

I have done this the past two versions and every time I learn exactly what changed in the product.  Gives me a ton of stuff to explore and blog about later!  In this version, and as in the past two, there were some surprises that I will share here.  Keep in mind, the build that was released still doesn't have a ton of stuff "wired" up in terms of backend code.  Pretty ridiculous to see people touting "SharePoint 2016 training" based on a build that isn't even a Release Candidate (RC).  It's total bull.

Some pre-reqs that the current code-base requires (and is not installed as part on the installer process):

  • Reporting Services Add-In – SQL R2 2008 – 10.0
  • Reporting Services Add-In – SQL R2 2008 – 11.0
  • SQL Server ADOMD.NET (Analysis Services) – 10.0 
  • Windows Azure Storage 3.0.3.0
  • Microsoft.Azure.GraphClient.FirstClient – ??? – no idea where or what this is…has to do with UserProfiles and sync but is not included and can't be downloaded from what research I have done

Couple that show up that are installedconfigured, yet still interesting:

  • NewtonSoft.Json 6.0 – used in Search
  • System.Web.Http 5.2.2.0 – used in Search
  • Microsoft.Data (5.3 and 5.6) – seems some dlls have old 5.3 references that have not been updated

Interesting things I noticed:

  • ChicagoIntegration.dll – check it out…and the fact it has 0.0.0.0 as its version…LOL – used for ContentPush
  • Assemblies moved from GAC_MSIL to GAC_64 – yeah, much more performant for the target processor.  Many Search Dlls fell into this world.  I'll leave it to you to find the rest and see where performance was improved.
  • All things SilverLight and windows phone are gone!
  • SharePoint Education is now dead…as much as Rob thought it didn't ship before…LOL
  • Ceres Flows are not longer seperate Dlls…still need to find where these went!
  • New Ceres OfficeGraph and ContentPush Dlls are included now
  • Portable libraries are now included in the install
  • New format handlers for search and Office Excel files
  • New FileStorage code for Azure Storage interation
  • New O365 Resource discovery code
  • New Directory code with ServerStubs for CSOM

    There were a few Dlls added to the codebase.  These include (some are not verified and should be removed, but I only have so much time in the day):

    • AbsBlobStore
    • ChicagoIngestion
    • Microsoft.Ceres.AnalysisEngine.Managed
    • Microsoft.Ceres.Common.Tools.Sandbox
    • Microsoft.Ceres.ContentEngine.ContentPush
    • Microsoft.Ceres.ContentEngine.Operators.ContentPush
    • Microsoft.Ceres.ContentEngine.Operators.External
    • Microsoft.Ceres.ContentEngine.Operators.Mars
    • Microsoft.Ceres.ContentEngine.Operators.Security
    • Microsoft.Ceres.ContentEngine.Processing.ContentPush
    • Microsoft.Ceres.ContentEngine.Processing.External
    • Microsoft.Ceres.ContentEngine.Processing.Security
    • Microsoft.Ceres.DataLossPrevention
    • Microsoft.Ceres.DocParsing.External.Client
    • Microsoft.Ceres.DocParsing.External.Core
    • Microsoft.Ceres.DocParsing.FormatHandlers.ExcelGeneric
    • Microsoft.Ceres.DocParsing.FormatHandlers.ExcelLegacy
    • Microsoft.Ceres.DocParsing.FormatHandlers.ExcelOdbc
    • Microsoft.Ceres.DocParsing.FormatHandlers.PointPublishing
    • Microsoft.Ceres.External.ContentApi.GraphExtensions
    • Microsoft.Ceres.Flighting
    • Microsoft.Ceres.NlpBase.Phonetic
    • Microsoft.Ceres.Platform.Parallax
    • Microsoft.Ceres.SearchCore.FastServer.DumpLib.Managed
    • Microsoft.Ceres.SearchCore.FastServer.Managed
    • Microsoft.Ceres.SearchCore.GraphModel
    • Microsoft.Ceres.SearchCore.GraphQueryModel
    • Microsoft.Ceres.SearchCore.GraphSeedingModel
    • Microsoft.Ceres.SearchCore.GraphServer.Managed
    • Microsoft.Ceres.SearchCore.GraphStatusModel
    • Microsoft.Ceres.SearchCore.Indexes.GraphServerIndex
    • Microsoft.Ceres.SearchCore.IndexStorage
    • Microsoft.Ceres.SearchCore.KeyValueModel
    • Microsoft.Ceres.SearchCore.Tango
    • Microsoft.Ceres.SearchCore.TenantMapping
    • Microsoft.Ceres.SearchVssWriter
    • Microsoft.Data.Services.Client
    • Microsoft.Data.Services.Design
    • Microsoft.Data.Services
    • Microsoft.FileServices.Beta
    • Microsoft.FileServices.ServerStub.Beta
    • Microsoft.FileServices.ServerStub.V1
    • Microsoft.FileServices.ServerStub.V2
    • Microsoft.FileServices.V1
    • Microsoft.FileServices.V2
    • Microsoft.HtmlTrans.Interface
    • Microsoft.NaturalLanguage.Core
    • Microsoft.NaturalLanguage.KeywordExtraction
    • Microsoft.NaturalLanguage.KeywordExtraction.resources
    • Microsoft.O365.ResourceCatalog.PulsePackage
    • Microsoft.O365.ResourceCatalog.WexPackage
    • Microsoft.O365.ResourceDiscovery
    • Microsoft.OData.Core
    • Microsoft.OData.Edm
    • Microsoft.Office.Access.Server.Application
    • Microsoft.Office.Access.Server.Calculation
    • Microsoft.Office.Access.Server.Calculation.Interface
    • Microsoft.Office.Access.Server.Calculation.Interop
    • Microsoft.Office.Access.Server
    • Microsoft.Office.Access.Server.resources
    • Microsoft.Office.Access.Server.Security
    • Microsoft.Office.Access.Server.UI
    • Microsoft.Office.Access.Services.Application
    • Microsoft.Office.Access.Services.DataProxy
    • Microsoft.Office.Access.Services.DataServer
    • Microsoft.Office.Access.Services
    • Microsoft.Office.Access.Services.Moss
    • Microsoft.Office.Access.Services.Native
    • Microsoft.Office.Access.Services.resources
    • Microsoft.Office.Access.Services.Sql
    • Microsoft.Office.Access.Services.Storage
    • Microsoft.Office.Access.Services.UI
    • Microsoft.Office.BusinessApplications.Tools.AutoGen
    • Microsoft.Office.BusinessApplications.Tools
    • Microsoft.Office.BusinessApplications.Tools.Intl
    • Microsoft.Office.Client.Policy.Portable
    • Microsoft.Office.CompliancePolicy.Platform
    • Microsoft.Office.DocumentManagement.Intl
    • Microsoft.Office.DocumentManagement.ServerStub
    • Microsoft.Office.Excel.Server.CalculationServer.Proxy
    • Microsoft.Office.Excel.Server
    • Microsoft.Office.Excel.Server.MossHost
    • Microsoft.Office.Excel.WebUI
    • Microsoft.Office.Graphics.ShapeBuilder
    • Microsoft.Office.InfoPath.CLRLoader
    • Microsoft.Office.InfoPath
    • Microsoft.Office.InfoPath.Server.Administration.CommandLine
    • Microsoft.Office.InfoPath.Server
    • Microsoft.Office.InfoPath.Server.Host
    • Microsoft.Office.InfoPath.Server.Intl
    • Microsoft.Office.InfoPath.Server.Intl.Resources
    • Microsoft.Office.InfoPath.Server.Pages
    • Microsoft.Office.InfoPath.Server.RepairUtilities.Intl
    • Microsoft.Office.InfoPath.Server.RepairUtilities.Intl.Resources
    • Microsoft.Office.MhtExport
    • Microsoft.Office.Ofc
    • Microsoft.Office.Policy.Barcode.Generator
    • Microsoft.Office.Policy.intl
    • Microsoft.Office.Policy.ServerStub
    • Microsoft.Office.Project.Server.Database.Extension
    • Microsoft.Office.Project.Server.Optimizer
    • Microsoft.Office.SecureStoreService
    • Microsoft.Office.SecureStoreService.Intl
    • Microsoft.Office.Server.Chart
    • Microsoft.Office.Server.Chart.Intl
    • Microsoft.Office.Server.Chart.Intl.Resources
    • Microsoft.Office.Server.Conversion.Framework
    • Microsoft.Office.Server.Conversion.Storage
    • Microsoft.Office.Server.Conversion.ViewerInterface
    • Microsoft.Office.Server.Conversions
    • Microsoft.Office.Server.Diagnostics
    • Microsoft.Office.Server.Directory.SharePoint
    • Microsoft.Office.Server.Directory.SharePoint.Remote
    • Microsoft.Office.Server.Directory.SharePoint.Remote.ServerStub
    • Microsoft.Office.Server.Directory.SharePointInterface
    • Microsoft.Office.Server.Directory.SharePointInterface.ServerStub
    • Microsoft.Office.Server.FilterControls
    • Microsoft.Office.Server.ImportProfileProperties
    • Microsoft.Office.Server.Intl
    • Microsoft.Office.Server.PowerPoint
    • Microsoft.Office.Server.PowerPoint.Intl
    • Microsoft.Office.Server.PowerPoint.Intl.Resources
    • Microsoft.Office.Server.Sandbox
    • Microsoft.Office.Server.Search.Administration.MSSITLB
    • Microsoft.Office.Server.Search.ClickPredict
    • Microsoft.Office.Server.Search.ETW
    • Microsoft.Office.Server.Search.Intl
    • Microsoft.Office.Server.Search.Native
    • Microsoft.Office.Server.Search.Portability
    • Microsoft.Office.Server.Search.PowerShell
    • Microsoft.Office.Server.Search.RemoteSharepoint
    • Microsoft.Office.Server.UI
    • Microsoft.Office.Server.UserProfiles.Synchronization
    • Microsoft.Office.Server.WebAnalytics
    • Microsoft.Office.Server.WebAnalytics.Intl
    • Microsoft.Office.Server.WebAnalytics.Intl.Resources
    • Microsoft.Office.Server.WebAnalytics.UI
    • Microsoft.Office.ServiceInfrastructure.Definitions
    • Microsoft.Office.SharePoint.ClientExtensions.Intl
    • Microsoft.Office.SharePoint.Tools
    • Microsoft.Office.SlideLibrary
    • Microsoft.Office.TranslationServices.intl
    • Microsoft.Office.TranslationServices.intl.resources
    • Microsoft.Office.Visio.Server
    • Microsoft.Office.Visio.Server.GraphicsServer
    • Microsoft.Office.Visio.Server.Intl
    • Microsoft.Office.Visio.Server.Intl.Resources
    • Microsoft.Office.Web.Environment.OfficeServer
    • Microsoft.Office.Web.Sandbox
    • Microsoft.Office.Word.Server.intl
    • Microsoft.Office.Word.Server.intl.resources
    • Microsoft.Office.Workflow.Actions.intl
    • Microsoft.Office.Workflow.Actions.intl.resources
    • Microsoft.Office.Workflow.Feature
    • Microsoft.Office.Workflow.Routing
    • Microsoft.Office.Workflow.Routing.intl
    • Microsoft.Online.FileServices
    • Microsoft.Online.FileServices.ServerStub
    • Microsoft.PerformancePoint.Scorecards.DataSourceProviders.Standard
    • Microsoft.PerformancePoint.Scorecards.Server
    • Microsoft.Search.Platform.Parallax
    • Microsoft.Search.Server.comadmin
    • Microsoft.SharePoint.Client.Directory
    • Microsoft.SharePoint.Client.DocumentManagement.Portable
    • Microsoft.Sharepoint.Client.Publishing.Portable
    • Microsoft.SharePoint.Client.Runtime.Windows
    • Microsoft.SharePoint.Client.Runtime.WindowsPhone
    • Microsoft.SharePoint.Client.Runtime.WindowsStore
    • Microsoft.SharePoint.Client.Search.Applications.Portable
    • Microsoft.SharePoint.Client.Search.Portable
    • Microsoft.SharePoint.Client.ServerRuntime.OData
    • Microsoft.SharePoint.Client.UserProfiles.Portable
    • Microsoft.SharePoint.Client.WorkflowServices.Portable
    • Microsoft.Sharepoint.Excel.Proxies.Intl
    • microsoft.sharepoint.excel.proxies.intl.resources
    • Microsoft.Sharepoint.Sandbox
    • Microsoft.SharePoint.Search.Extended.Query
    • Microsoft.SharePoint.Search.Native
    • Microsoft.SharePoint.WorkflowServices
    • Microsoft.Spatial
    • Microsoft.Web.Constraint
    • Microsoft.Web.Design.Server
    • Newtonsoft.Json
    • System.Spatial
     
    Check out the ExcelPowerPivot workbook attached to this post for more details!
     
    Enjoy!
    Chris