Authenticate requests to the SQL API with LDAP

In this recipe, you'll learn how to authenticate requests to the SQL API with a LDAP catalog. You can use this approach to enable single sign-on (SSO) experience in BI tools connecting to Cube for your end users.

Use case

We want to allow the end users to enter their Google Workspace credentials to get access to the datasets in Power BI or any other BI tools, powered by Cube's SQL API. Google Workspace provides the Secure LDAP (opens in a new tab) service that we'll use to securely check credentials. However, any LDAP provider would work.

Here's a step-by-step breakdown of the authentication flow:

Users enters their Google Workspace credentials in Power BI.
Power BI sends them over a TLS-secured SQL API connection to Cube.
Cube verifies the credentials via the Secure LDAP service.
If credentials are correct, users get access to the dataset.

Configuration

To verify the credentials, use the check_sql_auth configuration option which accepts user name and password as its 2nd and 3rd arguments. You can verify the credentials and either authenticate or fail the request:

Python

JavaScript

Here's an example code you can use to verify the credentials via the Secure LDAP service in Google Workspace:

cube.js

const ldap = require('ldapjs');
 
const createLdapClient = () => ldap.createClient({
  url: [ 'ldaps://ldap.google.com:636' ],
  tlsOptions: {
    key: process.env.GOOGLE_LDAP_KEY,
    cert: process.env.GOOGLE_LDAP_CERT,
    ca: [ process.env.GOOGLE_TRUST_SERVICES_CERT ],
    requestCert: true,
    rejectUnauthorized: false
  }
});
 
const client = createLdapClient();
 
client.on('connectError', (err) => {
  console.log(`Can't connect to LDAP: ${err.stack || err}`);
});
 
module.exports = {
  checkSqlAuth: async (req, user_name, password) => {
    const ldapSearchResult = await new Promise((resolve, reject) => {
      const resultArray = [];
 
      const userId = `${user_name}@example.com`;
      console.log(`User id`, userId);
 
      const opts = {
        filter: `mail=${userId}`,
        scope: 'sub',
        attributes: [ 'dn', 'sn', 'cn', 'memberOf' ]
      };
 
      client.search('dc=example, dc=com', opts, (err, res) => {
        if (err) {
          console.log(err);
        }
 
        res.on('searchRequest', (searchRequest) => {
          console.log('Search request: ', searchRequest.messageId);
        });
 
        res.on('searchEntry', (entry) => {
          console.log('Search entry: ' + JSON.stringify(entry.object));
          resultArray.push(entry.object);
        });
 
        res.on('searchReference', (referral) => {
          console.log('Referral: ' + referral.uris.join());
        });
 
        res.on('error', (err) => {
          console.error('Error: ' + err.message);
          reject(err);
        });
 
        res.on('end', (result) => {
          console.log('Status: ' + result.status);
          resolve(resultArray);
        });
      });
    });
 
    if (ldapSearchResult[0]) {
      console.log(ldapSearchResult[0]);
 
      const canBind = await new Promise((resolve, reject) => {
        console.log(`Authenticating ${userId}`);
 
        createLdapClient().bind(userId, password, (err) => {
          if (err) {
            console.log(`Error binding LDAP: ${err}`);
            resolve(false);
          }
          else {
            console.log(`Authentication for ${userId} has been successful`)
            resolve(true);
          }
        });
      });
 
      if (canBind) {
        return {
          password,
          securityContext: {
            attributes: ldapSearchResult[0]
          }
        }
      }
    }
 
    throw new Error('Incorrect user name or password');
  }
};

Using different data models for tenants REST API and Auth0