Sitecore connecting to MongoDB using SSL

After configuring valid certificates to configure MongoDB for SSL/TLS it is time to establish the communication from Sitecore to MongoDB, and unfortunately Sitecore does not communicate with Mongo using SSL Out of the box.

I found a good article that explain how to achieve it, however, I don’t have any experience on it and had to ask for help to my colleague Leonardo Faggiani

Our first successful attempt was using a PFX file that requires a password but the problem was

Nobody wants to expose password in config files, specially for a PFX file!

So, we started thinking about using the Windows Certificate Store to retrieve the certificate! And in order to accomplish that, we need to extend UpdateMongoDriverSettingsProcessor class to update Mongo Driver Settings with the SSL certificate.

public class EnableSSL : UpdateMongoDriverSettingsProcessor – This method allow users to turn on/off the SSL settings.

private bool UseSSL()
        {
            string value = Settings.GetSetting("UseSSL");
            return !string.IsNullOrEmpty(value) && Convert.ToBoolean(value);
        }

Self-explaned method
private StoreName? FindStoreName()
        {
            StoreName storeName = StoreName.My;
            try
            {
                storeName = (StoreName)Enum.Parse(typeof(StoreName), Settings.GetSetting("SSLCertificateStoreName"));
            }
            catch (Exception e)
            {
                Log.Error("Error loading store certificate: Settings.GetSetting(SSLCertificateStoreName) " + e.Message, this);
                return null;
            }

            return storeName;
        }

The Certificate Store must be opened in order to find the certificate.

        private X509Certificate2 FindX509Certificate2()
        {
            StoreName? nullableStoreName = FindStoreName();
            if (nullableStoreName == null) return null;

            X509Certificate2 firstCertificate = null;

            StoreName storeName = (StoreName)nullableStoreName;
            X509Store store = new X509Store(storeName, StoreLocation.LocalMachine);

            try
            {
                store.Open(OpenFlags.MaxAllowed);

                var certificates = store.Certificates.Find(X509FindType.FindByThumbprint, Settings.GetSetting("SSLCertificateThumbprint"), false);
                firstCertificate = certificates.Count > 0 ? certificates[0] : null;

                if (firstCertificate == null)
                    Log.Warn("Cannot find certificate with thumbprint " + Settings.GetSetting("SSLCertificateThumbprint"), this);
            }
            finally
            {
                store.Close();
            }

            return firstCertificate;
        }

Overridden method join all the pieces

        public override void UpdateSettings(UpdateMongoDriverSettingsArgs args)
        {
            if (UseSSL())
            {
                X509Certificate2 x509Certificate2 = FindX509Certificate2();

                if (x509Certificate2 == null) return;

                args.MongoSettings.SslSettings = new SslSettings();
                args.MongoSettings.SslSettings.ClientCertificates = new[] { x509Certificate2 };
                args.MongoSettings.SslSettings.CheckCertificateRevocation = false;
                args.MongoSettings.SslSettings.EnabledSslProtocols = SslProtocols.Tls12;
                args.MongoSettings.UseSsl = true;
            }
        }

Finally, we need a .config file in order to add those configurations and push the code in the Sitecore’s pipeline

<configuration xmlns:patch=”http://www.sitecore.net/xmlconfig/“>

  <sitecore>

    <settings>

      <setting name=”UseSSL”>

        <patch:attribute name=”value”>true</patch:attribute>

      </setting>

      <setting name=”SSLCertificateStoreName”>

        <patch:attribute name=”value”>My</patch:attribute>

      </setting>

      <setting name=”SSLCertificateThumbprint”>

        <patch:attribute name=”value”>33d567ffc26697605c31ebd4bd87c7254128f049</patch:attribute>

      </setting>

    </settings>

    <pipelines>

      <updateMongoDriverSettings>

        <processor type=”Custom.MongoDriver.EnableSSL, Custom.MongoDriver” />

      </updateMongoDriverSettings>

    </pipelines>

  </sitecore>

</configuration>

Once we’ve applied it to the Sitecore installation, it didn’t work from beginning and after couple hours troubleshooting here’s what we found to solve it

The Solution

Open Certificate Store

1. Open a Command Prompt window, and type mmc then press Enter
2. On the File menu, click Add/Remove Snap In

mmc-add

3. Double click Certificates

double-click-certificates.PNG

4. Select Computer Account, and click Next

computer-account

5. Select Local Computer, and click Finish then click OK to exit the Snap-in window

local-computer

Manage Private Key Permissions

Assuming you have the private key installed already, and Certificate Store still open, please follow the steps below

1. In the left pane of MMC, expand Certificates (Local Computer) node, expand the Personal node, and then select the Certificates subfolder

personal-certificates-mmc.png

