SQL Server Self-Signed certificate required by Sitecore installer

Recently I had a very interesting case when Sitecore installer was failing during installation because required a secured connection with the SQL Server. It was even more interesting because the connection at the beginning was established correctly, some of the databases were created and then …. during a step related to xConnect and xDB databases I saw an error saying:

A connection was successfully established with the server, but then an error occured during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issues by an authority that is not trusted.)

After some time spent on checking certs generated by Sitecore I realized that I was looking for a solution in the wrong place.

By default SQL Server instance does not use any encryption for incoming connections:

So it should not be a problem – but it was. I decided to add a self-signed certificate to my SQL Server instance to check if it resolved the problem (spoiler alert: it did).

SQL Server requires certificates prepared in a special format. You can generate a certificate with this PowerShell script

New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" -FriendlyName "SQL Server Self-signed" -DnsName "$env:COMPUTERNAME",'localhost.'  -KeyAlgorithm RSA -KeyLength 2048 -Hash 'SHA256' -TextExtension '2.5.29.37={text}1.3.6.1.5.5.7.3.1' -NotAfter (Get-Date).AddMonths(24) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider"-CertStoreLocation "Cert:\LocalMachine\My"

Details of it are described here:

https://www.sqlserver-dba.com/2022/06/how-to-generate-a-self-signed-certificate-for-sql-server-with-new-selfsignedcertificate.html

When the certificate is generated and added to the certs store you will find it by running mmc (from Windows > Run) where you have to select certificates for your computer.

Now, you have to assign rights to read that certificate for your SQL Server instance.

Open “Services” app and find your “SQL Server” service and check its properties. In the “Log On” tab you will find user name used to run this service:

Now you need to go back to the Certs Store and on your self-signed certificate right-click and select options:

All Tasks > Manage Private Keys

You have to add there a SQL Server user and add it to the list of users allowed to read that key:

I would recommend also copying your key to the “Trusted Root Certification Authorities” folder:


Then export your certificate with a private key using encryption AES256-SHA256:

Ok, at this point you have already exported the certificate ready to be used by your SQL Server.

Again open MMC and this time select SQL Server Manger from the list:

Then go to the “SQL Server Network Configuration” node and right-click protocols to select the properties option:

In the opened window select “Certificate” tab and import the certificate that you have just exported.

Restart your SQL Server instance and your error should be gone now.

How to encrypt/decrypt Sitecore connection strings

Sitecore as every other .NET application supports encryption of the connection strings. You can use that feature to secure your connection strings and reduce the chances that someone will steal your passwords to your databases.

It is important to remember that:

  • encryption and decryption are done in the scope of the server
  • encryption and decryption are done in the scope of the application

Alright, but what do the above statements mean in real life?

It means that connection strings files encrypted for one application on one server will not work on different servers and different applications. You need to repeat encryption/decryption for every environment you have and for every application you have.

Sounds like a lot of work – well, security has its own price.

Before you start encrypting your files you need to verify if connectionStrings.config file contains “configBuilders” attribute in the “connectionStrings” node:

If the “configBuilders” attribute is there – you have to remove it.

As a next step, you have to find the aspnet_regiis.exe file in your environment – usually, you will find it in your Microsoft.NET\Framework64 folder. In my case it was the path:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe

When you find it you have everything to run encryption and decryption commands.

To encrypt connectionStrings file you need to run:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -pef "connectionStrings" D:\inetpub\wwwroot\sc10-dev-cd.localhost

To decrypt connectionStrings file you need to run:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -pdf "connectionStrings" D:\inetpub\wwwroot\sc10-dev-cd.localhost

And this is basically it, easy peasy lemon squeezy!

Sitecore and Salesforce custom integration – Salesforce JWT bearer token generation

In this post I will explain my approach to the Salesforce – Sitecore integration. In fact code that is going to be described here can be used in almost every .net application.

Available Sitecore – Salesforce integration

Sitecore provides an integration to everyone who needs to synchronize the data between the Sitecore and Salesforce – the “Salesforce Connect” extension.

You can download it here: https://dev.sitecore.net/Downloads/Salesforce_Connect.aspx

If you do not know which version you should install, check it here (comatibility table): https://support.sitecore.com/kb?id=kb_article_view&sysparm_article=KB1000576

The natural question that probably comes to your mind right now is ‘why did not I use it when I had a need to connect to salesforce’ – this is a really good question.

The answer is – because available extension is focused on the syncing contacts between Salesforce and Sitecore, when in our case we just wanted to send some data to Salesforce and the synchronization was being made on the different level.

In other words, available extension did not meet our needs.

Few general words about the integration

Integration with Salesforce is not in any kind special – it is just an API that is managed by the Salesforce developers. An endpoints and parameters can differ but the common thing is token generation that I decided to describe in this post because it took me a while to understand how to generate the correct JWT bearer token that will be honored by the Salesforce endpoint.

Documentation about it is available on the Salesforce help portal : https://help.salesforce.com/s/articleView?id=sf.remoteaccess_oauth_jwt_flow.htm&type=5

Documentation contains the Java code that I had to transform into .NET one what sometimes was not so obvious.

What do you need to generate OAuth 2.0 JWT bearer token

To make communication possible you must have generated certificate that is going to be used by Salesforce and your Sitecore instance. When certificate will be installed on the Salesforce side you can continute configuration on your side.

To generate the JWT token you need to gather the following information:

  • iss – this is OAuth client_id (provided by Salesforce)
  • aud – this is authorization server’s url (login.salesfroce.com for production and test.salesforce.com for test environments – provided by Salesforce)
  • sub – the username of account used to connect to salesforce (usually email – provided by Salesforce)
  • exp – timestamp of the expiration (provided by Salesforce)

