Can I call the oData service directly in xConnect?

Question

I've been playing round with Sitecore 9 recently and the xConnect api and had a question around oData.

Underneath the api layer there is an oData service that can be reached via: https://yourxconnecturl/odata.

Presumably xConnect uses the oData protocol so that external systems (PowerBI for example) will be able to call this api in a standard way.

As Sitecore is now secure by default you will get a 403 error when trying to access this.

So my question is:

Can the oData layer be accessed directly via the URL? If so, can this be done with a Client Certificate request?

Answer 1

It turns out this is entirely possible. To access xConnect's oData api you have to create a Client Certificate Request.

To test this out you can use Fiddler to use your client certificate:

• Go to IIS on your machine -> Server Certificates

• Find the Certificate that xConnect uses (typically it's name will be "site name_client")

• Export this as a .pfx file to a place on your machine.

• Go to "Manage Computer Certificates" on your machine. Make sure you are using Local Computer account.

• Go to -> "Trusted Root Certificate Authorities" -> "Certificates" -> Right click "Import"

• Choose the .pfx file you created.

• Go through the default options on the import wizard.

• Once installed in the Trusted Root Store. Find the certificate and right click it and "Export" it as a .CER file.

• Export it to a place where Fiddler can use it :

  C:\Users\yourusername\Documents\Fiddler2\ClientCertificate.cer (It must be called this for Fiddler to pick it up)

• Start Fiddler

If you add /odata on the end of your URL you'll get something like this.

If you get issues with trust:

• Ensure the client certificates root certificate is also in the "Trusted Root Authorities" store.
• If you get further issues with trust see this answer here: https://stackoverflow.com/questions/27232340/iis-8-5-mutual-certificates-authentication-fails-with-error-403-16. Annoyingly to get this to work with self signed certs in some circumstances you have to add a registry setting.

There's a full explanation of how I got this working on my blog here:

http://www.coreblimeysitecore.com/blog/odata-xconnect-sitecore-9/

UPDATE: An easier way to do this is to just comment out the following setting in this file

Yourxconnectsite/App_Config/AppSettings.config

 <add key="validateCertificateThumbprint" value="53FA51C904F2B281811B44D08E86F69C324F6647" />

Answer 2

I think it is possible.

You need to pass correct certificate on the following line: >CertificateWebRequestHandlerModifierOptions.Parse("StoreName=My;StoreLocation=LocalMachine;FindType=FindByThumbprint;FindValue=15E6693B0AECB63DE57D991EC363CA462DC52432");

Please have a look at this code:

 // Valid certificate thumbprints must be passed in
 CertificateWebRequestHandlerModifierOptions options =
 CertificateWebRequestHandlerModifierOptions.Parse("StoreName=My;StoreLocation=LocalMachine;FindType=FindByThumbprint;FindValue=15E6693B0AECB63DE57D991EC363CA462DC52432");

 // Optional timeout modifier
 var certificateModifier = new CertificateWebRequestHandlerModifier(options);
 List<IHttpClientModifier> clientModifiers = new List<IHttpClientModifier>();
 var timeoutClientModifier = new TimeoutHttpClientModifier(new TimeSpan(0, 0, 20));

 clientModifiers.Add(timeoutClientModifier);

 // This overload takes three client end points - collection, search, and configuration
 var collectionClient = new CollectionWebApiClient(new Uri("https://sc900rev170622_xconnect/odata"), clientModifiers, new[] { certificateModifier });
 var searchClient = new SearchWebApiClient(new Uri("https://sc900rev170622_xconnect/odata"), clientModifiers, new[] { certificateModifier });
 var configurationClient = new ConfigurationWebApiClient(new Uri("https://sc900rev170622_xconnect/configuration"), clientModifiers, new[] { certificateModifier });

 var cfg = new XConnectClientConfiguration(
                new XdbRuntimeModel(CollectionModel.Model), collectionClient, searchClient, configurationClient);

 try
 {
      await cfg.InitializeAsync();
 }
 catch (XdbModelConflictException ce)
 {
      Console.WriteLine("ERROR:" + ce.Message);
      return;
 }

 using (var client = new XConnectClient(cfg))
 {
 
 }

Source code is from this link: https://doc.sitecore.com/developers/101/sitecore-experience-platform/en/instantiate-client-in-a-non-sitecore-context.html