Managing authentication and data access

This tutorial explains how to manage authentication and data access when working with SQL Server Analysis Services (SSAS) via XMLA. We support two different approaches:

Using roles from Analysis Services
Using Windows authentication

Note If you do not have XMLA configured (msmdpump.dll), we recommend using Flexmonster Accelerator — a special server-side utility developed by Flexmonster.

Using roles from Analysis Services

In SQL Server Analysis Services, access rights are provided based on roles. For more information about role configuration, visit Microsoft documentation on roles and permissions.

After roles are configured in Analysis Services, specify them in the dataSource.roles property when connecting Flexmonster Pivot to SSAS:

dataSource: {
  type: "microsoft analysis services",
  // URL to msmdpump.dll
  proxyUrl: "http://localhost/OLAP/msmdpump.dll",
  catalog: "Adventure Works DW Standard Edition",
  cube: "Adventure Works",
  // Roles from SSAS
  // To add multiple roles, separate them with a comma
  roles: "Sales Manager US"
}

Live example

Using Windows authentication

Prerequisites

Flexmonster 2.8.2 or later.
Flexmonster that is connected to your cube without authentication.
See how to connect to Microsoft Analysis Services via XMLA.

Step 1. Allow the OPTIONS request

In CORS, the browser sends the OPTIONS preflight request to check which request methods and headers the server permits. This request does not contain any credentials, so servers using Integrated Windows Authentication may reject the request and prompt for authentication instead.

To prevent this, configure the server to accept the OPTIONS preflight request without requiring authentication. The Microsoft CORS tutorial provides details on how to allow the OPTIONS request.

Step 2. Enable Windows authentication in Flexmonster

Windows authentication must be enabled on the client side as well. To enable the authentication in Flexmonster, set the dataSource.withCredentials property to true:

const pivot = new Flexmonster({
  container: "pivotContainer",
  toolbar: true, 
  report: {
    dataSource: {
      type: "microsoft analysis services",
      proxyUrl: "http://localhost/OLAP/msmdpump.dll",
      catalog: "Adventure Works DW Standard Edition",
      cube: "Adventure Works",
      withCredentials: true
    }
  }
});

After applying the configurations, the requests to Microsoft Analysis Services will be performed using your current Windows user.