SharePoint 2010 Search: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

Last night we stumbled intoo an issue at one of our clients.The clients supplier had recently performed a lot maintenance activities in a maintenancewindow on their SharePoint 2010 environment. One of them was replacing the existing SSL certificates with a new wildcard certificate. The SharePoint farm exists of two WFE’s, one APP and a SQL server. The regular three-tier-farm you see alot.

Usually this change activity should not be a problem however, the supplier was somewhat to enthasiastic with replacing the SSL certificates. They unconsciously replaced the self-signed SharePoint Services SSL certificate on the SharePoint Web Services site in IIS with the new wildcard SSL certificate. Without knowing this history we started troubleshooting SharePoint Search which was causing issues. When browsing to the Search Service Application Administration component it showed this error message on the Administration Page:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

Users were not getting back any search hits and when browsing to the Content Sources in Central Administration, SharePoint threw a Correlation ID.

After going through the logging and finding the error related to the correlation ID we noticed this error in the ULS logging:

An operation failed because the following certificate has validation errors:\n\nSubject Name: CN=*, OU=IT, O=”Contoso”, L=Paris, S=Paris, C=FR\nIssuer Name: CN=Thawte SSL CA – G2, O=”Thawte, Inc.”, C=US\nThumbprint: AB12CD34XX123456ABCDEFG123XXX\n\nErrors:\n\n SSL policy errors have been encountered.  Error code ‘0x2’

This error pointed us to SharePoint Search trying to communicate over HTTPS to it’s back-end web-services with the use of the clients wildcard SSL certificate and failing. After troubleshooting further we noticed the wilcard certificate was indeed binded on the HTTPS listener on port 32844. Now you would think, that’s an easy fix to do. Just remove the certificate on the binding and there you go. Well, when configuring a SSL certificate on a binding in IIS you are required to select a SSL Certificate located in the servers Personal Certificate store. And that’s not the correct store for SharePoint web-services.

The rootcause:

SharePoint uses it’s own “SharePoint Services” self-signed certificate to securely communicate over HTTPS with it’s web-services and other farm memberservers. You can find this specific certificate looking in the Local Computer Certificate Store using the MMC snapin. I discovered a folder called SharePoint which had three certificates in it, all issued by the Sharepoint Root Authority:

  • SharePoint Security Token Service
  • SharePoint Security Token Service Encryption
  • SharePoint Services

The solution:  

Now to undo the faulty configuration and reconfigure the correct “SharePoint Services” SSL certificate on the SharePoint Web Services IIS site – which can’t be done with the IIS Manager Console –  you can either use good old netsh command-line or use PowerShell. Now the last method is the method I personally prefer so that’s what you’re getting.. =)

$iisBinding = “SharePoint Web Services”
$webservice = Get-WebBinding -name $iisBinding -protocol “https” -port 32844
if ($webservice)
$pfx = get-childitem cert:\\localmachine\sharepoint | where { $_.subject -contains “^CN=SharePoint Services” }
if (!$pfx)
throw “No certificate found with the name “SharePoint Services” in the SharePoint certificate store in MMC. The script has stopped”

[void]$webservice.AddSslCertificate($pfx.ThumbPrint, “SharePoint”)
throw “The certificate cannot be assigned to the designated IIS Binding. Check servers eventviewer for further information.”

if (!(Get-WebBinding -name $iisBinding -protocol “http” -port 32843))
New-WebBinding -name $iisBinding -ip “*” -port 32843 -protocol “http”

This script looks for an existing IIS site or binding with the port 32844 (which SharePoint uses OOTB). Once found it checks whether it can find an existing valid SSL certificated in the computer which contains the name “SharePoint Services”. If both are found it adds the correct certificate to the designated SharePoint Web Services binding. An action which can’t be done via the ISS Management Console.

This recovers the Search Service Application to it’s original healthy state. In our specific scenario we had to run a “Full Crawl” to on all the Content Sources since the index became inconsistent too. I always use this small PowerShell script to initiate the Full Crawls:

$searchapp = Get-SPEnterpriseSearchServiceApplication “Search Service Application” 
Get-SPEnterpriseSearchCrawlContentSource -SearchApplication $searchapp | where-object { $_.Type -contains “Sharepoint”} | foreach-object { $_.StartFullCrawl() }

If this still doesn’t fix your issue you’re being really unlucky. You will still be getting this error message prompted when initiating searchresults:

“Property doesn’t exist or is used in a manner inconsistent with schema settings” and not receiving any People results.

But don’t worry, there’s still one option to be checked. We found out that after this issue is dependant on the actual webparts used by search (including People Search). For this the solution in the end was to edit the People Core Result Web Part on the search results page and expand the “Display properties” and make sure “Use Location Visualization” is checked.