2.  In the right pane, look for your certificate – in my case it is NLCVD2LAP @ Valtech – right-click in the certificate, choose All Tasks, and then choose Manage Private Keys.

manage-keys-certificate

Permissions window shows up, and you have to add the user that is running the Application Pool of your Sitecore instance which could be either ApplicationPoolIdentityNetworkService or a special account just for that.

3. Click Add in the Permissions window, and let’s grant privileges to our Application Pool user

application-pool-user

Please note that in my case the Sitecore was running using the ApplicationPoolIdentity using an ApplicationPool named sc82u3

4. Ensure the permissions are Full Control and Read for testing purposes only, then click Ok

permissions-certificate

Once we have done the permission stuff, the solution provided by Leonardo, started to work right away!

I hope you liked it, and thanks for reading!

And I’ll see you on my next post!

Advertisements

Using Valid Certificates to Configure MongoDB for SSL/TLS

I was told to configure MongoDB for SSL/TLS for a production environment which means I would have to use VALID CERTIFICATES!

For production use, your MongoDB deployment should use valid certificates generated and signed by a single certificate authority.

And as I wanted to go fancy, I choose to setup MongoDB with Certificate Validation that requires the creation of two .pem files – one to use as PEMKeyFile and as CAFile parameters in my Mongo configuration file.

Accordingly to MongoDB’s tutorial page, here is what you need to know about them

  • PEMKeyFile with the name of the .pem file that contains the signed TLS/SSL certificate and key.
  • CAFile with the name of the .pem file that contains the root certificate chain from the Certificate Authority.

The first thing to do is to work with the valid certificate you have, usually a PFX format which contains the Public Key and the associate Private Key.

Exporting PFX

Working with certificates sometimes is tricky, I’d recommend to use DigiCert® Certificate Utility for Windows which I use on my daily basis and it is a time-saving tool! Or you can use OpenSSL – that is another GREAT tool.

Moving on… assuming you already downloaded, executed DigiCert Certificate Utility, and that your PFX is already installed in your machine, let’s continue

1. In the DigiCert Certificate Utility for Windows©, click SSL (gold lock), select the certificate that you want to export as a .pfx file, and then click Export Certificate.

1

2. In the Certificate Export wizard, select Yes, export the private key, select key file (Apache compatible mode), and finally click Next

2

3. Choose a location and file name where you want to save the key file, click Save, and then, click Finish

3

4. Navigate to the location you saved, note that the folder should contain the Public Key (wildcard_nonlinear_ca.crt), Private Key (wildcard_nonlinear_ca.key) and Intermediate Certificate (DigiCertCA.crt)

5

PEMKeyFile

In order to generate the PEMKeyFile we will have to use Notepad++ or any other text editor of your preference.

  1. Navigate to the location where are the exported keys, and open the Private Key – in my case wildcard_nonlinear_ca.key6
  2. The Private Key should looks like this7
  3. Copy the entire body of this certificate, including —-BEGIN RSA PRIVATE KEY—- and —-END RSA PRIVATE KEY—-
  4. Open a new tab (or a new text editor), and paste the content you just copied8
  5. Now, let’s open the Public Key (wildcard_nonlinear_ca.crt) that should look like as follows9
  6. Copy the entire body of this certificate, including —-BEGIN CERTIFICATE—- and —-END CERTIFICATE—-
  7. Go to the new text you just created (step 4), and paste the Public Key content in the next line after —-END RSA PRIVATE KEY—-10
  8. Then save this document as .pem file

11

CAFile

The process is pretty much the same listed for the PEMKeyFile, however, the CAFile must have the Root Certificate on it as well. Please ensure to keep the following order:

  1. The Private Key (wildcard_nonlinear_ca.key)
  2. The Primary Certificate (wildcard_nonlinear_ca.crt)
  3. The Intermediate Certificate (DigiCertCA.crt)
  4. The Root Certificate – you should be able to get it directly from the place you issued your certificate, in my case it is named TrustedRoot.crt

Make sure to include the beginning and end tags on each certificate, then save the using .pem extension as well.

MongoDB configuration

Assuming you already have a MongoDB up and running, in order to enable SSL/TLS you must change couple things on the mongod.cfg

net:
ssl:
mode: requireSSL
PEMKeyFile: F:\ssl\SysAdmin.pem
CAFile: F:\ssl\ca.pem

Once you modified it, try to stop/start the MongoDB service and ensure it backs up.

To connect to MongoDB from now on using CMD, you should include the following options

  • –ssl
  • –host
  • –sslPEMKeyFile
  • –sslCAFile

mongo –ssl –host mongodb.mydomain.com –sslPEMKeyFile F:\ssl\SysAdmin.pem –sslCAFile F:\ssl\ca.pem

