Cognos Analytics

 View Only

Cognos Analytics: Configuring Snowflake Data Server Connections with Microsoft Azure OpenID Connect Using JSON Web Token (JWT)

By Dhruva J Mazumdar posted Mon December 18, 2023 01:06 AM

  

Introduction

This blog presents a comprehensive, step-by-step guide to integrating a Snowflake dataserver connection with Cognos Analytics through Azure OpenID Connect using JSON Web Tokens (JWT).

Throughout this guide, we'll delve into critical aspects, beginning with the creation of custom scopes within the Azure application dedicated to Cognos, uncover the essential API permissions required for  this Azure-Cognos and explore the creation of a security integration within the Snowflake database, aligning it with the client ID of the Azure application configured for Cognos Analytics.

The culmination of this walkthrough involves testing the dataserver connection from Cognos Analytics, utilizing Azure OpenID Connect as the external namespace thereby passing the JSON Web Tokens (JWT) for a successful connection.

Overview

Technologies Involved:

  • Cognos Analytics 12.0.x and 11.2.4 Fixpack 2
  • Azure Open ID Connection with Cognos
  • Snowflake Database

Prerequisites

Before delving into this guide, it's essential to have Cognos Analytics configured with Azure OpenID Connect. This blog assumes a pre-established setup of Microsoft Azure OpenID Connect with Cognos Analytics. While the blog focuses on the integration of Snowflake Dataserver with Cognos Analytics using Azure OpenID Connect and JSON Web Tokens (JWT), the configuration steps for Azure OpenID Connect with Cognos Analytics won't be covered in this guide.

Content Overview

A. Ensure you have a user in Azure Active Directory that matches a user in Snowflake

i) User Synchronization Between Azure Active Directory and Snowflake

 Ensure synchronization between Azure Active Directory and Snowflake by creating a user with identical userid on both platforms. This alignment is crucial for a seamless integration between Azure OpenID Connect and Snowflake.

ii) Managing Snowflake Role and Azure Scope for User Authorization

When creating a user in Snowflake, take note of the assigned Snowflake role. This role will play a pivotal role in establishing a connection between Snowflake and Azure. In the upcoming sections of this documentation, you'll witness the utilization of this Snowflake role when creating a scope in Azure.

Keeping this role information handy is imperative, as the scope configured in Azure will act as an additional connection property during the setup of the Snowflake dataserver connection in Cognos Analytics.

This meticulous preparation ensures a smooth and coherent integration process between Snowflake, Azure, and Cognos Analytics an ensuring only the user will the correct role can access the designated  database on Snowflake. Its primary function is to control access ensuring that only users possessing the designated role are granted access to the Snowflake database through Cognos Analytics.

B. Configuring the Azure Application for Cognos Analytics as an Authorization Server for OAuth Access Token Granting

i)Configure the Azure Application for Cognos Analytics as an Authorization Server

Configure the Azure Application for Cognos Analytics as an Authorization Server by exposing an API with a scope having the name of the Snowflake role with the prefix 'session:scope:' to Grant the Access Tokens on behalf of the user authentication.

C. Configuring the API Permission for OAuth client configured for Cognos Analytics

i)Configure the API permission for the OAuth client configured for Cognos Analytics that will request an Access Token for itself.