SharePoint User Profile Service Application: Exception while trying to migrate account. The user does not exist or is not unique.

A while ago I was facing an issue with NetBIOS names when configuring the SharePoint Server User Profile Service Application. In this particular scenario the NetBIOS name was different from the domainname. For instance NetBIOS namespace would be CON\username and the domainname would be CONTOSO\username. The errors which were thrown on the applicationserver:

The extensible extension returned an unsupported error.
The stack trace is:

“System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.AggregateException: One or more errors occurred. —> Microsoft.Office.Server.UserProfiles.UserProfileException: Exception while trying to migrate account ‘CON\Username’ to ‘CONTOSO\Username’. —> Microsoft.SharePoint.SPException: The user does not exist or is not unique. —> System.Runtime.InteropServices.COMException: The user does not exist or is not unique.<nativehr>0x81020054</nativehr><nativestack></nativestack>
at Microsoft.SharePoint.Library.SPRequestInternalClass.SelectSitesAndUserInfoForMigration (Object& pvarContentDsns, Object& pvarContentIds, String bstrOldLogin, String bstrNewLogin, String bstrFullUserKey, Boolean bEnforceSidHistory, Guid guidSubscriptionId, String& pbstrNewLogin, Byte[]& ppsaOldSid, Byte[]& ppsaNewSid, Object& pvarSidHistory, Object& pvarSiteIds)at Microsoft.SharePoint.Library.SPRequest.SelectSitesAndUserInfoForMigration (Object& pvarContentDsns, Object& pvarContentIds, String bstrOldLogin, String bstrNewLogin, String bstrFullUserKey, Boolean bEnforceSidHistory, Guid guidSubscriptionId, String& pbstrNewLogin, Byte[]& ppsaOldSid, Byte[]& ppsaNewSid, Object& pvarSidHistory, Object& pvarSiteIds)
— End of inner exception stack trace —
at Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx)
at Microsoft.SharePoint.Library.SPRequest.SelectSitesAndUserInfoForMigration(Object& pvarContentDsns, Object& pvarContentIds, String bstrOldLogin, String bstrNewLogin, String bstrFullUserKey, Boolean bEnforceSidHistory, Guid guidSubscriptionId, String& pbstrNewLogin, Byte[]& ppsaOldSid, Byte[]& ppsaNewSid, Object& pvarSidHistory, Object& pvarSiteIds)
at Microsoft.SharePoint.Administration.SPFarm.MigrateUserOrGroup(Guid subscriptionId, String oldLogin, String newLogin, Boolean usersOnly, Boolean enforceSidHistory)
at Microsoft.SharePoint.Administration.SPFarm.MigrateUserAccount(Guid subscriptionId, String oldLogin, String newLogin, Boolean enforceSidHistory)
at Microsoft.Office.Server.UserProfiles.UserProfile.MigrateUser(String newAccountName, Boolean fullMigration)
— End of inner exception stack trace —

The default configuration and behaviour of the User Profile Service Application does not include the NetBIOS namespace. When you would address the Service Application with PowerShell with Get-SPServiceApplication you would see the property NetBIOSDomainNamesEnabled set to 0 which is disabled.

To enable the NetBIOS namespace for SharePoint you have to recreate the Synchronization Connection to ADDS in the User Profile Service Application. This is necessary since in the creation process of this connection the Configuration Naming Context (CNC) is written to the FIM configuration and SQL database behind it.

  1. Delete the existing faulty Synchronization Connection in the User Profile Service Application.
  2. Open the SharePoint 2013 Management Shell with administrative rights.
  3. Run Get-SPServiceApplication and copy the GUID of the User Profile Service Application.
  4. Run this script in with the correct GUID.

    $UserProfile = Get-SPServiceApplication –Id <GUID>



  5. Create a new Synchronization Connection with Active Directory Domain Services.
  6. Run a new and first “Start Profile Synchronization”.

Once the sync has completed eveyones SAMAccountName should be including the correct NetBIOS namespace of CON\username.

ULS log settings. What setting should I use?

I get this question surprisingly often: “what is the best setting for my SharePoint Farm diagnostic logging (ULS logs)?
Unfortunately; there is no best answer here because it really depends on how your farm is configured and, most importantly, how it is managed and runs.

So there isn’t an answer then?” Well.. there is, kinda, because there are guidelines! And knowing how to use these guidelines got me triggered to write up some advice.
The other day a customer called us and told us that their SharePoint environment did not work anymore. Their hosting company rebooted the server and it worked again, but could not explain the issue so they asked if we could help out. After logging in it didn’t take long to see what happened: the guidelines were not followed.

  • The ULS logs were on the system drive
  • The ULS logs were configured with Verbose logging

What we did here was move the ULS log file directory away from the system drive and configure the ULS logs to their default setting.

