October 21, 2016

SharePoint Search TrimDuplicates trims too much


Simple SharePoint search <MANAGER_PROPERTY>:<VALUE> returns only subset of items.

E.g., you have 100 unique customers on a list with same managed term defined in a field, and you try to return all active customers with query “Status:active”, but you only get few hits from search.

I’m using on-prem SharePoint 2013.


Disable TrimDuplicates in your Search Results Web Part settings or in your Query XML by setting <TrimDuplicates>false</TrimDuplicates> ALTHOUGH the items are not duplicate and only thing in common is this one individual value.

Works? Yes. Makes sense? No, this is SharePoint.

October 4, 2016

Log Parser issue with SharePoint claims user name special characters


I'm using Log Parser 2.2 to read IIS logs and import them to SQL Server. Everything works fine except the cs-username field: if it contains special characters, those get messed up during database import. As this is SharePoint web application and it uses external trusted provider, I cannot control nor get rid of the special characters in user names before they are stored in IIS logs.

So, the IIS logs contain hits with user names such as:

    #Software: Microsoft Internet Information Services 8.5
    #Version: 1.0
    #Date: 2016-10-01 07:27:28
    #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) cs-host sc-status
    2016-10-01 07:28:15 GET /_layouts/images/advadd.png - 443 0ǵ.t|claims+idp|12345678 Mozilla/5.0
https://my.site.com/page.aspx my.site.com 200

And after Log Parser imports those to DB, user name has changed to 0ǵ.t|claims+idp|12345678

Same applies if I test outputting it to Data Grid with

    LogParser.exe "SELECT cs-username FROM f:\iislogs\dev3\*.log" -i:IISW3C -o:datagrid

--> I see 0ǵ.t|claims+idp|12345678 instead of 0ǵ.t|claims+idp|12345678.



As IIS Log files are stored using UTF-8, you need to add -iCodepage 65001 to the Log Parser command, like so:

logparser.exe file:AnalyticsQuery.sql -i:IISW3C -o:SQL -database:AnalyticsDB -server:sqlserver -iCodepage:65001

And of course remember to set your table column as nvarchar or other n-type.

August 30, 2016

SharePoint SP.UserProfiles.PeopleManager GetMyProperties Unknown Error


When attempting to query SharePoint User Profile using REST at _api/SP.UserProfiles.PeopleManager/GetMyProperties, you get error

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<m:code>-1, Microsoft.SharePoint.Client.UnknownError</m:code>
<m:message xml:lang="en-US">Unknown Error</m:message>


Go to Central Admin –> User Profile Service Application –> Setup My Sites


Fill in the MySite Host Location field. It will also work if you have centralized MySite host located on another farm.



June 29, 2016

SQL Server: Log Shipping backup job fails


After configuring Log Shipping, the first job that runs is the transaction log backup. However, it keeps on failing with very generic error:

Executed as user: DOMAIN\sqlagent1. The step failed.

No matter what you do, it just won’t log anything else, e.g., configuring the Job step to log to file just ends up with empty log file. Also the log table contains no rows for this specific step.


I finally tried executing the sqllogship.exe command from the failing Job step on the SQL server:

"s:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" –Backup <GUID> -server SQL-1

and it displayed me dialog that .NET Framework 3.5 was required on the server. After installing that from “Add Roles and Features”, the Job finally executed without issues.

April 22, 2016

How to configure SharePoint + SQL on Azure VM to use Internal Load Balancer


While creating SharePoint 2016 MinRole farm in Azure with altogether 14 servers, it took me a while to understand how to configure SQL Server connection on SharePoint servers. In more simple farms without SQL Server AlwaysOn Availability Groups it was straightforward to simply set SQL Alias using cliconfg tool on each SharePoint server. However now that there would be Internal Azure Load Balancer (ILB) via which all traffic would flow from SharePoint servers to SQL Server, this SQL Alias wasn’t working.

Trying to point SQL Alias prodsql to the IP address of the ILB just didn’t work, but as soon as I pointed prodsql to one single SQL Server everything worked nicely.


As many times, the solution was simple, but for some reason I was just too stuck to the “always use SQL server alias” thought to realize, that this time I in fact needed to remove the SQL Alias from all SharePoint servers and instead create DNS A record that points from prodsql to the ILB IP address.

Case closed.

April 6, 2016

SharePoint 2016: Distributed Cache cacheHostInfo null


When setting up MinRole SharePoint 2016 server with 9 SharePoint servers using AutoSPInstaller, one of the Distributed Cache servers was not behaving correctly, and gave error “cacheHostInfo is null”. Also Health checks were saying that “This Distributed Cache host may cause cache reliability problems.“ and referring to the individual server. Fix button on the Services on Server page in Central Admin didn’t do anything.


Basic cache host repair steps worked fine on SP2016 as well, so did the following on the offending server:

  1. Get the Distributed Cache Service Id of the offending server

    Get-SPServiceInstance | where {$_.TypeName -like '*distributed*'} | select Id, Server
  2. Remove the service instance

    $d = Get-SPServiceInstance <GUID>
  3. Finally recreate the service instance


March 17, 2016

New-AzureRmResourceGroupDeployment : A parameter cannot be found that matches parameter name '_artifactsLocationSasToken'


I was trying to deploy new Azure Resource Group using private storage on Azure instead of GitHub (or some other publicly) hosted templates, but run into error when including artifacts.

With the default Deploy-AzureResourceGroup.ps1 (as it comes with Azure SDK, full file can be found, e.g., here), it throws the following error when UploadArtifacts parameter was set to $true.

New-AzureRmResourceGroupDeployment : A parameter cannot be found that
matches parameter name '_artifactsLocationSasToken'.

Same occurred if I execute Deploy-AzureResourceGroup.ps1 via PowerShell ISE or did Deploy via Visual Studio.

At the end of Deploy-AzureResourceGroup.ps1, there is call to Deploy-AzureResourceGroup.ps1 like this:

New-AzureRmResourceGroupDeployment -Name ((Get-ChildItem $TemplateFile).BaseName + '-' + 
((Get-Date).ToUniversalTime()).ToString('MMdd-HHmm')) ` -ResourceGroupName $ResourceGroupName ` -TemplateFile $TemplateFile ` -TemplateParameterFile $TemplateParametersFile ` @OptionalParameters ` -Force -Verbose

@OptionalParameters was correctly populated with HashTable containing values for _artifactsLocation and _artifactsLocationSasToken, but New-AzureRmResourceGroupDeployment is not accepting them.



Adding the missing parameters to azuredeploy.json solved the original error. What I didn’t know was that (obviously), New-AzureRmResourceGroupDeployment passes the parameters to the TemplateFile (usually called azuredeploy.json).

"_artifactsLocationSasToken": {
  "type": "string",
  "metadata": {
    "description": ""
"_artifactsLocation": {
  "type": "string",
  "metadata": {
    "description": ""

Solution 2

One final issue was that I needed to include the _artifactsLocation in the configuration variables of the resources like this to append the SAS Token to the URL so that deployment engine can fetch those from our private Azure Storage Account:

"provisioningPrimaryDCURL": "[concat(parameters('_artifactsLocation')