10 April, 2023

Sitecore 9.3 SIF Install - A connection was successfully established with the server, but then an error occurred during the login process.

Back to blogging after a long pending vacation. :)

Back to a project where Sitecore 9.3 is used in an IaaS model. After using containers for local development in Sitecore 10+, setting up the Sitecore 9 in a VM is a misery. 


While setting up Sitecore 9.3 using SIF, I was facing an issue while setting up the login for Shard DBs and installation abruptly stopped. 

A connection was successfully established with the server, but then an error occurred during the login process. 
(provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Microsoft has clear documentation on what to do when this error occurs. 
One of the solution for the local development machine is to enable Trust Server Certificate while connecting to SQL using Management Studio. That worked for Management Studio. 

In my case, none of this solution worked. Uninstalled the SQL Server completely and installed again but the issue was still there. Looking into the SIF json files, I understood that it is using Invoke-Sqlcmd PowerShell command. This command has various options to pass the variables like SQL commands, login credentials and other parameters, etc... 

Documentation on Invoke-Sqlcmd PowerShell command by Microsoft. One of the parameter is to set the flag for TrustServerCertificate.

Since SIF uses the Invoke-Sqlcmd command, it has designed in a way that we can pass additional parameters. In my case, I had to just pass the TrustServerCertificate flag to True for all the tasks related to Invoke-Sqlcmd. With this change, Sitecore installation was successful without Sql connection related errors. 

17 January, 2023

Sitecore PowerShell - All Page Layout Data Report

Recently, for a migration project, I have been mostly working on Sitecore PowerShell to create reports and content exports. One of the ask from the client is to find out the unique set of pages with certain renderings in a template. So, the solution is to get the list of renderings of the page and find out the number of occurrences of each rendering. By which we can plan for bulk content export on those pages alone and leave the rest for manual migration. 

The below PowerShell script will help you to choose a root folder and it will export the data as below. 

You may consider this as a base script, and you should be able to make use of RenderingDefinition properties to group the component based on your need in line 49. Refer line 53 to 73 for the RenderingDefinition properties. 

Once we got the data, we used the Excel Pivot chart to group the results by Template Name and Layout.

20 December, 2022

Sitecore PowerShell - Expand Rich Text Links

First blog after submitting MVP Application 🙂 

In order to transform links in HTML to friendly URLs, you can make use renderField pipeline or Sitecore LinkManager ExpandDynamicLinks() method. 

There are cases where you would need to expand the HTML with friendly URLs as part of the Sitecore PowerShell report. In this article, you can see some examples on how this can be done using PowerShell and also, this can be used as a reference to run other pipelines in Sitecore PowerShell. 

Option 1: Using Pipeline

Option 2: Using LinkManager

Option 3: Custom Method

In case if you have any custom logic, you can add it as a method and call it via PowerShell. 

01 November, 2022

Sitecore Scheduled Publishing Module - Update

The purpose of Scheduled Publish Module is to give the content editor the option to delay the publishing of an item for a future point in time. Thus, a page or a feature that should go live at a specific time can be created and populated in Sitecore and previewed long before it goes live without the risk of an accidental publish before the specific time. Moreover, there is no need for a content-editor to go to Sitecore and manually publish something at an inconvenient hour, e.g. a New Year’s announcement. Scheduled Publish intends to give the content-editor all features of a normal publish with the addition of automation, timing and notifications.

Updated to Sitecore 10.2

This module was built by Hedgehog few years ago and it was not updated to Sitecore 10. I forked it and updated to work with Sitecore 10.2. As part of this task,

  • TDS project has been removed and converted to Sitecore Content Serialization with version 5.1.x. 
  • Updated the .NET version, referenced latest Sitecore nuget packages and fixed build issues.
  • Updated the documentation from an old source to GitHub readme file.
  • Created a Sitecore Package for installing it without the source.
You can view it here in my GitHub repository.

Docker Asset Image Creation

Lately, I was exploring options to create a Docker asset image for this module. Robbert Hock has created an excellent Docker asset image creator script which can intake the Sitecore package and convert it into an asset image. When you run the script, you may face this issue "The SQL provider cannot run with dacpac option because of a missing dependency". You can follow the below steps to fix it. Otherwise, it was super easy to create the image using this script. 

To fix the dacpac missing dependency issue:
  • Install the following applications by downloading it from here
    • Microsoft SQL Server 2012 Data-Tier Application Framework
    • SQL Server 2012 Transact-SQL ScriptDom
    • SQL Server System CLR Types 11.0
  • Register it using gacutil
    • "%ProgramFiles(x86)%\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8 Tools\gacutil" /i "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\Microsoft.SqlServer.TransactSql.ScriptDom.dll

Docker Asset Image:

For the Sitecore Scheduled Publishing module, the Docker image has been published to DockerHub and tagged it for Sitecore 10.2.

Image repository: nehemiah/sitecore-scheduled-publish

Tags: 10.2-1809

Dockerfile instructions:
  • cm:
COPY --from=<sitecore_scheduled_publishing_image> \module\cm\content .\


DacFx Issue:

30 September, 2022

Find and Delete xDB Contact Outliers and Automate it via API

This article is about finding and deleting xDB contact outliers automatically using Analytics Database Manager and PowerShell. Everyone talks about Sitecore SmartHub nowadays but huge number of clients are still with Sitecore XP and may face issues with xConnect performance. 

Let's talk about xDB contacts and Interactions. 

xDB Contacts

A contact represents an individual who interacts with or may potentially interact with your organization. Contacts are represented by the Sitecore.XConnect.Contact class, and are uniquely identified by ID (of type Guid) within the xDB. ref


An interaction describes any point at which a contact interfaces with a brand, either online or offline. Examples of interactions include:

  • Purchasing a something from a physical store
  • Using an app
  • Browsing a website
  • A phone conversation

Xdb Contact Outliers

Outliers - contacts with abnormal number of interactions. 

2022-05-27 05:05:30.910 -07:00 [Error] ["XdbContextLoggingPlugin"] XdbContext Batch Execution Exception
Sitecore.Xdb.Collection.Failures.DataProviderException: Execution Timeout Expired. 
The timeout period elapsed prior to completion of the operation or the server is not responding. ---> 
System.Data.SqlClient.SqlException: Execution Timeout Expired. 
The timeout period elapsed prior to completion of the operation or the server is not responding. ---> 
System.ComponentModel.Win32Exception: The wait operation timed out

If you have used Sitecore xConnect, you might have faced above error sometimes. Kelly Rusk has an excellent post on how excessive interactions can be prevented.

As per Sitecore support, contacts with >1000 interactions can be considered 'excessive'. so with this large number of interactions I suspect it was causing the SQL performance issues. 

There is a module called Sitecore Analytics Database Manager (ADM) which allows performing various operations with collection database:

  • Removing contact and interactions data.
  • Viewing database statistics.
  • Rebuild calculated facets.
As per the documentation, the current version of ADM has the following functionalities:

  1. Remove all data for contacts that do not have interactions after the specified date or for date range.
  2. Remove only interactions after the specified date or for date range. Rebuild of all calculated facets is triggered automatically for all contacts when the process finishes. Contacts are not removed in this case.
  3. Rebuild the selected calculated facets.
  4. Remove all data for a specific contact defined by the contact id or source and identifier.
  5. Check if the contact with specific id or source and identifier exists.
  6. Retrieve the xDB statistics.
  7. Retrieve xDB Index statistics.
  8. Find outliers in the database.
Once we install the ADM module, there is a set of APIs available to automate the xDB maintenance. We can use a PowerShell script to call this API and schedule the deletion of xDB contact Outliers. 

  1. Authentication:

    In order for calling any API, we need to authenticate the request. To get the access token, we can enable non-interactive client login. We can refer this article to enable it in Identity server. Once enabled, we will get Client_ID and Client_Secret. With that, we can call login API to get the Access Token.

  2. Generate Latest xDB Outliers List

    Using this API, xDB outliers list can be generated. It is a task and with this API, you can let ADM start this process.  

    This process may take minutes to hours based on the size of the xDB. It is better to start this task and we can get the status of the task using another API. Before we run the 3 step, we can execute the below API to get the status and if it is completed, we can start the next step. 

  3. Delete the Outliers

    In this step, there is an API to get the Outliers. Once we get the Outliers, we can call another API to delete the contact with the Outliers contact IDs. Deletion is also a task so once we execute the delete request, we need to check if the task is completed using the 3rd API which will give the status of the deletion request. Once it is completed, we can repeat the whole process. 

    Get the Outliers:

    Delete the Contact by IDs

    Check if the Deletion request is completed

    Repeat this step till we clear the contacts with abnormal number of interactions. This process can give us an overview on how we can find the Outliers using ADM and then delete it. 

blockquote { margin: 0; } blockquote p { padding: 15px; background: #eee; border-radius: 5px; } blockquote p::before { content: '\201C'; } blockquote p::after { content: '\201D'; }