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

Advertisements

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:

cannot find the File

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:

StagingPublishDialog

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:

SqlCE

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:

dirs

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:

Linqpad

  • 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: DCOM

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.

Encryption success
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:

Digesting Sitecore Commerce 8.2.1

A whole new take on Sitecore Commerce is hot off the presses and I had an opportunity to dig into it briefly this week.  Taking from the release notes and the documentation, which is actually fairly extensive:

This is Sitecore’s new re-envisioned Commerce product.

“Release number 8.2.1 has been assigned to reflect the compatibility with release 8.2 of the Sitecore Experience Platform (Sitecore XP). However, Sitecore Commerce 8.2.1 is not an update to previous Commerce 8.2 releases, but is an entirely new Commerce product and release.”

I worked on a few Sitecore Commerce implementations a while back, but it had been over a year since I ran a proof-of-concept or even completed the installation.  My background with the permutations of “Commerce” on Windows goes back over 15 years, starting with the Microsoft Site Server product and the initial craze around XSLT rendering HTML output from content engines . . . I remember a horrendous e-commerce project designed with a Commerce Server beta and the “elegance” of XML was a complete productivity killer.  It’s a poor worker who blames their tools, right? 🙂   I digress…

Anyway, the last real work I did with Sitecore Commerce was in 2015 and I recall the installation/configuration process being arduous, with both Web and Desktop elements, lots of security hoops to jump through, COM everywhere, and even registry edits for good measure.

This new 8.2.1 Release installation process is certainly an improvement over what is now considered “Legacy” Sitecore Commerce . . . but standing up a baseline installation to kick the tires will still likely occupy a solid day of your time.  The documentation is good, but not 100% bulletproof because there are so many moving parts.  I know I ended up needing to install some new .Net elements for ASP.NET Core . . . and I needed to install an old .Net framework SDK to get another piece of the puzzle to run on the IIS server. I took notes on what extra steps I needed to perform, but I was using a fairly old Rackspace server image so not particularly applicable to everyone.  A few examples from my notes, however:

  1. Re-install the Default Web Site to IIS (our scripted Sitecore installation cleans out the Default Web Site in IIS, so I needed to add it back in to satisfy an assumption one of the various installers made)
  2. Configure IIS 6 Metabase Compatibility to satisfy a requirement for the Commerce Server installation

It’s these sorts of nuances that I recall from previous run-ins with the Commerce platform Sitecore inherited and now fully owns.  In some respects, not all that much has changed.

On the bright side, however, there are clean new SPEAK applications for working with Commerce data:

threecommerce

To get this far, however, you really have to earn it.  There are eight Sitecore “packages” that must be installed, for example, once you get the base Commerce Server + Sitecore + Commerce Core running . . . oh, and they need to be installed in a specific order that is NOT alphabetical, either:

packages

On the bright side, there is a lot more documentation than I’ve seen before on this set of products.  I worked with Sitecore Commerce at a time when there was essentially no real current information about the product, so maybe I’m satisfied too easily with what is now available . . . but I really found this an area Sitecore has improved upon.

Based on this documentation, I was able to pull out some of Sitecore’s diagrams of the product and compile this single visual of the Sitecore Commerce platform as I understand it for version 8.2.1:

8-2-1-annotation

The above is just consolidated from a variety of pictures and notes contained throughout the official documentation from Sitecore on the subject, but one of the ways I digest a system is by diagramming and scribbling notes as I go through a project.  Maybe others will find it useful, too.