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 . . .

. . .  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. ---> 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. —> 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& 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& 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.<DownloadAndDeployPackage>d__17.MoveNext()

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:

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.

Sitecore Commerce 8.2.1 MSCS_Admin Database & the Tyranny of SQLOLEDB.1

Disclaimer: this is a journey of discovery and not a manual on database best practices for Sitecore Commerce 8.2.1.

You don’t see a lot of talk about disaster recovery and Sitecore Commerce — getting the regular implementation to work well is enough of a challenge that DR and Sitecore Commerce feels like a mythical Phase 5 of a project with only 4 Phases.

I want to write-up some notes and exploratory digging I did recently on the topic of disaster recover and Sitecore Commerce 8.2.1. You could even consider it a poor man’s option for database high availability that doesn’t incur the licensing costs of SQL Server Always On . . . but it’s probably best if you forget I ever mentioned that. Just because something can be done, doesn’t mean it should be done. I get in trouble sometimes presenting cans when I should just stick to the shoulds, but sometimes there’s real opportunity in those cans so I just can’t resist. Reader: beware.

Enough preamble. To simplify the scope of this post, I’m going to focus on database disaster recovery since it’s distinct for Sitecore Commerce 8 from “regular” Sitecore without Commerce. It’s because Commerce has a decade (or two!?) of COM code and legacy architectures that are way down deep in the Sitecore Commerce 8.2.1 system.

The crux of the challenge I was tasked with addressing was the inability to identify a SQL Mirror as part of a Sitecore Commerce 8.2.1 project. Many customers have used SQL Server database “Mirroring” as the high availability option for Sitecore databases for a long time because it was the only one officially supported by Sitecore. As this documentation explains, only since Sitecore 8.2 has “Always On” been an option for an officially supported Sitecore implementation. I know — many projects are successful with newer SQL Server approaches or RDS on AWS etc, but in my role at Rackspace, we have to walk the line of what Sitecore officially supports from top to bottom to ensure clean lines of escalation in the event of any issue; this is appealing to risk-averse customers, those with aggressive SLAs, etc.

To use SQL Server Mirroring one must identify a failover partner in each of your SQL Server database connections defined in ConnectionStrings.config like:

In Sitecore Commerce 8.2.1, however, this is the interface you have to manage a database connection for the MSCS_Admin database . . . there is a single endpoint (server name) and no provision for a failover partner. It comes down to a limit of the SQLOLEDB.1 provider, I believe. It’s fine for a SQL Server Availability Group listener where you get a service to route requests between the Always On SQL Server nodes, but this Commerce UI is incompatible with SQL Server Database Mirroring:

I set to digging and found some old documentation on a Windows Registry key for Commerce Server 2007 edition and the MSCS_Admin database. I’ll assume you understand the primacy of the MSCS_Admin database for Sitecore Commerce 8.2.1 — if that’s not the case, you can review this material for background, but trust when I explain that MSCS_Admin is the administrative heart of Sitecore Commerce 8.2.1. This SQL query shows how the ResourceProps table in MSCS_Admin stores all the dependent database connections for Inventory, Catalog, Profiles, etc:

Now, the old documentation I found mentions an encrypted registry key named ADMINDBPS that is where Commerce Server Manager, the desktop tool, reads and writes the actual database connection string for the MSCS_Admin database. Since I can’t insert a DB Mirroring Failover Partner into the desktop tool, I figured I could engineer a work around using this registry key as leverage.

The problem, however, is this documentation I was reading was from 2006 and no longer reflected reality. It also mentioned how this approach wasn’t supported by Microsoft and came with every cautionary disclaimer. Sounds like fun, right? The Windows Registry Key schema had changed, but not the overall approach and after doing some digging I found HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CommerceServer was where this newer version of Sitecore Commerce had reorganized the Registry state and that’s where ADMINDBPS was hiding!

The connection string to MSCS_Admin was still encrypted . . . sure . . . but nothing the .Net System.Security.Cryptography namespace couldn’t resolve.

The connection string to MSCS_Admin was still buried in the Windows Registry . . . sure . . . but nothing the .Net Microsoft.Win32 namespace couldn’t resolve.

Here’s the code I used to read this value:

Since we’re getting this value, we might as well just add the set logic too, right?

Yes, let the Registry edits flow through you!

I told you this was a reader: beware type of post.