Sitecore connecting to MongoDB using SSL

I’ve raised the question on Sitecore.Stackexchange and Hishaam Namooya, and Amitabh Vyas were able to address my problems.

Amitabh Vyas said that

OOTB Sitecore does not communicate with Mongo using SSL, we need to implement a custom pipeline for such communication.

Customize the Sitecore.Analytics.MongoDB.Config by defining a new Pipeline.

Now you have two approaches to establish SSL connection between Mongo and Sitecore.

  1. SSL Integration using physical PFX file
  2. SSL Integration reading Machine Key

Please have a look at my blog post for more details.

I hope you liked it, and thanks for reading!

And I’ll see you on my next post!

Sitecore 9 update 1 XP0 topology ‘sqlcmd’ is not recognized error [SOLVED]

A week ago, I was trying to provision a Sitecore 9 Update 1 XP0 topology and everything was great, until the step

CreateShardApplicationDatabaseServerLoginSqlCmd

error-xp0.PNG

As you can see, it complains about sqlcmd is not recognized as the name of the cmdlet. This is way weird as Sitecore 9 Installation Guide does not mention anything about it.

I was not convinced that it would be sqlcmd, so posted a question on the Sitecore StackExchange – as usual, when get myself stuck after couple tentatives… you should also try whenever you get yourself stuck (don’t be shy :))

While was waiting for the community, started dig in and googled “sql server 2016 sqlcmd download” with a hope to be able to easily install the sqlcmd without need the full SQL Server installed at the web server, right?

I found that two things would be necessary

Once I got both installed, it was just a matter to re-run the XP0 and get my Sitecore 9 Update 1 up & running smoothly!

Of course I posted this as an answer in Sitecore StackExchange, check out here! And as soon the answer was there, a Sitecore employee – Kieran Marron – commented saying that

“Unfortunately this was missed off the docs for update 1. We’re aiming to have this corrected soon.”

Thanks for your reply Kieran, really appreciate it! So, stay tuned to see a fix for that anytime soon.

I hope you liked it, and thanks for reading!

And I’ll see you on my next post!

 

Sitecore 9 – Reporting not working after install [SOLVED]

Couple days ago, I was installing a new Sitecore 9 XP1 topology and during my post-installations tests I received the following error while accessing the Reporting piece

error1

C’mon! You got to be kidding me… and I was telling Sitecore 9

am-i-wrong-dude-am-i-wrong

Well, didn’t solve my problem… SOOOOOOOOOOO, let’s dig in!

Let’s re-run the Reporting piece in Powershell

  • First of all import SitecoreFundamentals SitecoreInstallFramework
  • Then execute Install-SitecoreConfiguration and point to sitecore-XP1-rep.json full path

I was prompted to provide the Package and LicenseFile location but NO SqlDbPrefix as you probably noticed from other packages like Content DeliveryContent Management, and so forth. 

powershell1

Let’s check the ConnectionStrings.config post-installation

  • Navigate to Reporting installation path, usually C:\inetpub\wwwroot\ and the name you provided for the instance
  • Go to App_Config and find ConnectionStrings.config

Well, looks like something is wrong! Did you noticed that Initial Catalog= contains only an underscore + Database (e.g. _Core, _Master, etc)

connectionstrings

An easy workaround would be rename the Initial Catalog= with the correct name of those databases and solve the case BUT not so fast buddy, let’s find the reason for this typo!

Let’s check the JSON file

  • Navigate to Sitecore configuration files path, in my case is C:\SitecoreInstaller\XP1\Configuration
  • Find sitecore-XP1-rep.json and open in Notepad
  • Look for SqlDbPrefix

WOW! There’s a DefaultValue set to “” which means you won’t be asked to provide a SqlDbPrefix

jsonfile

and the concatenation throws a blank value, resulting in the underscore + database (e.g. _Core, _Master)

json-concatenation

So, the quickest way to fix that is to remove “DefaultValue”: “” and the comma after the Description value.

json-fixed

EASY PEASY, isn’t it? From now on, you will be prompted to provide SqlDbPrefix when provisioning the Reporting piece!

I hope you liked it, and thanks for reading!

And I’ll see you on my next post!

Is there an order to install the Sitecore 9 XP1 Topology?

A powershell script to “simplify your installation” is provided by the Sitecore 9 Installation Guide, however, you can use a different way where you have to execute every JSON file individually and following a particular order, which is my preferred for now until I better understand the whole process.

After analyzing the JSON files, and post a question on the Sitecore StackExchange, and getting myself with tons of errors during the installations, I decided to create a relation table where would be easier to determine the requirements of every JSON file, then establish a proper order.

XP1-ReferenceTable

