Google Authentication in Linux

When you connect to Google from Sisense, you can authenticate your account with your Google credentials if you are working from the localhost. If however, you are connecting remotely to the Sisense server, and the address of the Sisense Web Application is something other than localhost, Google requires that you connect using the OAuth 2.0 protocol. The OAuth 2.0 authorization framework enables a third-party, in this case, Sisense, to obtain limited access to an HTTP service such as Google Sheets.

To connect to Google Services through a remote Sisense server, you need to create a Google application to obtain OAuth 2.0 client credentials from Google and then add those credentials to a manifest file on your Sisense Server. This topic explains how to create a Google application when working in Linux.

Creating a Google Application

Google OAuth 2.0 requires you to create a Google API Console project and set up authorized JavaScript origins and redirect URIs. Sisense has built-in credentials for the default Google application so you can connect from localhost. For security reasons, you should create and set up your own Google application to support the Google OAuth 2.0 protocol. Your Sisense Administrator needs to configure it one time (for each connector with OAuth 2.0 authorization flow after each installation). The Google OAuth2.0 flow will work for all users after it.

To create a Google application:

  1. Prepare a Linux Virtual Machine with internal domain access.
  2. Go to the Google API Console.
  3. Select an existing project or create a new one by selecting Create.

  4. In the main API console, select your project.

  5. In Authorized domains, enter the domains from where you will connect to the Google API, for example, yourcompanyname.com. Then click Save.
    Note: If you are configuring remote access for SSL setup servers (https://), make sure that both the authorized domain and the callback url are configured with the https prefix.
  6. Under Application type, select Web application (more information here).
    In the Name field, you can enter the name you will see in the console.
  7. In the Authorized JavaScript origins field, enter the origin for your app – a unique combination of protocol, hostname, and port. You can enter multiple origins to allow for your app to run on different protocols, domains, or subdomains. You cannot use public IP addresses, wildcards, paths and trailing slashes. Do not use localhost for production for security reasons (only dev, test and staging environments).
    Examples of correct origins:
    http://localhost:8080
    https://myproductionurl.example.com
  8. The Authorized redirect URI is the path in your application that users are redirected to after they have authenticated with Google. The path will be appended with the authorization code for access. For example, for Google bug query it would be: http://test.corp.sisense.com:30845/oauth/R29vZ2xlQmlnUXVlcnk=/callback. Then press Enter.
    Note the following:
  9. The redirect URI must match the client side domain and be added to console APIs or you will get a redirect_uri_mismatch error.
    Note: If you are configuring remote access for SSL setup servers (https://), make sure that both the authorized domain and the callback url are configured with the https prefix.
    Google Sheets: http://APP_URL/oauth/R29vZ2xlIFNwcmVhZHNoZWV0cw==/callback
    Google Analytics: http://APP_URL/oauth/R29vZ2xlQW5hbHl0aWNz/callback
    Google Ads: http://APP_URL/oauth/R29vZ2xlQWR3b3Jkcw==/callback
    Google BigQuery: http://APP_URL/oauth/R29vZ2xlQmlnUXVlcnk=/callback
    For Sisense it should be in format:
    http://APP_URL/oauth/R29vZ2xlIFNwcmVhZHNoZWV0cw==/callback
    where '/oauth/R29vZ2xlIFNwcmVhZHNoZWV0cw==/callback' is a hardcoded immutable string.
    For example,
    http://sisense.com/oauth/R29vZ2xlIFNwcmVhZHNoZWV0cw==/callback
    If you are using a base URL, the format of the callback will be:
    http://BASE_URL/oauth/R29vZ2xlIFNwcmVhZHNoZWV0cw==/callback
    For example:
    https://baseurl.sisense.com/reporting/oauth/R29vZ2xlIFNwcmVhZHNoZWV0cw==/callback
    where base URL is 'https://baseurl.sisense.com/reporting'. The origin for this base URL will be 'https://baseurl.sisense.com'.
    Note: The Google OAuth2.0 redirect URL doesn’t allow spaces, so Sisense has encoded the provider’s name (Google Spreadsheets) into the base64 format – R29vZ2xlIFNwcmVhZHNoZWV0cw==. If you want to use another OAuth2.0 connector, you should add on this step redirect URL with appropriate base64 encoded provider name.
  10. Click Create. In the OAuth section, you will receive your client ID and secret.

  11. Copy the client ID and secret. You will need to add these credentials to the manifest file, as described in Manifest Configuration below.
  12. In the sidebar under “APIs & Services”, select Library.

    Search for the relevant Google API, open it and click Enable. For example, if connecting to Google BigQuery, enable the Google Big Query API. You can also enable BigQuery Storage API, if you want to use it.

Manifest Configuration

  1. Open the Sisense installation.
  2. Open the SSH connection to your Virtual Machine using IP.
  3. Edit the manifest.json file for OAuth:
    1. Run command cd /opt/sisense/storage/connectors/.
    2. Run command ls -la and choose the Google connector that you need.
    3. Run cd <google_connector_folder_name> (for example, cd googleads).
    4. Open the manifest.json for editing using the Nano or Vim editors (nano manifest.json).
    5. In the manifest.json file, find the OAuth2 block. Add the clientId and clientSecret that you received when you created your Google app:
  4. Save the manifest.json file and close the editor (CTRL+S and CTRL+X in Nano).
    Now you can open the Sisense web application using the domain name (http://www.domain.name:30845) and create a new ElastiCube with the configured source. For example: http://moshe77.corp.sisense.com:30845/<your URL>.