Generation process

All of that data need to be later encoded to base64 string:

private string GenerateClaimsString()
{
var iss = this._salesforceConfigurationService.GetJwtClaimsIss();
var sub = this._salesforceConfigurationService.GetJwtClaimsSub();
var aud = this._salesforceConfigurationService.GetJwtClaimsAud();
var exp = this._salesforceConfigurationService.GetJwtClaimsExp();
var claims = $"{{\"iss\": \"{iss}\", \"sub\": \"{sub}\", \"aud\": \"{aud}\", \"exp\": \"{exp}\"}}";
return this.Base64Encoder(claims);
}

But it is not only encoding by the standard Convert.ToBase64String method. We must also remove some of the chars from the generated string:

private string Base64Encoder(string valueToEncode)
{
byte[] valueToEncodeAsBytes = System.Text.Encoding.UTF8.GetBytes(valueToEncode);
return Convert.ToBase64String(valueToEncodeAsBytes).TrimEnd('=').Replace('+', '-').Replace('/', '_');
}

When the values are encoded and unwanted chars are removed from the encoded string we need to add to it predefined JWT header. Header has a very similar structure to the claims and can be hardcoded with value:

"{\"alg\":\"RS256\"}"

Here is the code that can do that:

private string GenerateJwtHeaderString()
{
var headerValue = SalesforceConstants.Api.Values.Header;
return this.Base64Encoder(headerValue);
}

After all operations we have two strings that we can use to build the assertion used later in the authorization request.

var assertion = this.GenerateJwtHeaderString();
assertion += ".";
assertion += this.GenerateClaimsString();

As you can see two strings are again connected with the dot sign.

But this is not the end – now we are going to use our certificate to sign the assertion. Full code of assertion generation will look like this:

var assertion = this.GenerateJwtHeaderString();
assertion += ".";
assertion += this.GenerateClaimsString();
this._assertion = assertion + "." + this.SignAndGeneratePayloadString(assertion);

Where SignAndGeneratePayloadString method looks like this:

private string SignAndGeneratePayloadString(string payload)
{
            X509Certificate2 certificate = new X509Certificate2(this._salesforceConfigurationService.GetCertPath(), this._salesforceConfigurationService.GetCertPass(), X509KeyStorageFlags.MachineKeySet | X509KeyStorageFlags.PersistKeySet | X509KeyStorageFlags.Exportable);
            using (var privateKey = certificate.GetRSAPrivateKey())
            {
                var signedData = privateKey.SignData(System.Text.Encoding.UTF8.GetBytes(payload), HashAlgorithmName.SHA256, RSASignaturePadding.Pkcs1);
                return Convert.ToBase64String(signedData).TrimEnd('=').Replace('+', '-').Replace('/', '_');
            }
}

As you may noticed SignAndGeneratePayloadString method uses certificate to ‘sign’ the assertion data – to make it work you need to load the certificate from the disk (it must be p12 certificate file) and have certificate password to read it.

The result of signing is again concatenated with the dot and original assertion data.

When the assertion is finally ready, you can authenticate with Salesforce API and Request Access Token for further Salesforce communication.

Summary

If you compare implementation from this blog post with implementation from the Salesforce’s help page you will notice few major differences like:

  • usage of p12 certificate file instead of jks
  • additional string operations on the generated/encoded string values

If you want you can check the full implementation here: https://github.com/lskowronski/SitecoreSalesforce/blob/main/SalesforceJwtService.cs

Because transition from JKS file to p12 file can be also tricky, I will describe it in the another blog post that is going to be published soon – stay tuned!

What to use to write Unit Tests for Sitecore?

In this post, I would like to share with you some experiences I had in my last project and give you advice on what you can use to write Unit Tests and why sometimes some of the libraries are not a good way to go.

In the most cases when the team is thinking to start writing unit tests there is an discussion about different approaches and available libraries – been there done that.

Let me tell you my story

In our case, we had the following libraries taken into consideration:

  • NUnit vs XUnit
  • NSubstitute vs Moq
  • Asserts vs FluentAssertions
  • FakeDB

We totally forgot about AutoFixture and that was our mistake!

After couple of meetings (we had several teams that had to agree something) we decided to vote and the winners were:

  • NUnit
  • Moq
  • FluentAssertions
  • FakeDB

Of course, you may ask “why” … the answer is usually simple because team members knew these three or just liked to write unit tests with the support of these libraries than others.

Is this logical – in some way yes. Is it professional – some people may say ‘no’ but this is how real-world works. Software developers tend to use libraries that they know – especially when it comes to writing unit tests – even when there are better solutions out there.

Everything was fine at the beginning…

We started to write tests, we had around 100 test cases and then we realized that we need something to generate fake Sitecore items automatically.

We started to play we AutoFixture library and then we realized that we made a mistake …

AutoFixture was working really good with all of the libraries instead of FakeDB. We were able to generate simple types like strings etc. but no items were added to the FakeDB.

After some digging in google I found out that many people had that issue and there are two repeated solutions:

  • switch into XUnit
  • get rid of FakeDB and prepare your own fake objects

For us was too late – we decided to stay with our setup.

What to use – the answer

Based on my experience I would not start writing unit tests for Sitecore with NUnit in the future.

The rest of the listed libraries seem to be pretty safe and good from my point of view.

So the answer is:

Use XUnit and whatever else you want – but not NUnit because it can block you at some point as blocked me.