Sitecore 9 CD Servers May Assume “Master” EventQueue by default

Here’s a quick one, and I wish it was a clever April Fools’ joke but it isn’t.

Sitecore support recently confirmed a bug for me in Sitecore 9.0 update-2 (may be present for other versions in the Sitecore 9 space — I’m unsure). A Sitecore CD environment might report exceptions assuming a “master” database endpoint like this:

Unknown connection string. Name: 'master'

For the stacktraces I’ve seen with this issue, it’s something like the following:

ERROR One or more exceptions occurred while processing the 
subscribers to the 'publish:end:remote' event.

In the days of Sitecore 8 (I guess those are the olden times now?), we’d adjust our SwitchMasterToWeb.config to address the EventQueue configuration that assumes the presence of a “master” database. For what it’s worth, I always thought Kam Figy’s was the most thorough at https://gist.github.com/kamsar/8096336f141c0e5e97b3.

In the case of this Sitecore 9 issue, we could brew up our own SwitchMasterToWeb.config patch file or work around the issue using role:require logic on the <eventQueue> node in Sitecore.config file. I thought the Sitecore 9 role:define features were designed to make the SwitchMasterToWeb.config obsolete, but if we don’t want to alter Sitecore’s default sitecore.config file, we may need a SwitchMasterToWebForSitecore9.config. History is cyclical!

Here’s the fragment of sitecore.config I’m referring to:

<eventQueueProvider defaultEventQueue=”core”>

<eventQueue role:require=”ContentManagement or Standalone” name=”master” type=”Sitecore.Data.Eventing.$(database)EventQueue, Sitecore.Kernel”>
<param ref=”dataApis/dataApi[@name=’$(database)’]” param1=”$(name)” />
<param hint=”” ref=”PropertyStoreProvider/store[@name=’$(name)’]” />
</eventQueue>

Advertisements

Some notes on the Sitecore Commerce PaaS Marketplace provisioning

I’m on the hook to do a session at the Manchester, NH Sitecore User Group next month and the topic will be a deep-dive into diagnostics for Azure PaaS Sitecore implementations. This is an absolutely huge surface area for a one hour talk, so I have a lot of flexibility in where to focus. One decision I made was to use a Sitecore Commerce PaaS sandbox as the foundation for the session. We’ll dive into the Azure Redis, Azure SQL, Azure Search, Azure App Services, and all the other PaaS goodness that comprise this Sitecore 9 Commerce in PaaS implementation I create.

I haven’t worked with the Azure Marketplace for a Sitecore PaaS implementation in about a year, so I worked through that interface to create a shiny new Sitecore Commerce environment . . .paas

. . .  it’s a straight-forward process and after a few minutes I was patiently waiting for my new Azure PaaS environment to be ready.

Issue #1

Then some bad news: Azure’s interface reported that the Sitecore PaaS deployment had failed after about 30 minutes of work.

DeploymentFailed:  At least one resource deployment operation failed. Please list deployment operations for details

I’ll post the full error at the end of the post, in case it helps a web searcher to find the resolution. For brevity, I scanned the details of the failure in JSON format and picked out this nugget:

ERROR_SQL_EXECUTION_FAILURE. ---&gt; System.Data.SqlClient.SqlException: Incorrect syntax near 't'.

