Single Sign-on via OAuth

One of the ways users can connect to data sources using their own access rights is via OAuth Authentication. This type of authentication makes use of the user's credentials to connect and authenticate access to a given data source directly. The process is often used in big organizations that have centralized security and are using one framework to secure all data assets.

It is possible to establish a single sign-on (SSO) framework using an OAuth provider (like Microsoft Azure Active Directory) that allows users to authenticate in Pyramid and then credential individually downstream to a data technology. Pyramid supports SSO via OAuth for the following data sources:

Note: This OAuth option is currently only available for the preceding data sources. Other SSO techniques are available for other database technologies like MS SQL Server, MS OLAP, SAP Hana, and SAP BW.

Enabling OAuth for Data Access

Where relevant, the Security tab contains an Authentication Method drop down to select the type of connection to be employed with that particular connection instance. After the default Username and Password there are a few possible options for OAuth SSO, depending on the data source:

  • Single Sign-on (OAuth) - Specific User: This option will use the identity of a specific user to authenticate through identity provider, retrieve the OAuth security identity for that user, then connect to the data source as that user. All users will share the same identity when querying data.

  • Single Sign-on (OAuth) - End User: (For Amazon Athena, Amazon Redshift, Azure Synapse, Big Query, Box, Google Analytics, Google Drive, and MS Fabric.) Each user will be prompted to sign in when starting Pyramid or when connecting to the data source. This is a "one off" event. The user's sign in code will be stored and reused for subsequent data access. Pyramid will automatically refresh this as needed. All users will share the Client ID and Client Secret defined here.

  • Single Sign-on (OAuth) - Proxy 1: (Snowflake only.) As well as establishing SSO between the provider, Pyramid, and the data source by an individual user, this option uses the user name contained in the Proxy 1 user information field for onward connection to other data sources, for example MS OLAP or SAP BW.

  • Single Sign-on (OAuth) - Proxy 2: (Snowflake only.) As above, but uses the Proxy 2 user information field for onward connection to other data sources.

Note: Proxy 1 and Proxy 2 are the equivalent of the "end user" option, but are only used for Snowflake. Details on setting up the proxy account can be found here.

Setting up the Provider in the Admin Console

When setting up the OAuth, supply the following details:

  • Client ID: Client identifier associated with the data application to connect to.
  • Client Secret: Client Secret identifier associated with the data application to connect to.
  • Scope: (Not always present.) Data source string that can limit the operations and roles permitted by the access token and what the user can access in the data source.
  • OAuth Settings: Select Custom Settings from this drop-down to override the Global settings with settings for the specific identity provider:
    • JSON Web Keys URI: The location of the JSON Web Keys Set.
    • OAuth Token Endpoint: The string used by Pyramid to get an access token or a refresh token.
    • OAuth Authentication Endpoint: The string used by Pyramid to get an access token or a refresh token.

Note: Some of the global settings can be setup in the Global Settings page if you need to repeat them across multiple data cards. In this case, you need to select Global Settings from the OAuth Settings drop-down.

Redirect

Pyramid requires a redirect page to define where the provider returns the OAuth tokens requested. Pyramid will use https://<Pyramid server name>/AuthenticateCallbackPage by default, however, it must be set on the Global Settings page. A button there will populate the field with this value. You can also specify an alternate redirect page if required.

  • Click here for more details on BigQuery OAuth setups
  • Click here for more details on Box OAuth setups
  • Click here for more details on Amazon OAuth setups (Athena and Redshift)
  • Click here for more details on Google OAuth setups (Big Query, Google Analytics, and Google Drive)
  • Click here for more details on MS Fabric setups

User ID Flow

Specific User

The Proxy 1 and Proxy 2 user information fields can be used to inject alternative account names to be used with alternative system authentications. For example, the user's Active Directory account needed for Microsoft SSAS authentication, or the user's SAP BW login for onward connection in other SSO environments, like Azure or Snowflake.

Shared User

For the "Single Sign-on (OAuth) - Specific User" option, all users will be sharing the same Client ID, Client Secret, and Scope, but will also share the same login connection to the data source. Supplying the User name and clicking Connect will connect to the data source and return the OAuth Refresh code to be used by Pyramid to connect to the data source.

  • Connect Button: Connect to the data source and retrieve the Refresh Code.
  • User Name: (Snowflake only.) The user name to use when connecting to the data source.
  • OAuth Refresh Code: Refresh authorization code returned by the data source.

User Experience

Prompts

When a user logs into Pyramid or attempts to access a data source authenticated through OAuth, they will be prompted to connect to the relevant data source (like Snowflake) using their account details. This will be used to connect that user to their data, enabling user level data security (and effectively sharing the Client ID and Client Secret and Scope as entered).

Initial Login

The first time a user connects, a pop up from the identity provider opens during the authentication of that user. This is by design from the provider and Pyramid has no control over this. Note: There may be a small delay on first connecting to the data source while the provider authenticates the user and generates the OAuth tokens needed. This delay will reoccur should the OAuth access tokens expire. In this situation, re-authentication is required. The time interval before expiration is set by the provider administrators.