Azure AD supports two different OAuth flows in which an OAuth Client can get an access token.

    a)The authorization server can grant the OAuth client that is the Azure application created for Cognos Analytics an access token on behalf of the user.

    b)The authorization server can grant the OAuth client that is the Azure application created for Cognos Analytics an access token for the OAuth client itself.

    This guide's implementation allows the single Azure application configured with Cognos to serve dual roles—acting as both the Authorization Server capable of granting access tokens on behalf of users and  itself thereby eliminates the need for two separate Azure applications, as provided in Snowflake documentation—one designated as the Snowflake OAuth Resource, and the other as the Snowflake OAuth Client.However, it's crucial to note that this implementation is tailored specifically for Cognos Analytics due to its reliance on the preconfigured Azure application within the Cognos environment.

    D. Creating the Snowflake Security Integration with Azure AD of Type External OAuth

    i.)Snowflake Security Integration

    In this critical step, we establish a security integration within Snowflake, to ensure that Snowflake can communicate with Microsoft Azure AD securely,validate the tokens from Azure AD and provide the appropriate Snowflake data access to users based on the user role associated with the OAuth token.This integration plays a pivotal role in validating tokens received from Azure AD, ensuring provisioning of precise Snowflake data access, tailoring permissions based on the user role associated with the OAuth token.

    E. Testing the Snowflake Dataserver connection from Cognos Analytics

    i.)Testing the Snowflake JDBC connection in Cognos Analytics

     In the final phase of this guide, we'll conduct a test validating the Snowflake Dataserver connection within Cognos Analytics which involves the incorporation of additional connection parameters passed as a scope to the Azure Authorization Server.

    Step-by-Step Guide

    F. Ensure you have a user in Azure Active Directory that matches a user in Snowflake

    The designated user from Azure Active Directory for our demonstration is AZ89@IBM8991.ONMICROSOFT.COM. We will create an identical user with the same id on Snowflake database as demonstrated below: 

    ==============================================================

    CREATE OR REPLACE USER
     
     "Az89@ibm8991.onmicrosoft.com" 
     
    login_name ='Az89@ibm8991.onmicrosoft.com' 
     
    email = 'Az89@ibm8991.onmicrosoft.com' 
     
    default_warehouse = 'COMPUTE_WH' 
     
    Password = 'YOUR_PASSWORD_HERE'
     
    default_namespace = 'SNOWFLAKE_SAMPLE_DATA.TPCH_SF1' 
     
    default_role = 'ANALYST'
    =====================================================

    G. Configuring the Azure Application for Cognos Analytics as an Authorization Server for OAuth Access Token Granting

    1. Navigate to the Microsoft Azure Portal and authenticate.

    2. Navigate to Azure Active Directory.

    3. Click on App Registrations

    4. Select the application created for the Cognos Analytics application

    5. Click on Expose an API 

    6. Click on the Set link next to Application ID URI to set the Application ID URI as demonstrated below

    ==========================================================================================

    H. Click on Add a scope to add a Snowflake Role as an OAuth scope for OAuth flows where the OAuth client application created for Cognos acts on behalf of a user. In this scenario, the default role assigned to the user AZ89@IBM8991.ONMICROSOFT.COM is 'Analyst'.

    I. Configuring the API Permission for OAuth client configured for Cognos Analytics
    8.Next Click on API Permissions

    9.Click on Add Permission.

    10.Click on My APIs.

    11.Select the scope name that was created in step H as shown below

    Application ID URL and Scopes
    J. Creating the Snowflake Security Integration with Azure AD of Type External OAuth
    Create the Security integrations on Snowflake to ensure that Snowflake can communicate with Microsoft Azure AD securely, validate the tokens from Azure AD, and provide the appropriate Snowflake data access to users based on the user role associated with the OAuth token.
    In order to create the Security integration you need to make a note of the following Azure Endpoints:
    • AZURE_AD_ISSUER which can be found for the Federation metadata document located under "entityID" parameter
    • The "jwks_uri" which can found from the Discovery endpoint URL 
    • The Client Identifier of the Azure application configured for Cognos
    • EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM relies on the user's choice for mapping in Azure AD to Snowflake. For this demo, I'm using the User Principal Name (UPN) as the mapping claim
    • EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE will be the attribute in Snowflake the user choose to use for the mapping. I am using the attribute login_name as the Snowflake User Mapping attribute.

    ==================================================================================

    CREATE SECURITY INTEGRATION AZURE_INT

    TYPE = EXTERNAL_OAUTH

    ENABLED = TRUE

    EXTERNAL_OAUTH_TYPE = AZURE

    EXTERNAL_OAUTH_ISSUER = 'https://sts.windows.net/21fbe893-1f95-4b00-95f7-dxcsdsxxxxxsxx/'

    EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.microsoftonline.com/21fbe893-xxxxxxxx-95f7-xxxxxxxxxxx/discovery/v2.0/keys'

    EXTERNAL_OAUTH_AUDIENCE_LIST = ('https://ibm8991.onmicrosoft.com/16de74eb-xxx-xxx-xxxx-c7843d938799') <THE APPLICATION ID URI AS SHOWN IN THE PICTURE BELOW>

    EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'upn'

    EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name';

    ==========================================================================================================================

    Application ID URL and Scopes

    =============================================================================================================================================

                                                                  Snowflake Security Integration 

    Security Integration on Snowflake
                                                                          
    K. Testing the Snowflake Dataserver connection from Cognos Analytics
    Test the Snowflake JDBC connection in Cognos Analytics with using the scope created in Azure in step B(7).Include the Snowflake name-value pair authenticator=oauth
    JDBC URL: jdbc:snowflake://<snowflake-account-url.canada-central.azure.snowflakecomputing.com?warehouse=COMPUTE_WH?database=SNOWFLAKE_SAMPLE_DATA?schema=TPCH_SF1&authenticator=oauth
    Connection properties: ibmcognos.oidc.scope<the custom scope created on Azure>
    Id-token received at the time of Authentication with the Cognos application using Azure
    Resulting Access Token exchange with the Snowflake Database from the Cognos Application
    ACCESS TOKEN
    Conclusion
    In summary, this blog outlines the essential steps to configure a Snowflake connection in Cognos via Azure using JSON
    Web Tokens (JWT). It's important to note that certain steps involve 3rd-party vendors beyond IBM's scope of support. The
    provided information aims to offer a comprehensive understanding of the necessary configurations across different
    platforms for a functional Snowflake JDBC connection in Cognos  Analytics. IBM does not assume responsibility for any
    changes in the technical aspects of 3rd-party vendors over time. It's recommended to refer to the respective vendor's
    information for updated  instructions related to the tasks outlined in this blog.
    0 comments
    64 views

    Permalink