Taking a step back, remember my original challenge was finding a way to weave support for SQL Server DB Mirroring into Sitecore Commerce 8.2.1 and I found the MSCS_Admin database connection to be my focus? We can wrap the above c# code into a command line tool that can be run in a database failover situation to update the MSCS_Admin database connection automatically — changing to the intended failover partner defined as part of the conventional SQL Server Database Mirroring. We could schedule this c# logic to run once every 30 seconds in Windows Task Scheduler, for example, conditionally changing the MSCS_Admin database connection in the event of a failover scenario. The MSCS_Admin database connection string could update automatically. I’m not going to present this as a viable alternative to SQL Server Always On for many many reasons, but you achieve some semblance of it with robust enough PowerShell.

If you’re scratching your head here because I work with customers who are risk averse or have critical Sitecore SLAs that would still rely on the above Registry hacks, you’re not quite getting my point. I’m sharing this because it’s possible, it’s interesting to know how the guts of Sitecore Commerce 8.2.1 are working, and others may also learn from this. While it’s theoretically an option to introduce some automated failover logic through this method, it’s an uncomfortable hack. Based on my testing, though, it works.

Honestly, this technique is most appropriate in a disaster recovery scenario where a set of database servers are unavailable (and then this can apply whether using SQL Server  Always On or DB Mirroring — doesn’t really matter). Instead of considering this a high availability solution, it’s a DR solution.

I spoke with some on the Sitecore Commerce technical team about this and they agreed this was a bit crazy, but it works (and also I shouldn’t quote them directly). They also pointed out how you don’t have to store MSCS_Admin connections in the Windows Registry and that as part of the PaaS support evolving through Sitecore Commerce there was a “Registry Free” deployment option for Sitecore Commerce 8.2.1 that I didn’t know about for this version. With this technique, you can use a ConnectionString defined like the others for Sitecore (see some details here)

<connectionStrings>
 <add name="ADMINDBPS" connectionString="<your MSCS_Admin connection string" />
</connectionStrings>

I haven’t experimented with the Registry Free deployment for Commerce 8.2.1 but I’d like to see if it avoids the tyranny of the SQLOLEDB.1 provider and would let us add the Failover Partner logic to a connection string. I think this blog post may have a Part 2 . . . but I’m not sure how much further down this rabbit hole it’s worth tunneling.

Sitecore Commerce 8.2.1 and ListManager with EXM

I’ve been engaged on a few more Sitecore Commerce builds (Commerce 8.2.1 still as these have carried over from 2017) and found an interesting wrinkle the other day. At first, it looked like a MongoDB issue as contacts weren’t being properly added to Sitecore ListManager “Lists” for use in EXM, but after scratching beneath the surface it was a lot more interesting. I used a utility sent my way by Sitecore support — it’s a .zip that has a Sitecore 8.2 update-5 specific tool for seeing Sitecore Lists and their status in terms of what’s in MongoDB and what’s in content search indexes (Solr in my case).

The tool made it pretty clear that the data was being stored properly in MongoDB but NOT in the search index (the screenshot below shows “Contacts in index: 3” which is after we corrected the problem — initially the Contacts in index would only ever show 0 and that’s what helped to isolate the problem to Sitecore Content Search):

Another piece of evidence, in the Sitecore UI when we’d try to add a new contact to ListManager we’d see this message:

Please note that contacts in the list are currently being indexed, so not all contacts are available to view at this time. 0 out of 3 contacts are currently indexed.

Once we enabled verbose logging for search and examined the Search.log output, we see messages like this in the logs:

INFO  Solr Query - ?q=(type_t:(contact) AND contact.tags_sm:(ContactLists\:\{B76B0E74-E94D-4EBB-F219-6A347C75520D\}))&start=0&rows=20&fl=contact.contactid_s,contact.identifier_t,contact.firstname_t,contact.surname_t,contact.preferredemail_t,contactscount_tl,_uniqueid,_datasource&fq=_indexname:(sitecore_analytics_index)

I bolded the contact.tags_sm criteria in the query as that turned out to be key. This is the query that Sitecore issues to Solr when trying to obtain contacts for ListManager.

