Sitecore Rebuild Reporting Database gets stuck on StoppingDataRecording

After resolving the previous issue with the Sitecore reporting database rebuild getting stuck on the starting process state. I was able to progress to the next step where it managed to get stuck on the StoppingDataRecording process state.

The logs were clean from errors in relation to this issue, yet it would stay stuck on this state until cancelled.

It turns out that the content management server which I was running the rebuild off of had it's configuration files configured incorrectly. As there was no dedicated processing server in this environment the content management server should have been setup as content management + processing.

It was the disabled processing related configurations which was causing the reporting database rebuild to get stuck.

Sitecore reporting database rebuild SQL script

As a part of the instructions of rebuilding the reporting database in Sitecore, there is a step where some SQL needs to be run manually on the database. When the process state is WaitReadyToReceiveData the contents of 7 database tables need to move from the primary DB to the secondary DB. The instructions state:

In the Rebuild Reporting Database page, when you see Waiting to receive to data status, copy the following marketing definition tables from the primary to the secondary reporting database:

• CampaignActivityDefinitions

• GoalDefinitions

• OutcomeDefinitions

• MarketingAssetDefinitions

• Taxonomy_TaxonEntity

• Taxonomy_TaxonEntityFieldDefinition

• Taxonomy_TaxonEntityFieldValue

The SQL to copy this data is as follows:

INSERT INTO [reporting.secondary]..CampaignActivityDefinitions
SELECT * FROM [reporting]..CampaignActivityDefinitions

INSERT INTO [reporting.secondary]..GoalDefinitions
SELECT * FROM [reporting]..GoalDefinitions

INSERT INTO [reporting.secondary]..OutcomeDefinitions
SELECT * FROM [reporting]..OutcomeDefinitions

INSERT INTO [reporting.secondary]..MarketingAssetDefinitions
SELECT * FROM [reporting]..MarketingAssetDefinitions

INSERT INTO [reporting.secondary]..Taxonomy_TaxonEntity
SELECT * FROM [reporting]..Taxonomy_TaxonEntity

INSERT INTO [reporting.secondary]..Taxonomy_TaxonEntityFieldDefinition
SELECT * FROM [reporting]..Taxonomy_TaxonEntityFieldDefinition

INSERT INTO [reporting.secondary]..Taxonomy_TaxonEntityFieldValue
SELECT * FROM [reporting]..Taxonomy_TaxonEntityFieldValue

Ensure that the database names are updated to reflect that of your environment.

Sitecore Rebuild Reporting Database gets stuck on Starting

After moving over to a new MongoDB host, and with fresh data I needed to rebuild the reporting database in Sitecore to get everything in sync. The problem was that it got stuck in the Starting state and never progressed.

Process State does not change from starting

Looking into the logs the following error was visible:

ERROR Message: Exception of type 'System.Web.HttpUnhandledException' was thrown. - InnerException: System.Data.SqlClient.SqlException (0x80131904): Cannot open database "Sitecore_Reporting_Secondary" requested by the login. The login failed.
Login failed for user 'MyAccount'.

It turned out to be a permission issue with the reporting secondary database, fixing  that up allowed the rebuild to continue.

Sitecore Commerce Server profile definitions not found

Working inside the Commerce Server Manager, under the profile definitions I noticed a 404 not found page when selecting a definition (such as User Object).

Sitecore Commerce Server - profile definition not found

This was due to a local website binding being missing.

1. Open up IIS Manager
2. Select the default web site (or create this with an empty binding).
3. Create a virtual directory called Widgets and set the physical path to c:\Program Files (x86)\Common Files\Microsoft Shared\Enterprise Servers\Commerce Server\Widgets

The profile definition will now load correct without error.

MVC controller action from JavaScript is returning 404

In developing front-end logic to call MVC controller actions (with Sitecore context), I noticed that the calls were returning a 404 from the delivery servers and not management servers.

The URL of the AJAX request was:

url: "/Accounts/Register"

Inside the console, that request appeared as follows

The request on delivery server returned a 404

The request on the management server worked

Investigation lead to the result that URLs on the delivery server were all being redirected to lowercase URLs. Hence the initiator for the AJAX call changing between servers. MVC controllers will work with both uppercase and lowercase URLs, so the problem was not likely on that end. What appeared to be happening was that the redirect was messing up the call and requesting it as a standard page (hence the 404 response, which was actually returning the IIS 404 page).

Changing the request URL in AJAX to be lowercase fixed the issue.

url: "/Accounts/Register"

Anti forgery tokens on MVC controllers called via JavaScript

In a previous post I demonstrated how an MVC controller (which inherits from SitecoreController) can be used to identify a contact via JavaScript. The next step is to prevent Cross-Site Forgery Requests (CSRF) via the use of an anti forgery token. Because this is a service call and not form post, it's a slightly different method to hook this all together.

Users of AngularJS and Web API can refer to this example: CSRF in AngularJS/Web API on Web Forms.

The anti forgery token

The first step is to place the hidden field on the page which contains the anti-forgery token. This would be placed on the layout, or a view if you are restricting it to certain site features. Web forms users would place this on a master page.

@using System.Web.Helpers
@Html.AntiForgeryToken()

or

