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')

March 14, 2016

Display error details with custom SharePoint WCF Service


I always keep forgetting how to configure web.config file in order to display verbose error messages thrown by custom WCF services you create for SharePoint (the ones under _vti_bin).


In %PROGRAMFILES%\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\web.config within system.serviceModel\behaviors add:

    <serviceDebug includeExceptionDetailInFaults="true" />

March 4, 2016

PowerShell 4.0 GetEnumerator not working if array variable name length > 20 chars


Was going about my daily PowerShell (4.0) life and run into issue where the following script didn’t output any rows.

$moduleSiteCollections = @{
    'Field Intelligence' = 'fi';
    'News Feeds' = 'nf';
    'Key Developments' = 'kd'
foreach ($sitecoll in $moduleSiteCollections.GetEnumerator()) {   
    Write-Host $($sitecoll.Value)


If the length of the name of the variable holding the array is longer than 20 characters, GetEnumerator won’t return any results, so reduce variable name to 20 characters or less. Or use PowerShell 5.0.

The following script works fine.

$moduleSiteCollection = @{
    'Field Intelligence' = 'fi';
    'News Feeds' = 'nf';
    'Key Developments' = 'kd'
foreach ($sitecoll in $moduleSiteCollection.GetEnumerator()) {   
    Write-Host $($sitecoll.Value)

February 3, 2016

SharePoint New-SPContentDatabase in VSTS Build throws error “String or binary data would be truncated”


Nightly build was running nicely until 3rd December, 2015, after which it started throwing error:

2015-12-08T07:51:03.8157500Z ##[error]New-SPContentDatabase : String or binary data would be truncated.    
    2015-12-08T07:51:03.8157500Z ##[error]The statement has been terminated.    
    2015-12-08T07:51:03.8157500Z ##[error]At E:\Builds\Plaza for SharePoint\Scripts\50_CreateModuleSiteCollections.ps1:36 char:9    
    2015-12-08T07:51:03.8157500Z ##[error]+         New-SPContentDatabase -Name $ContentDatabase -WebApplication $WebApplica ...    
    2015-12-08T07:51:03.8157500Z ##[error]+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    
    2015-12-08T07:51:03.8157500Z ##[error]    + CategoryInfo          : InvalidData: (Microsoft.Share...ContentDatabase:SPCmdletNewContentDatabase) [New-SPConte     
    2015-12-08T07:51:03.8157500Z ##[error]   ntDatabase], SqlException    
    2015-12-08T07:51:03.8157500Z ##[error]    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletNewContentDatabase

when the PS script doing nightly environment reinstall attempted to create SharePoint content database with simple command:

New-SPContentDatabase -Name $ContentDatabase -WebApplication $WebApplicationName

In ULS logs it says things like:

    System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.  The statement has been terminated.
    Unknown SQL Exception 8152 occurred. Additional error information from SQL Server is included below.  String or binary data would be truncated.  The statement has been terminated.
    Exception occured during acquiring a server lock. System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.  The statement has been terminated.


I had all recent Windows Updates installed to SQL and SharePoint.

When I run the exact same script manually in PowerShell window in the same server, it works OK, but when the script is run as a part of nightly build, it fails.

I do not see the initial CREATE DATABASE in SQL Profiler when running New-SPContentDatabase via the Visual Studio Team Services Agent (nightly build script). I see CREATE DATABASE just fine if I run the script manually on the same server.

Issue occurs also if I manually Queue the build during day time.

There are no SQL server or SharePoint maintenance jobs taking place at the time when script is failing.

Build Agent account is the same account I'm using when running the script manually.

Finally after everything seemed OK, I was inclined to think cause is one of these things that occurred on Dec 3rd just before errors started to appear:

  1. Build Agent was updated at that time (cannot test with previous version as it auto-updates, have SR open of this whole issue with VSO support)
  2. Running Configuration wizard (ConfigWiz was also run 30th Nov, and there were no WU SharePoint updates released between 30thNov and 3rdDec, so it shouldn't directly be related to any updates, although ConfigWiz may have made some changes still)
  3. Windows Update KB3112336 (which I cannot seem to uninstall in order to test the effect)

So, I ended up opening service request at VSTS.

After 1,5 months of sending logs to friendly team at VSTS, I got answer that “Based on investigations from our Product team it does appear there were code changes in how VSTS communicate’ s with PowerShell which is breaking your builds. Development team will continue to investigate areas they may have to rework.


Now that I had confirmation VSTS is the culprit, I came to think perhaps good old STSADM would work. AND IT DID!

So, use STSADM -o addcontentdb instead of New-SPContentDatabase:

#this works
& 'C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\BIN\STSADM.EXE' -o addcontentdb -databasename My_ContentDB -url $WebApplicationURL

#this does NOT work
New-SPContentDatabase -Name My_ContentDB -WebApplication $WebApplicationName



Will update here after it is fixed at VSTS or on the Agent and there is no need for workaround.

November 30, 2015

SharePoint Multi-Tenancy: Search returns no hits from tenant site


After setting up on-premises SharePoint 2013 in multi-tenancy mode, and configuring all Service Applications, everything else was working fine, but there was no search results being returned from the tenant site collections.

I could see from SharePoint search logs that the site contents were crawled properly, but even a broad search query such as Size>0 didn’t return any hits.


I had to tweak AutoSPInstaller scripts to enable creating different Service Applications using the -Partitioned or -PartitionMode parameter, and I had missed to set -Partitioned parameter for the Search Service Application Proxy.

I got to the bottom of this by looking at the Properties of the Proxy using PowerShell like this:

PS C:\AutoSPInstaller> Get-SPEnterpriseSearchServiceApplicationProxy

DisplayName          TypeName             Id
-----------          --------             --                                 
Search Service Ap... Search Service Ap... c54d9470-714a-46ff-983c-33422d466a4c

PS C:\AutoSPInstaller> $sp = Get-SPEnterpriseSearchServiceApplicationProxy c54d9470-714a-46ff-983c-33422d466a4c

PS C:\AutoSPInstaller> $sp.Properties

DisplayName                    Value                       
----                           -----
Microsoft.Office.Server.Uti... UnPartitioned

Note the “Unpartitioned”, it should say “UniquePartitionPerSubscription”.


  1. Remove Search Service Application Proxy
  2. Create it using the -Partitioned parameter