Basically this last one sentence should be your best practice in most SharePoint environments.
You should never have your ULS log files on your system drive. Yes, ULS is designed for knowing when a disk space issue is imminent and reduce logging when this happens, but issues can still occur in certain cases!
Furthermore never use Verbose when you are not actively troubleshooting one or more issues on a environment. If you do use it, remember to set the level back to what you need when you are done with troubleshooting.

So, back to the guidelines.
I’ve created an overview of when to use what setting. Use it the right way and you are well on your way to a properly managed SharePoint environment!

The default setting.
Use this in 90% of the SharePoint environments:

  • SharePoint 2007: STSADM -o SetLoggingLevel –Default
  • SharePoint 2010: (start the SharePoint Management Shell) Clear-SPLogLevel
  • SharePoint 2010: (start the SharePoint Management Shell) Clear-SPLogLevel

The-very-good-managed-SharePoint-environment setting (yeah, I just made that up).
Use this when you need almost no logging since you have very few to no issues most of the time:

  • SharePoint 2007: Central Admin, select “All” categories, and “Error”, “Error”.
  • SharePoint 2010: (SharePoint Management Shell) Set-SPLogLevel -TraceSeverity Unexpected -EventSeverity Error
  • SharePoint 2013: (SharePoint Management Shell) Set-SPLogLevel -TraceSeverity Unexpected -EventSeverity Error

The troubleshoot setting.
Use this when you need to troubleshoot an issue (remember to set the level back to what you need when you are done with troubleshooting)

  • SharePoint 2007: Open Central Admin > Operations > Diagnostic Logging. Then set ‘select a category’ to ‘All’ categories, set ‘Least critical event to report to the event log’ box value to ‘Warning’. Set ‘Least critical event to report to the trace log’ box value to ‘Verbose’.
  • SharePoint 2010: (SharePoint Management Shell) Set-SPLogLevel -TraceSeverity Verbose -EventSeverity Verbose
  • SharePoint 2013: (SharePoint Management Shell) Set-SPLogLevel -TraceSeverity Verbose -EventSeverity Verbose

There is more to this, of course, but if you obey and maintain these guidelines you can’t go wrong.

Remember that a good performing SharePoint environment starts with who manages it. So I’ll leave you with what I tell my customers who manage their SharePoint environment themselves:

don’t prepare your environment for SharePoint but make sure you are prepared for SharePoint.

Make sure you properly know how it works and users will love their environment!


GetSafeOrdinal FATAL ERROR Could not find column LastModifiedTime in PartitionProperties

Migrating from one SharePoint version to another can be harder then it seems at first glance. Thinking through your migration strategy and prerequisites is the first and foremost important step, but no migration is without issues, and no issue is without an error you nor Google has seen before.

In our case this issue happened when migrating from SharePoint 2010 to SharePoint 2013 and upgrading the User Profile Service Application. This caused the error:

GetSafeOrdinal: FATAL ERROR: Could not find column ‘LastModifiedTime’ in PartitionProperties. Please check if the Content and Federated Services farm are of compatible versions.

After rerouting our steps we found out that our customer had ordered and installed a SharePoint .iso in their native language and not in English, where the old SharePoint environment was in English but with a language pack installed in their native language. So where the environment seems Dutch on both versions when using Central Administration, basically these are actually two different languages. Migrating the UPS Sync database from one environment to another caused this error, but recreating the UPS Sync database (and keeping the migrated and upgraded UPS Social and UPS Profile databases) solved this error. All other databases could be migrated and upgraded but this particular one caused a little headache, but nothing is unsolvable – right? 🙂

SharePoint Online performance issue caused by deleted term store admin user objects

After working with Microsoft for over a month on a daily basis we found the cause of a serious performance issue within SharePoint Online. This issue affected several enterprise customers of ours.

This issue only occurs on sites which use the “managed navigation” functionality. In SharePoint, you can choose between two navigation methods: structural navigation or managed navigation. Structural navigation is based on site structure. Managed navigation is based on term sets. For further information read this article.

Rootcause symptoms which identify the cause:
When clients initiate a webrequest to the SharePoint Online site it takes 5 to 30 seconds untill the server returns data to the GET method. See this screenshot from Internet Exporer “Profiler”. 


Rootcause analysis:
After intensively troubleshooting several tenants we noticed strange behaviour the way the system communicates with the Managed Metadata Service Application. Since the sites are using managed navigation the content will be represented using a managed metadata term set. Because of this configuration EVERY FIRST WEBREQUEST is relies on your defined term sets and its configuration.

Allright. Now that we have some understanding how the system relies on the Managed Metadata Service Application when using a managed navigation let’s proceed to implementing the solution.