<%@ Import Namespace="System.Web.Helpers" %>
<%# AntiForgery.GetHtml() %>

Token validation

The following attribute will be used on the MVC controller and validates that the token is present and matches the users cookie correctly.

using System.Web.Mvc;

namespace MyProject
{
    public class ValidateAntiForgeryHeader : System.Web.Mvc.FilterAttribute, System.Web.Mvc.IAuthorizationFilter
    {
        public void OnAuthorization(AuthorizationContext filterContext)
        {
            string clientToken = filterContext.RequestContext.HttpContext.Request.Headers.Get(KEY_NAME);
            if (clientToken == null)
            {
                throw new HttpAntiForgeryException(string.Format("Header does not contain {0}", KEY_NAME));
            }

            string serverToken = filterContext.HttpContext.Request.Cookies.Get(KEY_NAME).Value;
            if (serverToken == null)
            {
                throw new HttpAntiForgeryException(string.Format("Cookies does not contain {0}", KEY_NAME));
            }

            System.Web.Helpers.AntiForgery.Validate(serverToken, clientToken);
        }

        private const string KEY_NAME = "__RequestVerificationToken";
    }
}

The MVC controller

Now we just need to place the attribute on any actions which require anti forgery. Generally this happens on calls where data is being sent through, such as: login, registration etc. 

[HttpPost]
[ValidateAntiForgeryHeader]
public ActionResult ForgottenPassword(string emailAddress)
{
 
}

The service call

Now the service call just needs to pass through the token in the header.

var token = $('input[name="__RequestVerificationToken"]').val();

$.ajax({
    type: "POST",
    url: "/Contact/IdentifyContact",
 headers: {
        '__RequestVerificationToken': token
    },
    data: JSON.stringify({'emailAddress': 'testing@email.com'}),
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (result) { },
    error: function (request, status, error) { }
});

Conclusion

Now the service calls are protected against CSRF, and of course in a Sitecore implementation this type of service call will still have access to the Sitecore context.

ServiceStack the clean way to integrate API calls

ServiceStack is a powerful and fast framework which adds value to any .NET solution. It handles a fair bit of functionality and features, as the image below shows). This post will be going over just how simple it is to integrate a REST API call into your solution using ServiceStack.

What comes out of the box with ServiceStack

In this example, I am going to be calling an API to get weather data using Open Weather Map. This API has limited free access, but does require signup to get an API key. An example of the call is shown below, it simply takes in the city along with an app ID.

The request model

You'll notice with the request model below that the call type is defined as get, the return object is referenced directly, and that DataMember is the service's variable name (which allows for cleaner classes). The route does not include any querystring as this is automatically handled, this could be something like /weather/{q/ if  a parameter is part of the route.

using ServiceStack;
using System.Runtime.Serialization;

namespace MyProject
{
    [Route("/weather", "GET")]
    [DataContract]
    public class CurrentWeatherRequestModel : IReturn<CurrentWeatherResponseModel>
    {
        [DataMember(Name = "q")]
        public string City { get; set; }

        [DataMember(Name = "appid")]
        public string ApiKey { get; set; }
    }
}

The response model

The response model is simple enough, DataMember could also be used to have a cleaner class, but this example demonstrates that ServiceStack will map directly to the model.

using System.Collections.Generic;

namespace MyProject
{
    public class CurrentWeatherResponseModel
    {
        public Coord coord { get; set; }
        public Sys sys { get; set; }
        public List<Weather> weather { get; set; }
        public Main main { get; set; }
        public Wind wind { get; set; }
        public Rain rain { get; set; }
        public Clouds clouds { get; set; }
        public int dt { get; set; }
        public int id { get; set; }
        public string name { get; set; }
        public int cod { get; set; }
    }

    public class Coord
    {
        public int lon { get; set; }
        public int lat { get; set; }
    }

    public class Sys
    {
        public string country { get; set; }
        public int sunrise { get; set; }
        public int sunset { get; set; }
    }

    public class Weather
    {
        public int id { get; set; }
        public string main { get; set; }
        public string description { get; set; }
        public string icon { get; set; }
    }

    public class Main
    {
        public double temp { get; set; }
        public int humidity { get; set; }
        public int pressure { get; set; }
        public double temp_min { get; set; }
        public double temp_max { get; set; }
    }

    public class Wind
    {
        public double speed { get; set; }
        public double deg { get; set; }
    }

    public class Rain
    {
        public int __invalid_name__3h { get; set; }
    }

    public class Clouds
    {
        public int all { get; set; }
    }
}

The call

Finally here is the actual service call, in 3 simple lines the API is called and the result is mapped back to your response class. The base URL for the API is also set here.

using ServiceStack;

namespace MyProject
{
    public static class GetCurrentWeather
    {
        public static CurrentWeatherResponseModel ByCity(CurrentWeatherRequestModel request)
        {
            var client = new JsonServiceClient("http://api.openweathermap.org/data/2.5");
            client.Headers.Add("ContentType", "application/json");

            return client.Get(request); 
        }
    }
}

Conclusion

As you can see from the example above, ServiceStack is a really clean/simple way of making service calls in your c# solutions. The code is easily readable, can be integrated into unit tests and works well across multiple environments, if the base URL is a configurable variable.