Through considerable trial and error, Solr schema inspection, and just determination (and I think Dana [https://twitter.com/thesoftwarejedi] was the one who finally yelled “bingo” and discovered this), when we run this query directly against Solr, we would find our missing ListManagement contact:s

http://solr-server:8983/solr/sitecore_analytics_index/select?q=(type_t:(contact) AND contact.tags_tm:(ContactLists\:\{B76B0E74-E94D-4EBB-F219-6A347C75520D\}))&start=0&rows=20&fl=contact.contactid_s,contact.identifier_t,contact.firstname_t,contact.surname_t,contact.preferredemail_t,contactscount_tl,_uniqueid,_datasource&fq=_indexname:(sitecore_analytics_index

The contact.tags_tm is bold above, and that was the crux of our challenge.

Sitecore was indexing contacts using tags_tm while Sitecore queries were looking for tags_sm.

In Sitecore config file CommerceServer\CommerceServer.ContentSearch.Solr.DefaultIndexConfiguration.config is a fragment of XML like the following:

<typeMatches hint="raw:AddTypeMatch">
 <typeMatch typeName="idCollection" type="System.Collections.Generic.List`1[[Sitecore.Data.ID, Sitecore.Kernel]]" fieldNameFormat="{0}_sm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
 <typeMatch typeName="textCollection" type="System.Collections.Generic.List`1[System.String]" fieldNameFormat="{0}_tm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />

The typeMatch for typeName=”textCollection” was the issue, along with how it duplicates the mapping for the System.Collections.Generic.List`1[System.String] type — and the many places that were using the textCollection returnType that depended on this typeMatch. I removed the typeMatch from the config file and updated any dependency on textCollection to use stringCollection instead and . . . magic . . . the contacts properly indexed into Solr and the contact.tags_sm criteria would match the new data.

According to Sitecore Support, this is a defect in the way Commerce search indexing is setup and it’s overlap with Sitecore ListManager (EXM in our case). Commerce should probably use a custom configuration section instead of modifying the default index configuration, but we’ll have to wait and see how this is implemented in a future patch or release.

For the time being, I’ve created the following Sitecore patch configuration file to remove the textCollection elements. This is preferable to editing the standard Sitecore configuration files that come with the product and will make for easier Sitecore upgrades or adjustments when (or if?) a true correction for this defect is released by Sitecore:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
 <sitecore>
 <contentSearch>
 <indexConfigurations>
 <defaultSolrIndexConfiguration type="Sitecore.ContentSearch.SolrProvider.SolrIndexConfiguration, Sitecore.ContentSearch.SolrProvider">
 <fieldMap type="Sitecore.ContentSearch.SolrProvider.SolrFieldMap, Sitecore.ContentSearch.SolrProvider">
 <typeMatches hint="raw:AddTypeMatch">
 <typeMatch typeName="textCollection">
 <patch:delete />
 </typeMatch>
 </typeMatches>
 <fieldNames>
 <field fieldName="instocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="outofstocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="orderablelocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="commerceancestornames">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field> 
 </fieldNames>
 <fieldTypes hint="raw:AddFieldByFieldTypeName">
 <fieldType fieldTypeName="catalog selection control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="child categories list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="child products list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="parent categories list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="relationship list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 <fieldType fieldTypeName="variant list control">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </fieldType>
 </fieldTypes>
 </fieldMap>
 <documentOptions>
 <fields hint="raw:AddComputedIndexField">
 <field fieldName="instocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="outofstocklocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="orderablelocations">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 <field fieldName="commerceancestornames">
 <patch:attribute name="returnType">stringCollection</patch:attribute>
 </field>
 </fields>
 </documentOptions>
 </defaultSolrIndexConfiguration>
 </indexConfigurations>
 </contentSearch>
 </sitecore>
</configuration>

The case of the Inventory gotcha with Azure SQL and Sitecore Commerce 8.2.1

I only recently discovered comments to this blog were going into an unmonitored spam folder — my apologies if anybody had their hearts set on a response to their comment!  In scanning some of those comments, I thought a proper response was warranted to one note on a piece I wrote about a hard to find Sitecore Commerce configuration setting.

I started that post by writing:

After recently swapping the backing store from Azure SQL to SQL Server (due to an interesting Inventory gotcha with the Reference Storefront that I’ll maybe share at some other time), I’m finding nooks and crannies of configuration I never knew existed with the Commerce product until now.

This post discusses the interesting Inventory gotcha with the Sitecore Commerce storefront implementation. I should point out this is relevant to Sitecore Commerce 8 and I’ve not tested this specifically with Sitecore Commerce 9, but I assume it’s been addressed in version 9 by either not using the same Reference Storefront for Commerce (in version 9 you’d be steered to an SXA UI instead of the older “Reference Storefront”) or by the general platform improvements in Sitecore Commerce 9.  I guess you can stay tuned for Sitecore Commerce 9 news on this front!

The Problem Scenario

This is IaaS VMs running Sitecore 8 (conventional IIS, not PaaS) and using the Sitecore Commerce 8.2.1 release in a scaled Sitecore environment. Azure SQL was chosen as the database technology for a variety of reasons I need not go into. This implementation used separate Commerce catalogs for CM servers and CD servers, to provide the “publish workflow” behavior Sitecore uses for marketing content; this is accomplished for Commerce via the Staging Service.

One tricky area when using multiple Commerce catalogs is Inventory. A late-breaking customer requirement was the need for inventory updates to flow real-time between content management and content delivery servers, so nobody sees stale inventory data. Don’t you love those late-breaking customer requirements?

This can be handled in different ways, but the method appropriate to this project was to use a single Inventory database that both Sitecore CM and CD environments would reference for inventory data.  Commerce Server Manager presents this in the UI as the “Inventory” resource under the Commerce site:

It’s smart to have separate databases for product catalog and inventory, and we set this solution up to have multiple catalogs (one for CM and one for CD) with a shared Inventory database so that data was always realtime.

The crux of the problem was the mechanism Sitecore’s Reference Storefront uses to coordinate Inventory queries between the two Commerce catalog databases — it’s called Cross Database Query and it’s not supported the same way in Azure SQL as it is in conventional SQL Server.  You can do cross database queries with Azure SQL, just not the way the Reference Storefront for Sitecore Commerce depends on it.

The exception we would see was as follows:

SqlException (0x80131904): Reference to database and/or server name in ‘CFSolutionStorefrontSite_productcatalog.dbo.Habitat_Inventory_InventorySkus’ is not supported in this version of SQL Server.

The above was followed by a fair amount of StackTrace such as:

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3189408
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +753
 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +5042
 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +87
 System.Data.SqlClient.SqlDataReader.get_MetaData() +101
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) +590

You get the idea.

What We Considered

After considering our Sitecore Commerce configuration and confirming it was solid, the exception next had us examining Azure SQL compatibility settings, but that wasn’t the issue.

We determined we could do an import/export of Inventory data.  Maybe the Sitecore publish:end event could push inventory changes to the CD servers, where they store inventory in an XML file and on publish:end:remote we could import that XML data into the Content Delivery servers.

We could remove publishing from the equation and use a scheduled <agent> to move Inventory data around.

What We Actually Did

The import/export options were never going to be truly realtime, at least not sufficiently “real” for our project and their inventory requirements. So we swapped out the database back-end and used SQL Server in IaaS VMs instead. Cross database query worked as expected, and we could use a central Inventory database with multiple Commerce Catalog databases for CM and CD sites. It worked as it should.

This concludes the tale of the interesting Inventory gotcha around Sitecore Commerce 8.2.1 — this should close the loop with the initial post from a few months ago, I hope this helps out somebody else!

Sitecore Commerce 8.2.1 Firewalls & Azure SQL Notes

I know Sitecore Commerce 9 is just around the corner, but I know many projects are under way with the current Sitecore Commerce 8.2.1 and I wanted to share some notes we’ve collected on how to work with the technology.

This is from lessons our team at Rackspace has collected, including some exceptionally talented “sales weasels” who have really dug into the Commerce platform with us to determine perf benchmarcks etc — score 1 for the talented sales weasels ξ(^◒^)ξ !

These two links are the key pillars of online documentation for getting started with the product:

http://commercesdn.sitecore.net/SitecoreCommerce/DeploymentGuide/en-us/index.html

http://commercesdn.sitecore.net/SitecoreCommerce/DevOpsGuide/en-us/index.html

As a complement to the above, I’d like to share the following tips we’ve learned on a few Sitecore Commerce 8.2.1 projects . . .

Firewall Settings

We’ve run into issues with both the Sitecore Commerce Staging software and the Profile system; the exceptions are logged to the Windows Event log or the Sitecore logs and can report an ugly COM error such as the following . . .

Don’t panic

. . . it turns out that the current documentation doesn’t mention Firewall rules for these two services to operate.  After some investigation we realized we needed to open up ports.  Prior versions of Sitecore Commerce have this article, http://commercesdn.sitecore.net/SCpbCS82/SitecoreCommerceDeploymentGuide/en-us/FirewallPorts.html, and it turns out port ranges 5000-5030 and 507 can be important to proper communications in a distributed Sitecore Comemrce build.

Azure SQL

Everyone is price conscious and Azure SQL can save customers a lot of money over conventional SQL Server.  Documentation online for 8.2 states Azure SQL is supported, but the installation documentation doesn’t treat it as a first-class citizen.  There is this great link, http://commercesdn.sitecore.net/SCpbCS81/SitecoreCommerceDeploymentGuide/en-us/c_UsingAzureSQLwithCS.html, with notes on Azure SQL specific provisioning for Sitecore Commerce — but it’s organized under a PaaS installation topic which hides it from our IaaS Sitecore Commerce eyes.

Generally speaking, the Initialize-CSSite script modifies the SQL database schemas and does not work if you use Azure SQL.  The authentication assumptions are invalid (using an App Pool user isn’t going to fly with Azure SQL).

This can be worked around (mostly), but we learned the hard way that the Sitecore Commerce Reference Storefront application is not compatible with Azure SQL in the reliance on Cross-Database queries for certain key operations; in our case, trying to use a central Commerce Inventory Azure SQL database was ultimately untenable.  Azure SQL does offer Cross-Database queries, but the syntax is different and the Reference Storefront code has non-trivial dependencies on the IaaS SQL Server understanding of querying between databases.

There are a lot of nooks and crannies with Sitecore Commerce 8.2.1.  I know everyone is excited about a fresh start with Sitecore Commerce 9 that doesn’t include the legacy COM and thick-client dependencies; this write-up summarizes a few of the Commerce highlights on the horizon.  For now, however, there are Commerce 8.2.1 builds in the wild that require our attention and I hope the above notes help others to be successful.

The Odyssey of Sitecore Commerce Staging’s Reliance on SQL CE 3.5

The Problem

I recently completed a fun journey triaging a set of self-inflicted wounds around Sitecore Commerce Staging.  The rest of Sitecore Commerce 8.2.1 ran as expected except for when I setup the Sitecore Commerce Staging between a CM and CD tier . . . I would get an odd “The system cannot find the file specified” dialog box immediately after trying to start a basic Staging replication project:

And when I say that pop-up appears immediately after clicking the “Start Replication” button, I mean it — we’re talking almost instantaneously.

Background

I’ll pause here just to lay out why one cares about Commerce Server Staging with Sitecore.  The Commerce Staging documentation is on MSDN in a variety of places, and Sitecore is using this sub-system to manage Commerce specific content promotion (such as product catalog changes, promotion codes, etc).  As explained in the summary on CommerceSDN.Sitecore.net :

The role of Commerce Server Staging is to move Commerce Server data between environments or sites.

If the master and web environments are pointing to the same Commerce Server site instance, as soon as data is changed on the master environment, it will be published to web. To ensure that Commerce Server data is not published unexpectedly, it is strongly recommended that you have one Commerce Server site for your Content Management (CM) environment, a separate Commerce Server site for your Content Delivery (CD) environment, and then use Commerce Server [Staging] to move the data.

I added the [Staging] to the final sentence above, as I think it’s pretty key to understanding the topic.

Commerce Server Staging is the vehicle one uses to manage Commerce data the same way we manage marketing data in the rest of Sitecore.  We create and update Sitecore CMS content in a “master” SQL Server database and then promote it to “web” with a Sitecore Publish operation . . . then we create and update Sitecore commerce content via the Sitecore CM and promote changes to the live website via Sitecore Staging operations.  The Sitecore Publish process has been extended with Sitecore Commerce to include Staging, so you can run it as a single integrated process.  Note the “Commerce Server Staging” checkbox included in the Sitecore Publish dialog below:

It’s usually magic and just works — but I was not able to run any type of Commerce Staging operation, so I needed to peel back the curtain and learn a bit more about how to troubleshoot Commerce Staging.

The Solution

This write-up on Monitoring Commerce Staging laid the foundation for my eventual resolution.  Before exploring that angle, however, I reviewed this write-up with some basic pitfalls on Commerce Staging . . . of course there’s this gem about DCOM permissions (& my write-up on applying that setting when the GUID isn’t readily available) . . . Sitecore’s KB site has some suggestions and the Community.Sitecore.net has some suggestions. There are hundreds of nooks and crannies one can investigate here, so it took me some time to discover what was going on.  Hopefully, my adventure can inform others, so here goes . . .

1. The link about monitoring Commerce Staging (https://msdn.microsoft.com/en-us/library/ms961837(v=cs.70).aspx) talks about ways to configure what is logged and how to view the events of the Staging process.  I digested this info and, while the write-up assumes some old IIS configurations, I discovered the C:\Program Files (x86)\Commerce Server 11\Staging directory has two folders of particular interest to me: Data and Events.  The Events folder has .mdb and .ldb files (yes, that’s Microsoft Access) that form a type of localized Event Log for Commerce Staging; the Data folder has a StagingLog.sdf file — .sdf is a format used by SQL Server Compact Edition
2. I opened the Access database and found that it didn’t have any helpful information for my scenario.  My “cannot find the file specified” exception was not in evidence there . . . but there is some interesting meta-data captured in that Access MDB for Staging.  This Events folder appeared to be a dead-end.
3. I used LinqPad to analyze the StagingLog.sdf file (turns out Commerce Staging uses SQL CE 3.5 edition), and I learned two key things:
   • This StagingLog.sdf file was essentially empty — no data was being written to it when I tried to start a Commerce Staging operation
   • I couldn’t use LINQPad on the server running Commerce Staging as I didn’t have the proper SQL CE 3.5 drivers, but when I moved it to my developer machine I could view the .sdf file without issue.  Interesting . . .
4. My next step was to use ProcMon (available at https://docs.microsoft.com/en-us/sysinternals/downloads/procmon) and compare a process capture from my environment that raised the exception with a properly functioning Commerce Staging environment.  This took some trial and error, but filtering on the CSSsrv.exe helped me focus on the task at hand.
   • The properly functioning Staging environment had a lot of activity with the StagingLog.sdf file.  We’re talking hundreds of operations.  The broken Staging environment had none.  Once I tracked ProcMon instruction sets 1:1 between the two environments I found the final piece of evidence I needed:

The highlighted area above, C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\sqlceer35EN.dll entries, were entirely missing from the initialization process of my broken Commerce Staging environment.

The lightbulbs finally went off for me, and I did a quick comparison of the C:\Program Files (x86) directories of the two servers.  There were key components missing from the environment where Staging wasn’t working, and it revolved around SQL Server Compact Edition.  Here’s a highlight showing what obviously wasn’t on the server in question:

Neither MS SQL Server Compact Edition nor MS Synchronization Services were installed to this server.  I did some further research, and learned that the CommerceServer.exe should fold in these elements with any Commerce install, so I needed to do a repair installation with the CommerceServer.exe and that added SQL Server CE and other dependencies necessary to solve the issue.

Our PowerShell that installs the CommerceServer.exe must not have been using a properly elevated account, or this was done with a manual execution of the installer that didn’t use the proper administrative credentials.  Everything worked fine after I completed the repair install of Commerce Server.

There are some additional items to point out here:

• Based on my testing, the CommerceServer.exe may exclude some key Staging dependencies if it’s not run in a proper Administrator context.  I thought it was written in the docs somewhere, but I don’t see it in Sitecore’s documentation on installing Commerce Server .exe — running that EXE in the context of a local admin account is crucial to the success of that process.  I use this account and the shift + right-click -> Run as different user” option when launching the CommerceServer.exe manually, and this has been successful for me.
• If you’re like me, and love the post-script to a good story,  here’s what LINQPad shows for that StagingLog.sdf once you’ve run a couple successful Staging operations:

• I think additional take aways from this write-up are:
  • there’s an MS Access database in C:\Program Files (x86)\Commerce Server 11\Staging\Events that could be a source of interesting diagnostic info on your Sitecore Commerce Staging activities
  • there’s an SQL CE database in C:\Program Files (x86)\Commerce Server 11\Staging\Data that could be a source of interesting diagnostic info on your Sitecore Commerce Staging activities

How a 13 year old archived list serv helped me out with Sitecore Commerce

Sitecore Commerce is an interesting landscape — it’s never a dull moment.  After recently swapping the backing store from Azure SQL to SQL Server (due to an interesting Inventory gotcha with the Reference Storefront that I’ll maybe share at some other time), I’m finding nooks and crannies of configuration I never knew existed with the Commerce product until now.

After I migrated to Azure IaaS SQL Server VMs from Azure SQL, I thought I had everything tidied up.

• Commerce Server Manager references?  ✔
• Sitecore application connection strings?  ✔
• Bootstrap configuration (I posted this gist on manipulating those files to make this easier)?  ✔

I updated the Azure SQL database credentials to prove that I had no lingering connections to Azure SQL.  I encountered an exception at Sitecore start-up related to initialization of the profile service, however, and had to start digging.  CommerceProfileSystemException was the exception type and the stacktrace started as follows:

Exception type: CommerceProfileSystemException 
 Exception message: Failed to initialize profile service handle.
 at CommerceServer.Core.Runtime.Profiles.ProfileContext..ctor(String profileServiceConnectionString, String providerConnectionString, String bdaoConnectionString, DebugContext debugContext)
 at CommerceServer.Core.Runtime.CommerceContextFactory.CreateProfileContext()
 at CommerceServer.Core.Runtime.CommerceContextFactory.get_ProfileContextSingleton()
 at CommerceServer.Core.Runtime.Profiles.CommerceProfileModule.get_ModuleProfileContext()
 at CommerceServer.Core.Runtime.Profiles.CommerceProfileModule.get_ProfileContext()

The Commerce Server Manager encapsulates the connection strings, and I thought I had them all updated to the SQL Server VM equivalents, even going so far as to inspect MSCS_Admin in SQL Server with a query like this:

SELECT [i_ResourceID]
 ,[s_PropertyName]
 ,[s_Value]
 FROM [MSCS_Admin].[dbo].[ResourceProps]
 where f_IsConnStr=1
 ORDER BY 1

While interesting to find where this information is stored (may or may not be permanent, though — tough to tell with Commerce!), this output didn’t shed light on what might be going on, though:

Eventually I stumbled across some 13 year old documentation on Commerce Server discussing updating the ProfileService data source in some detail (http://microsoft.public.commerceserver.general.narkive.com/NPLMLusv/commerce-2002sp3-on-windows-2003-can-t-change-profiles-data-source).  It turns out, this 13 year old solution was completely applicable to my 2017 Sitecore Commerce predicament.

Succinctly, within Commerce Server manager you should do the following:

1. Expand the Commerce Server “Global Resources” node, then “Profiles” node, then “Profile Catalog” node, then “Data Sources” node, and finally expand the “ProfileService_SQLSource” node
2. Click on the Partitions node:
   • 
3. In the right pane, there’s a SQLSource element you right-click and choose “Properties”
4. Select the Partitions Tab, then “Edit” the connection string
5. Make your connection string modifications here.  This is where my elusive reference to Azure SQL was hiding and causing Sitecore to fail to initialize.

The more work I do with Sitecore Commerce, the more I’m appreciating the value of the older documentation targeting previous editions of the product.  The catch is, it’s not 100% relevant to the modern experience with Sitecore Commerce . . . and knowing what is and isn’t applicable to the Sitecore Commerce 8.2.1 world is a challenge.  I think we’re getting there, a little bit at a time!

A note on the Sitecore Commerce DCOM Config Permissions

I’ve been having lots of fun with multiple Sitecore Commerce projects and Azure SQL lately . . . here’s one quick note I can share that might save some hassle for others working in this space . . .

This Sitecore link mentions a DCOM permission to verify in the course of troubleshooting some Sitecore Commerce “Staging” issues.  My problem was that this guid, 7E95698D-CD3C-4C98-93C7-6510C31F7DDF, wasn’t visible in the Component Services treeview under “DCOM Config.”

Sitecore support informed me that they needed to update their documentation to mention that in the absence of that Guid, one should locate the “CSS Replication Server” object in the treeview and proceed from there.

My screen shot shows where the illusive Guid is stored as a property of the CSS Replication Server entry:

I expect Sitecore will update their documentation shortly, so this blog post may have a brief shelf-life in terms of relevancy . . . but if you’re like me and the Commerce platform, any notes are appreciated, so I’ll see if this helps anyone in the community.

Encrypting Sitecore connection strings for Sitecore Commerce, Azure SQL, and beyond

There’s been a lot of Sitecore Commerce on my plate this Summer, and sadly one limitation of using that product for some customers is the requirement for SQL Server authentication instead of Active Directory and Windows Auth; I won’t get into why they need SQL auth at this point, but trust that in many use-cases this is a necessity.

In an effort to always deliver a secured platform for customers, at Rackspace we encrypt the App_Config/connectionStrings.config file to avoid having plaintext usernames and passwords on disk.    This is a link to our Rackspace GitHub “gist” performing such encryption with the ASP.Net tool aspnet_regiis.exe.  The logic is also there to un-encrypt, in case that’s necessary.

You can update the $configLocation variable at the top of the script to point to where your Sitecore installation is located; you then run this script using PowerShell, and you’ll get an output like this.

Once you’ve run the script, your connectionStrings.config file will resemble this:

Before you get too excited, for Sitecore Commerce in the current incarnation, there are several other plaintext passwords on disk in the \CommerceAuthoring\wwwroot\data\Environments and related .json files for both SQL and Sitecore.  The PowerShell I’ve shared doesn’t address those areas.  The Sitecore Commerce documentation does a good job of cataloging where to find these references, at least, but this still leaves a lot to be desired in terms of security.

I’m not going to go too far down this path, since I mostly wanted to post the PowerShell we use to automate SQL Server connection string encryption.  This technique can be useful for a variety of projects, not just for Sitecore Commerce — although this is the use case we’re repeatedly seeing right now.  If I have time, I’ll share some other Sitecore Commerce tips around Azure SQL friendly deployments (Sitecore’s documentation is a decent start, but lacking in some respects).

Here’s the script to encrypt/decrypt those Sitecore connectionStrings.config file:

	<#
	Note:
	– The encyption is specific to each server, so this needs to be run separately on every IIS server
	– ASPNet_RegIIS requires a web.config file to operate, so we have to massage our Sitecore .config into a web.config format it will understand
	Steps:
	1) Copy current Connectionstrings.config to a file named "web.config"
	2) insert <configuration> node surrounding the <connectionStrings> XML
	3) run this new web.config file through aspNet_RegIIS…
	C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" "S:\Sitecore\TEST-CMS\website\App_Config"
	4) take the contents of the — now encrypted — web.config file and pull the information within the
	<connectionStrings>…</connectionStrings> nodes and overwrite what's currently in connectionStrings.config
	#>
	$configLocation = "S:\Sitecore\website\App_Config"
	#this is here only in case you want a back-up, but don't blindly leave a back-up around or it defeats the purpose of encrypting
	#Copy-Item -Path ($configLocation + "\connectionStrings.config") -Destination ($configLocation + "\connectionStrings.PlainText.backup")
	Copy-Item -Path ($configLocation + "\connectionStrings.config") -Destination ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('</connectionStrings>', '</connectionStrings></configuration>') | Set-Content ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('<connectionStrings>', '<configuration><connectionStrings>') | Set-Content ($configLocation + "\web.config")
	#Encrypt
	C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" $configLocation
	$encryptedString = Get-Content ($configLocation + "\web.config")
	$encryptedString.replace('</connectionStrings></configuration>', '</connectionStrings>') | Set-Content ($configLocation + "\web.config")
	$encryptedString = Get-Content ($configLocation + "\web.config")
	$encryptedString.replace('<configuration><connectionStrings', '<connectionStrings') | Set-Content ($configLocation + "\web.config")
	#this is now our XML to inject into the Sitecore connectionStrings.config
	$encryptedString = Get-Content ($configLocation + "\web.config")
	Clear-Content -Path ($configLocation + "\connectionStrings.config")
	Set-Content -Path ($configLocation + "\connectionStrings.config") -Value $encryptedString
	Remove-Item ($configLocation + "\web.config")
	Write-Host "$configLocation\webconnectionStrings.config is now encrypted" -ForegroundColor Magenta
	########################################################################
	# to un-encrypt, run the following from the machine that performed the encryption. ConnectionStrings will be revealed in plain text in a new web.config file
	<#
	$configLocation = "S:\Sitecore\website\App_Config"
	Copy-Item -Path ($configLocation + "\connectionStrings.config") -Destination ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('</connectionStrings>', '</connectionStrings></configuration>') | Set-Content ($configLocation + "\web.config")
	$plainConnectionStrings = Get-Content ($configLocation + "\web.config")
	$plainConnectionStrings.replace('<connectionStrings', '<configuration><connectionStrings') | Set-Content ($configLocation + "\web.config")
	C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -pdf "connectionStrings" $configLocation
	Write-Host "Check $configLocation\web.config for the plain text configuration" -ForegroundColor Magenta
	#>

view raw

SecureSitecoreConnectionStrings.ps1

hosted with ❤ by GitHub