The reason why the system “waits” for over 15 seconds on this specific part of SharePoint is caused by corrupt or missing user permissions on the database of the Service Application. As we explained “Managed navigation is using term store for its items, every time a request made to the term store, it will try to look for these users which takes some time until the process gives up.  Therefore causes the delay.”

1. Check if there are “present” Term Store Administrators defined in the Office365 portal by going to the SharePoint Admin center.
2. Select “term store” on the left hand side menu.
3. Check if you see any current users defined or SID’s i.e. s-1-5-21-2522053391-2242016340-1337919630-744690. See the image listed below.

4. If you have identified these unknown SID’s on your environment OR identified other non system service-accounts please feel free to remove the permissions to instantly improve performance. 

Now.. if you are using AD FS and DirSync for providing single sign-on access to Office365 services then stick with me. If for instance while developing and building SharePoint solutions several users (developers or admins) have been granted the “Term Set Administrator” permission. At some point the project finishes and their account will be disabled or deleted at the on-premises Active Directory Services. Standard behaviour is that this deletion synchronizes to the cloud with DirSync. This process succesfully deletes the userobject on all front-end systems of Office365 but NOT correctly on all back-end systems in regarding to the Managed Metadata Service Application. In our case the user’s SID was still present on the Managed Metadata SQL database and therefore first webrequests got stuck on this explicit permission.

The only solution for this is contacting Microsoft Support and request them to delete the SID’s and/or old user objects on the database. Once deleted you will instantly notice great performance improvement on your environment!

Nintex Workflow 2010 issue: Error establishing database connection. SQL Error: 515 VALUE NULL

Today I came across an issue at one or our clients where Nintex Workflow 2010 was up/downgraded. After starting a workflow it threw an correlation ID. After looking up this error in ULS Logging I could find the error message described below:

Error establishing database connection. SQL Error: 515.: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column ‘DatabaseID’, table ‘DEV_Nintex_Workflow4.3.3.dbo.Storage’; column does not allow nulls. INSERT fails.  The statement has been terminated.  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   

 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()    at System.Data.SqlClient.SqlDataReader.get_MetaData()    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)    at Nintex.Workflow.Administration.Database.ExecuteReader(SqlCommand command, CommandBehavior behavior) (Build:2330)

The resolution for this issue is related to either a missing Config or Content Database for Nintex. If you look within Central Admin, you actually have to specify the database to use TWICE. This is due to the fact that Nintex has a “Config” and “Content” database – mostly (usually) within the same SQL database.


SharePoint 2010 connection string to determine farmmember status

Today I came across an issue where I had to determine if a server was sucessfully removed from a SharePoint Farm. When starting Central Admin on the application server it launched the Configuration Wizard asking to join or create a new farm. When launching SharePoint Powershell it returned the message “Farm is inaccessible”. The farm consisted out of mutliple application and webservers. Central Admin on one of the applicationservers didn’t list the server which I had to remove so all looked like it was succesfully removed.

However there is a way to know for sure if a server is succesfully removed from a SharePoint Farm. The solution can be found in the registry of the applicationserver. If it is succesfully removed from the farm you will not be able to find the connection string for it’s SharePoint configuration database.

The key can be found in \\HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure where the key will be named ConfigDb. If there is no key listed you can conclude the server has been succesfully removed from the farm. If there is still a key listed feel free to remove it!

Unable to apply SharePoint Service-Pack or CU due to a SQL mirrored database

I recently encountered an issue while updating one of our clients SharePoint environments. I came across the following error when applying SharePoint Server 2010 SP2 (14.0.7015.1000). However this issue can occur when updating hotfixes or cumulative updates aswell.

The error which PSCONFIG.EXE generated was:

Performing configuration task 3 of 4
Upgrading SharePoint Products…

Failed to upgrade SharePoint Products. An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: The operation cannot be performed on database “<SharePoint_UsageAndHealth>” because it is involved in a database mirroring session. 

ALTER DATABASE statement failed.

Total number of configuration settings run: 3
Total number of successful configuration settings: 2
Total number of unsuccessful configuration settings: 1
Successfully stopped the configuration of SharePoint Products.
Configuration of SharePoint Products failed. Configuration must be performed before you use SharePoint Products. For further details, see the diagnostic log l
ocated at D:\Logs\SharePoint\PSCDiagnostics_7_30_2014_15_23_59_204_130559607.log and the application event log.

As the error decribes it is due to a mirroring process on the SQL database. In my case it was the SharePoint Usage and Health service application database. As Microsofts best-practices describe: IT IS NOT RECOMMENDED to mirror the databases for this service applications.

Solution: The way to get past this issue is to BREAK – and not PAUSE – the database mirroring for the databases. Once this is done the SharePoint 2010 Products Configuration Wizard will succeed updating the Farm as designed. Once the update is succesfully applied the mirroring can be rebuild in SQL.