I’ve done enough Sitecore and PowerShell automation for this to set off familiar alarm bells. This looked like a character encoding or invalid escape sequence issue — like where an & (ampersand) or another “special” character was invalidating the script.  Several of the inputs in the Sitecore Marketplace forms are password fields, and like a good computer worker I used a strong password (like from https://www.grc.com/passwords.htm for example). One of my special character’s was a single quote (‘), to it really had me wondering about the password fields.

Regardless, I tried a second time, thinking maybe my Azure resources had a transient issue; I picked different Azure regions for the resources, because occasionally I’ve seen the same operation succeed with one Azure region but fail for another. Unfortunately, this second attempt also failed with the same type of exception.

For my third attempt, I tried using a weak password, just a simple alphanumeric like “123abc”– and the provisioning succeeded. It took almost 2 hours to run to completion, but I didn’t hit any failures. I was able to update the passwords to something more secure afterwards, since the automation creating the PaaS resources is a one-time execution, so I don’t have to live long term with the rather weak password.

I reached out to Sitecore Support and they confirmed it’s a defect they’re tracking under reference number 320324. I suggested the put a bit of validation in the marketplace module, then, because surely everyone is running into this?

Issue #2

The next issue I encountered was the Sitecore Commerce Catalog wasn’t properly bootstrapped into the PaaS environment. The Sitecore CM showed no catalogs in any of the usual places:

Sitecore support confirmed this behaviour and explained it’s an oversight of the installation routines. In the Azure Marketplace setup, I correctly selected the sample Habitat catalog with the SXA components etc. To fix this problem, I needed to “clean an initialize” the environment myself to include the sample catalog. With IaaS deployments, this magic is handled by SIF routines (go down that rabbit hole at https://github.com/CommerceMinion/Sitecore-Commerce-v902-Scaled-Installation if you’re brave).

These two blog posts were particularly helpful as I worked through the manual PostMan initialization process:

  1. https://tothecore.sk/2018/07/25/setting-up-development-environment-with-postman-and-sitecore-experience-commerce-sxc-9/
  2. https://naveed-ahmad.com/2018/02/21/sitecore-experience-commerce-xc9-getting-started-with-postman/

Many thanks to the authors https://tothecore.sk/about/ and https://naveed-ahmad.com/!

Finally, from Issue #1, here’s the full details of the provisioning failure message related to the password:

{“code\”: \”Conflict\”,”message\”: \”{\\r\\n “status”: “failed”,\\r\\n “error”: {\\r\\n “code”: “ResourceDeploymentFailure”,\\r\\n “message”: “The resource operation completed with terminal provisioning state ‘failed’.”,\\r\\n “details”: [\\r\\n {\\r\\n “code”: “Failed”,\\r\\n “message”: “Package deployment failed\AppGallery Deploy Failed: ‘Microsoft.Web.Deployment.DeploymentDetailedClientServerException: An error occurred during execution of the database script. The error occurred between the following lines of the script: “1” and “43”. The verbose log might have more information about the error. The command started with the following:\”declare @ApplicationName nvarchar(256) = ‘sitecore”\ Incorrect syntax near ‘t’.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘sql’ has already been declared. Label names must be unique within a query batch or stored procedure.\Unclosed quotation mark after the character string ‘\ ) from (select @Salt as [bin] ) T \execute [dbo].[aspnet_Membership_SetPassword] \ @ApplicationName\ ,@UserName\ ,@EncodedHash\ ,@EncodedSalt\ ,@CurrentTimeUtc\ ,@PasswordFormat\’. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE. —&gt; System.Data.SqlClient.SqlException: Incorrect syntax near ‘t’.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘sql’ has already been declared. Label names must be unique within a query batch or stored procedure.\Unclosed quotation mark after the character string ‘\ ) from (select @Salt as [bin] ) T \execute [dbo].[aspnet_Membership_SetPassword] \ @ApplicationName\ ,@UserName\ ,@EncodedHash\ ,@EncodedSalt\ ,@CurrentTimeUtc\ ,@PasswordFormat\’.\ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\ at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\ at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean&amp; dataReady)\ at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)\ at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean&amp; usedCache, Boolean asyncWrite, Boolean inRetry)\ at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\ at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\ — End of inner exception stack trace —\ at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\ at Microsoft.Web.Deployment.DBConnectionWrapper.ExecuteSql(DBStatementInfo sqlStatement, DeploymentBaseContext baseContext, Int32 timeout)\ at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)\ at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source, Int32 position, DeploymentSyncContext syncContext)\ at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentObject destParent, DeploymentObject sourceObject, Int32 position)\ at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\ at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\ at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)\ at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable, Nullable`1 syncPassId, String syncSessionId)\ at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)\ at Microsoft.Web.Deployment.WebApi.AppGalleryPackage.Deploy(String deploymentSite, String siteSlotId, Boolean doNotDelete)\ at Microsoft.Web.Deployment.WebApi.DeploymentController.&lt;DownloadAndDeployPackage&gt;d__17.MoveNext()

OPTIONS and PUT Verbs for the Sitecore Publishing Service

I’ve been involved with a lot of implementations of the new Sitecore Publishing Service lately and they’re starting to all run together. Before this episode fades too far into history, I want to record what I learned in case it’s useful to others (or myself!) at some point.

I’m going to skip all the preamble about .NET Core and how the new Publishing Service is designed to be lean and mean . . . that’s covered adequately in other places by now. I’ll jump right into this scenario where the Publishing Service was installed but all publishing operations would fail from the Sitecore Content Management server. It seemed like the CM wasn’t able to reach the .NET Core Publishing Service endpoint, but I confirmed the following were all in place:

  1. the host file included an entry for 127.0.0.1 pointing to Sitecore.Publishing.Service
  2. the Publishing Service was hosted through IIS with the Sitecore.Publishing.Service binding
  3. http://sitecore.publishing.service/api/publishing/operations/status responded with the expected JSON response “status 0” — so I concluded the service itself was OK

The Sitecore logs did have this cryptic “NotFound Not Found” exception, which was all I had to go on at first:

ERROR [Sitecore Services]: HTTP POST
URL http://cm.website.com/sitecore/api/ssc/publishing/jobs/0/ItemPublish

Exception System.AggregateException: One or more errors occurred. ---> Sitecore.Framework.Publishing.Client.Http.HttpServiceResponseException: The remote service encountered a problem processing the request:
NotFound Not Found
System.Net.Http.StreamContent
   at Sitecore.Framework.Publishing.Client.Http.JsonClient.<SendRequest>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

I envisioned a frustrated IIS server yelling “NotFound Not Found” — and, actually, that wasn’t so far from the truth. To discover the truth, I needed Fiddler.

There’s a ton of API calls going on when Sitecore CM servers interact with the Publishing Service, so Fiddler was the way to get a handle on all that communication. Sitecore has a KB article on how to wire up Fiddler to your Sitecore implementation, which is handy, and I put it to good use.

It was also necessary to dial up the verbosity of the Publishing Service instrumentation so we can see the full picture. One does this through the \config\sitecore\sc.logging.xml file under the Publishing Service installation. Set the filters like this . . .

 <Filters>
<Sitecore>Debug</Sitecore>
<Default>Debug</Default>
</Filters>

. . . and restart the .NET Core service to make sure these take effect.

Fiddler can provide an overwhelming volume of information; imagine hundreds of entries like the following:

Fiddler1

One technique is to carefully clear the Fiddler traces until just before you interact with the browser to perform your action, in my case just before I press the final Publish button in the Sitecore CM environment. I still had a lot of noisy Fiddler data to ignore, which I’ll remove from the screenshot below, but finally it revealed this red entry meaning failure.Fiddler2

And if you click on the details of that Fiddler trace you can inspect all the particulars. In this case it was pretty obvious the HTTP Verb Put wasn’t allowed here:

Fiddler3

The fix was fairly complicated because there are good reasons for servers to block HTTP Verbs that aren’t in use, so we had to make the case PUT was required for the Publishing Service to work (and then add HTTP Verb blocking at a different layer of the implementation). We also found the OPTIONS HTTP verb was crucial for Publishing Service work using the same Fiddler technique described above. We ended up needing to permit both those 2 additional HTTP Verbs to enable proper Sitecore Publishing Service activity.

This isn’t really the final way to permit those verbs in a real production implementation, but it’s close enough for the purposes of this blog . . . check out the IIS Request Filtering options we needed to explicitly permit for this CM server to communicate to the Publishing Service running on the same VM host:

Fiddler4

Sitecore’s had API service layers that depended on these HTTP Verbs for many years, so I shouldn’t be surprised to find the Publishing Service is also making use of them. I just never connected the two together. This customer, too, is very security conscious and features are thoroughly locked-down unless you explicitly enable them. This Sitecore 8 project isn’t otherwise using the Sitecore Sevices Client, for example, or the Item API. The good news is these PUT and OPTIONS exceptions are only internal to the system and not opened up outside to the public internet thanks to firewalls and other networking.

I will also note that we briefly explored the idea of customizing the HTTP Verbs the Publishing Service made use of  . . . but you can imagine threading the needle of dependencies through that stack of libraries would be a very tough challenge.

New Sitecore 8.2 & Sitecore 9 Security Patch

I guess I’m on a security hardening binge for 2019, since I’ll share a hot-off-the-presses security hardening measure from Sitecore today. I don’t want to say too much about the vulnerability, but this article explains in general terms and applying it to all Sitecore server roles for version 8.2 through any current 9 releases is emphasized as the best practice. I’d do it at the next opportunity.

I created a gist with the PowerShell necessary to apply this patch, just update line #8 with the path to your Sitecore website:

Our team has internal automation taking the above a bit further and using another layer of abstraction, and that’s secret Rackspace sauce I won’t share publicly,  but the snippet above should have your environment patched in just a few seconds.

One of the key elements to the patch involves an update to the /sitecore/admin/logs.aspx page which, if you dig into it, reveals a grip-load of additional C# validation logic and other stuff going on . . .

secpatch

There’s a lot to unpack in there if you’re curious, but suffice it to say that Sitecore’s keeping all your bases covered and isn’t trusting user input (using a broad interpretation of that principle).

Sitecore Commerce security hardening note

Let’s start the New Year off with a fun Sitecore Commerce note. Using the latest Sitecore Commerce available today, that is running Sitecore 9.0 update-2 with Sitecore Commerce update-3 (you have to cross-reference https://dev.sitecore.net/Downloads/Sitecore_Commerce.aspx and https://kb.sitecore.net/articles/804595 to really sort this out), we’re applying routine security hardening.

Now that Sitecore is truly built on a hybrid of “plain” .Net and .Net Core, this security hardening effort is more nuanced.

Sitecore is still updating their documentation for the Sitecore 9 space and one can end up at dead-ends like https://doc.sitecore.com/developers/90/platform-administration-and-architecture/en/security-guide-251908.html that leads you over to the .Net Framework documentation when there are better notes with 100% relevancy to Sitecore elsewhere on the Sitecore site. I persevered and eventually found Sitecore’s updated information like this on the hash algorithm https://doc.sitecore.com/developers/90/platform-administration-and-architecture/en/change-the-hash-algorithm-for-password-encryption.html. Still, this documentation overlooks the .Net Core details and given that this Sitecore Commerce project we’re working on will use the latest and greatest, we had to do our own research.

Fortunately, we have some history with this having published https://developer.rackspace.com/blog/Updated-Security-Hardening-For-Sitecore-8.2 or earlier versions going back several years. The PowerShell we’ve used for ages to automate this work, however, wasn’t going to cut it with this new Commerce and .Net Core dimension:

snippet

Instead, we need to do something like this to update the JSON configuration for the Sitecore Identity Server. While you could get fancy and parse the JSON, I used a more direct replace approach to knock this out quickly:

$siteNamePrompt = Read-Host "enter Identity Server website name"
$site = get-website -name $siteNamePrompt
$appSettingsPath = "{0}\wwwroot\appsettings.json" -f $site.physicalPath
Get-Content $appSettingsPath).replace("""PasswordHashAlgorithm"":""SHA1""},", """PasswordHashAlgorithm"":""SHA512""},") | Set-Content $appSettingsPath

The end result is  that SitecoreIdentityServer\wwwroot\appsettings.json file needs an updated PasswordHashAlgorithm value:

        “IDServerCertificateStoreLocation”: “LocalMachine”,
“IDServerCertificateStoreName”: “My”,
        “PasswordHashAlgorithm”: “SHA512”
}

Given the distributed nature of Sitecore 9 with Commerce, I think a discrete change like this just for the IdentityServer doesn’t warrant a lot of effort to integrate into the bigger security hardening Powershell script we use. It may be worthwhile to just update SIF at this point instead of applying security hardening after the Sitecore installation is complete. We’re also talking about SIF extension modules to run this type of logic after SIF is complete. For now, I’ll probably just keep this note handy for the foreseeable future and see whether Sitecore integrates the security hardening guidance directly into SIF in a future release (hint hint!) — or, over time we may collect a set of these best practice adjustments that deserves more effort to automate into a scripted deployment. For now, I think I’ve taken it as far as it deserves.

How I Add Custom Sitecore Publishing Service Targets

At this point, I think I’ve installed, configured, or customized the new Sitecore Publishing Service at least a dozen times for various projects. Sometimes it’s on PaaS, sometimes on IaaS . . . I’ve used a variety of different versions depending on the compatibility matrix (see below as of Dec 16, 2018):

PubSvcVisual

I’m going to skip all the preamble about how the new Sitecore Publishing Service works, about .Net core being the new hotness, why this component can be a great addition to many distributed Sitecore implementations, etc — smart people have written a lot about this already. For example, check out Stephen Pope’s no-holds-barred look at the Publishing Service at http://www.stephenpope.co.uk/publishing or Jonathan Robbins has a nice overview piece at https://jonathanrobbins.co.uk/2016/09/02/setting-up-sitecore-publishing-service/.

I’ve learned a good bit from all the iterations of working with the component and I think consistently the most error-prone part of the setup is aligning any additional custom Sitecore publishing targets one is using in an implementation. This write-up from Geykel Moreno at AlphaSolutions has all the good information, but it’s not as easy to follow because it doesn’t post a comprehensive sc.publishing.xml file — it took a bit of trial and error for me, so to simplify for posterity I’m going to share a reference sample Gist at https://gist.github.com/grant-killian/d2fe8d3e89c5d7b15f47464dd1809d62 that includes 2 additional custom publishing targets. I’ve inserted XML comments for the 3 locations one must update in the config\sitecore\publishing\sc.publishing.xml file:

  1. You need to add your ConnectionString entry for each database to the Publishing/ConnectionStrings XML
  2. You need to add your Services/DefaultConnectionFactory/Options/Connections XML definition for each custom target
  3. You need to add entries for each target to the StoreFactory/Options/Stores/Targets XML that will include the GUID of the Sitecore item that defines each publishing target, along with the Name of the item and additional details

Here’s the gist with the full XML for reference:

Sitecore artifact table patch config

I’ve patched EventQueues several times through the years, so I saved this Gist to make it easier for future opportunities.  There’s not much new to share in terms of introducing why one does this, refer to this blog about Sitecore artifact tables (or the old reliable Sitecore CMS Tuning Guide). You also have to take care around the order of configuration file processing, so zzzzzArtifactTableRetention.config this sucker if you really must 🙂

Here’s the Gist – https://gist.github.com/grant-killian/ffa1e84770b10a90e2454e241986b911  and here’s the expanded XML: