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