Enable different authentication methods to xConnect OData

Recently, we had a client that wanted to extract data from xConnect using Power BI. One option available was through the built-in OData service – site-prefix.xconnect/odata endpoint. The challenge was that Power BI (among other tools) doesn’t support the kind of authentication that xConnect implements.

Sitecore xConnect requires authentication through a client certificate. If you try to open site-prefix.xconnect/odata in a browser for example, the browser will ask you to provide the client certificate that xConnect is expecting.

PowerBI Desktop doesn’t support client certificate authentication. The tool supports 5 different ways of authentication, but client certificate is not one of them. This is a feature that has been requested from users since 2015, but was not implemented yet.

Ian Graham wrote a nice blog post on Using Power BI with xConnect in Sitecore 9 where he explains (among other things) how to disable the client certificate authentication from xConnect so you can extract some data (for testing purposes). However, you should not disable client authentication in a production environment as it loosens security and opens xConnect data to exploits.

Solution 1: Reverse Proxy Service

Our first solution was to build a Reverse Proxy Service in .NET Core that overpasses this xConnect security layer.

A reverse proxy is a server that sits in front of web servers (xConnect) and forwards client (e.g. Power BI) requests to those web servers. Reverse proxies are typically implemented to help increase security, performance, and reliability.

You can use commercial solutions like NGINX or implement a custom one. As we didn’t have any commercial tool available, we built one ourselves.

You can learn how to implement a reverse proxy in this blog post Building a Reverse Proxy in .NET Core, but if you are lazy (like me), you can download the source code we implemented. Bear in mind that the code is not complete and it is not implementing any authentication method.

I had a quick chat with Ian Graham on Slack and he also had to build a reverse proxy server, but using NodeJS, in order to get data from xConnect with the authentication enabled.

Solution 2: xConnect default authentication + your extra logic

In the meantime, we had opened a ticket with the Sitecore Helpdesk Support. Their response was that there are not alternative ways to access xConnect from Power BI with certificate validation enabled. They registered a corresponding wish to the product team ( reference number 330407).

However, the Sitecore Helpdesk Support didn’t stop there. They walked the extra mile for us and provided a very helpful suggestion, which I am going to share here.

You can implement your custom authentication method which will work together with client authentication. The client certificate validation is performed by the filter added by Sitecore.XConnect.Security.Web.CertificateValidationHttpConfiguration, Sitecore.XConnect.Security.Web service defined in the c:\inetpub\wwwroot\site-prefix.xconnect\App_data\config\sitecore\CoreServices\sc.XConnect.Security.EnforceSSLWithCertificateValidation.xml.

Here is the code of the ConfigureServices method:

Modify the CertificateAuthenticationAttribute and implement the AuthenticateAsync method to call the certificate related code first and use your custom logic if the result is not successful.

This will allow still having default authentication (client certificate) + your extra logic.

For the sake of simplicity, we implemented basic authentication on xConnect while keeping the client certificate authentication enabled.

Before I go any further, as we implemented basic authentication in this example, I strongly recommend you reading about it in the Microsoft documentation Basic Authentication in ASP.NET Web API.

Basic authentication brings some risks.

• User credentials are sent in the request.
• Credentials are sent as plaintext.
• Credentials are sent with every request.
• Vulnerable to cross-site request forgery (CSRF); requires anti-CSRF measures
• Because the credentials are sent unencrypted, Basic authentication is only secure over HTTPS

However, you can use the source code available (link provided below) and implement a harder security method.

I am not going to dig into details of how the code was implemented, but I give an overview below. You can see the complete code we implemented.

First, we implemented the class that is going the replace xConnect default’s certificate validation service.

Next, we added a condition for the basic authentication in the certificate validation filter class:

Following, we implement the logic for the basic authentication filter class:

Finally, we replace xConnect default’s certificate validation service.

The remaining configuration needed can be found in the readme.md file available on my Github repo.

The end result is that we can Connect to xConnect OData service by using a user and password (basic authentication).

xConnect OData limitations

Unfortunately, we have found some xConnect limitations along the way.

• DeviceProfiles data is not available. This is because xConnect expect a different type of request (http POST) that Power BI and other tools don’t implement.

Preview of certain subset of data is not available, but you can still fetch it. It is only the preview that doesn’t work. This is because xConnect doesn’t support expanding some subsets of data.

That’s it for today.

Credits

Photo by Carlos Muza on Unsplash.