Experience Profile custom facet retrieval error

Question

I was trying to create custom facets in Sitecore 9.0 Update 1. I was able to create one and add a custom tab in experience profile. I fill the custom facet data with the help of a WFFM form. I see the data is being saved to _Shard DB. I get an error while data retrieval.

The error occurs only on a scaled Sitecore Azure environment and the error is visible in logs generated within the reporting web server. On an on-premise environment everything works fine. Also on Azure, if I force CM to also do processing and reporting, everything works fine.

The solution works fine on On-Premise Standalone environment.

I have added the necessary patch files to the include folder. I see the files are patched fine in the show.config. I have the pipeline patch as required.

The error details are as shown below:

Exception: Sitecore.XConnect.XdbCollectionUnavailableException
Message: An error occurred while sending the request.
Source: Sitecore.Xdb.Common.Web
   at Sitecore.Xdb.Common.Web.Synchronous.SynchronousExtensions.SuspendContextLock[TResult](Func`1 taskFactory)
   at Sitecore.XConnect.Client.XConnectSynchronousExtensions.SuspendContextLock(Func`1 taskFactory)
   at sSupport.Controllers.ContactQueryPipelineProcessor.GetContact(Guid contactId, String[] facets)
   at sSupport.Controllers.ContactQueryPipelineProcessor.GetTableFromContact(DataTable rawTable, Guid contactId)
   at sSupport.Controllers.ContactQueryPipelineProcessor.Process(ReportProcessorArgs args)
   at (Object , Object[] )
   at Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args)
   at Sitecore.Cintel.Reporting.ReportingServerDatasource.QueryPipelineDataSource.GetData(ReportDataQuery query)

Nested Exception

Exception: System.InvalidOperationException
Message: The certificate was not found.
Source: Sitecore.Xdb.Common.Web
   at Sitecore.Xdb.Common.Web.CertificateWebRequestHandlerModifier.Process(HttpClientHandler handler)
   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.CreateRequestHandler()
   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.CreateClient()
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.d__37.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Xdb.Common.Web.CommonWebApiClient`1.d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.XConnect.Client.WebApi.ConfigurationWebApiClient.d__4.MoveNext()

The "sSupport.Controllers.ContactQueryPipelineProcessor" is the custom project which is used to get the data from xConnect.

Also, i have the xconnect.collection.certificate value in my reporting server ConnectionString.config with thumbprint and AllowInvalidClientCertificates=true.

The same is true for CM server (which is able to get the custom facet data).

Any help is much appreciated.

Answer 1

Go to your Azure Sitecore Reporting instance and select "Application settings" under the Settings menu. Under "Application settings" on the right, add a new "APP SETTING NAME" called WEBSITE_LOAD_CERTIFICATES as shown in image below. The value will be your certificate thumbprint. It is still hazy why this needs to be done especially since other facet queries work fine without adding this setting, but this will get the functionality working.