Assuming you already have SQL Server and SOLR in-place, and based in the reference table above, I’ve determined the following order to install XP1 topology without getting stop due missing prerequisites.

  1. At the SOLR server, modify and run XConnect-Solr.json and Sitecore-Solr.json
  2. Create XConnect certificate – I’d recommend to download the XP0 topology and use XConnect-CreateCert.json for that, check the reason here in Sitecore StackExchange
  3. XConnect-xp1-collection.json
  4. XConnect-xp1-collectionsearch.json
  5. XConnect-xp1-referencedata.json
  6. XConnect-xp1-marketingautomation.json
  7. XConnect-xp1-marketingautomationreporting.json
  8. sitecore-xp1-rep.json
  9. sitecore-xp1-prc.json
  10. sitecore-xp1-cm.json
  11. sitecore-xp1-cd.json

I hope this helps you as it helps me!

Also, I’d recommend the following articles to get you asap working with Sitecore 9

And last but not least, I special thanks my friend João Neto, who just become Sitecore MVP 2018, and the one that provided initial directions in Sitecore StackExchange.

Thanks for reading!

And I’ll see you on my next post.

 

 

Sitecore announces 2018 MVPs

Exactly one year ago, for the very first time, I was receiving the honor to be part of the Sitecore MVP group. Back then, I shared with you about my start, goals and how happy I was in be awarded!

Last November, once again, I submitted for this year’s technical Sitecore MVP awards, and I felt more anxious than last year

Haven’t seen in my entirely life a January soooooooooooooooooo long as this year, and I was like

 

giphy-downsized.gif

Just yesterday, in the non-ending month of January, Sitecore named its 2018 Most Valuable Professionals and I was THERE for the 2nd time!

Well, don’t need to say that all anxious became something like this….

giphy (1).gif

I am proud and glad to be part (again) of the Sitecore Most Valuable Professionals in 2018!

The first big thanks goes to my wife, Raquel, for all support and understanding during the journey! I LOVE YOU RAQUEL!

For the second round of thanks, I’d like to thank Sitecore, Sitecore Community and Sitecore MVPs!

And last but not least, a HUGE thanks to all readers!

As usual, thanks for reading, and I’ll see you on my next post!

 

Sitecore Content Delivery – Error after upgrade EXM 3.5 [SOLVED]

Couple weeks ago I was working on a Sitecore upgrade from 8.2 update 2 to 8.2 update 5 in order to also get EXM updated from 3.4 to 3.5 due Azure requirements.

My scenario had a Content Delivery and a Content Management which was properly upgraded to Sitecore 8.2 update 5 & to EXM 3.5 by following their upgrade guides.

I managed to get Sitecore 8.2 update 5 and EXM 3.5 in both nodes, however, Content Delivery keeps showing the following error after received the EXM 3.5 files.

1

The requested failed with the error message:

<html><head><title>Object moved</title></head><body>

<h2>Object moved to <a href=”/404?item=%2femailcampaign%2fecmclientservice&user=extranet%5cAnonymous&site=modules_website”>here</a>.</h2>

</body></html>

CRAP! What am I going to do?

Well, let’s double check the EXM Upgrade Instructions…

maxresdefault

AHA! Found something interesting…

“During the installation, all the .aspx pages, .asmx web services, and the .ashx handlers have been moved to the \Sitecore\modules\Web\EXM folder of the instance.”

The EXM Upgrade Instructions suggested applying a URL Rewrite rule in IIS

<rewrite>
<rules>
<rule name="ExmSitecoreFolderRule">
<match url="^(.*)ConfirmSubscription.aspx|DedicatedDispatchService.asmx|ECMClientService.asmx|RedirectUrlPage.aspx|RegisterEmailOpened.ashx|Unsubscribe.aspx|UnsubscribeFromAll.aspx)"/>
<action type="Rewrite" url="sitecore modules/Web/EXM/{R:2}" />
</rule>
</rules>
</rewrite>

BUT didn’t work here 😦

I started compare files before vs after the upgrade, and find myself with the ConnectionStrings.config which uses ecmclientservice.asmx webservice from the Content Management node

<add name="EmailCampaignClientService" connectionString="url=http://sitecore-cm/sitecore%20modules/web/emailcampaign/ecmclientservice.asmx;timeout=60000" />

Well, as the upgrade instructions said that the path has changed, let’s see what happens

<add name="EmailCampaignClientService" connectionString="url=http://sitecore-cm/sitecore%20modules/web/exm/ecmclientservice.asmx;timeout=60000" />

ConnectionStrings.config saved, re-load Sitecore Content Delivery node…

maxresdefault

…voilá!

2.PNG

I hope you liked it, and thanks for reading!

And I’ll see you on my next post!