Cognos Analytics

 View Only

Cognos Analytics: Configuring Snowflake Data Server Connection with Federated Authentication via OKTA

By Dhruva J Mazumdar posted Wed June 28, 2023 05:05 PM

  

Introduction

Learn how to set up an OpenID Connect Namespace with Snowflake data server connection from Cognos, configured with an OKTA IDP with SAML 2.0 compliant service/application for federated authentication from Active Directory with this easy step-by-step guide.

By following the instructions, you'll be able to seamlessly connect your Snowflake data server to your Cognos environment using your existing OKTA IDP credentials. This will ensure secure and efficient data access for your team while also simplifying your authentication processes.

Environment

Active Directory Domain Controller

OKTA provider Integrated with Active Directory 

On-Premise: Cognos Analytics 11.2.4 

Outline

This blog outlines the steps required to successfully connect Cognos Analytics to a Snowflake data server using federated authentication from Active Directory.

 The following steps outlined below will be covered in this blog post:

  1. How To Set up the OKTA IDP service/application and ensure it is configured for SAML2.0 authentication.
  2. How To Integrate Active Directory with the OKTA SAML2.0 application 
  3. How to Create  SECURITY INTEGRATION between Snowflake and OKTA. 
  4. How To Create a data source connection in Cognos Analytics with Snowflake
  5. Testing  the data source connection in Cognos Analytics using Active Directory configured for Federated authentication with OKTA

The results should indicate a successful connection to the Snowflake data server with federated authentication through the IDP service/application.

Steps

A.Configuration OKTA with Snowflake Application

1. Create a Snowflake application in Okta from Application-->Browse App Catalog

2. In the Label field for the application, you can specify any name. 
In the SubDomain field for the application, you need to enter the account identifier of your Snowflake account. The account identifier includes the name of the account along with its organization. The account identifier for the Snowflake account. 
The account identifier for the Snowflake account can be found in the Snowflake database at the very bottom as provided below
3. Assign an Active Directory user to the Snowflake application within Okta, as covered in the Additional information section of this document where the integration between Active Directory and Okta is explained
4. In Step 1, for the Snowflake Application that was created, select 'SAML 2.0' as the sign-on method and make a note of the Issuer, SignOn URL and Certificate. This information will be required during the setup of Security Integrations between OKTA and SNOWFLAKE in the later part of the process.
5. Under Credentials Details, ensure that the 'Application Username format' is configured to use the AD user principal name. However, it is possible to configure it to utilize other AD attributes such as AD SAM account name, email, or AD Employee ID, depending on the requirements and how the user profile is set up in Active Directory.
In summary, we have successfully set up the OKTA provider with a Snowflake application configured for SAML2.0 authentication with a user from the Active Directory assigned to the application. 
The subsequent steps will guide you through setting up the Security Integration with Snowflake, utilizing the information obtained from Okta. This integration enables users to seamlessly authenticate and access the Snowflake database using their Active Directory credentials integrated with Okta.

B.Setup Snowflake with OKTA as the Identity Provider for Federated Authentication from Active Directory

1. Logon to the Snowflake Database 

2. Begin by creating a user in Snowflake, ensuring that their email address or User Principal Name (UPN) matches the Active Directory user imported into Okta. This synchronization will establish a connection between the Snowflake user and the corresponding Active Directory user in Okta.

As an example, we will be using the following  SNOWSQL for creating the user in SnowFlake 

CREATE OR REPLACE USER

 "djokta"

login_name = 'djokta@frivoled.ibm.com'

email = 'djokta@frivoled.ibm.com'

default_warehouse = 'dbcert'

must_change_password = false

default_namespace = 'dbcert.dbcert'

default_role = 'ANALYST'

3. Grant the "Analyst" role to the newly created user, and also assign the necessary Role and Database Usage privileges to the role as provided in the sample SNOWSQL below. 

GRANT ROLE analyst TO USER djokta;

GRANT ROLE PUBLIC to user "djokta@frivoled.ibm.com"

GRANT ROLE ANALYST to user "djokta@frivoled.ibm.com" 

GRANT USAGE ON DATABASE DBCERT TO ROLE analyst;

GRANT USAGE ON WAREHOUSE dbcert TO ROLE analyst;

GRANT USAGE ON SCHEMA public TO analyst;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

Setting up SAML Security Integration between Okta and Snowflake

To configure the integration, ensure that the user in Snowflake has the "ACCOUNTADMIN" role. Then, execute the command "create CREATE SECURITY INTEGRATION" provided in the sample blow with the gathered parameters from the Snowflake application created in Okta mentioned in step A(4). 

create security integration OKTASAML

TYPE = saml2 

ENABLED = true

SAML2_ISSUER = 'http://www.okta.com/exk9e0fsa1tMOlv2m5d7'

SAML2_SSO_URL = 'https://dev.okta.com/app/snowflake/exk9e0fsa1tMOlv2m5d7/sso/saml'

SAML2_PROVIDER = 'OKTA'

SAML2_X509_CERT = 'MIIDqDCCApCgAwIBAgIGAYfk7elCMA0GCSqGSIb3DQEBCwUAMIGUMQ swCQYDVQQGEwJVUzETMBEGA1UECAwKQ2FsaWZvcm5pYTEWMBQG A1UEBwwNU2FuIEZyYW5jaXNjbzENMAsGA1UECgwET2t0YTEUMBIGA1 UECwwLU1NPUHJvdmlkZXIxFTATBgNVBAMMDGRldi02ODY5MTcyMDEc MBoGCSqGSIb3DQEJARYNaW5mb0Bva3RhLmNvbTAeFw0yMzA1MDQwN DAyMjlaFw0zMzA1MDQwNDAzMjlaMIGUMQswCQYDVQQGEwJVUzETM BEGA1UECAwKQ2FsaWZvcm5pYTEWMBQGA1UEBwwNU2FuIEZyYW5ja XNjbzENMAsGA1UECgwET2t0YTEUMBIGA1UECwwLU1NPUHJvdmlkZXIx FTATBgNVBAMMDGRldi02ODY5MTcyMDEcMBoGCSqGSIb3DQEJARYNa W5mb0Bva3RhLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQo CggEBAJtPrrnITIt/0x2/iebtCZXYjD/KYnoIgz2Ii8G5mQ9kdRpFv8FFY/qpBFCk 044DpQ7Ylc2r9sU2Pc9a0dtzVQ3gBqjz2nYFozdZEv/9gR4qUkPKaFeIfwWhdk+ FwiN3Up7jv5GGz0QckOlCA26hCepR2baqGXRXups9uoFuq7nAGpe9ri+vucdD pugIDaVAVq9aB2jgW57YsMEwIjcFLnCmmkVDDzmN9kk+zIJfGenW1qhlKvz TSwAz8oOFF2Ub9Cv6vQ79LxAd2mQkyqwKrcGhb8D1gnTxe+jav11A0wQCzm d8/NAhBwkVtZf39oGoGg33L+kqVS8JcvfTjH2ib0ECAwEAATANBgkqhkiG9w 0BAQsFAAOCAQEADV8+AtnrtIZDl1Df0UPnvqg8qOGg3lLe0aUeGMZyp1eM 0YpB2g0TVJAeZAgqO//G8P/70Is6xvMXw8QFZK/8dRwVTzYUyiLj05t3XpnHj xvC9QCPSQgqTztTNPho0dEp8Ia0rCPIjpoDRHdIBhQLMJ9pWgO1ixUqTKquh NoT59ziH5L/ltl4JW0jg5qfMwtRS7vWh32Dmb+kla+yLO8nosIMtmpo5yfXsPlzE atPFkCN4fmQMBiW3xIadN5shPngMD1tcdwqD36u2mhw3aSq2eURxkQqfUEkb wSfXMylBd244BrNawZiPsrUCokpEv6NBpgy0PNmiYhOFGE7x5DA/g=='

After completing the integration above, it is crucial to validate the authentication on Snowflake to ensure the Security integration has been set up correctly.

Follow the steps below to conduct the authentication test:

  1. Sign out from the Snowflake database.
  2. Test the authentication by using the SAML2_SNOWFLAKE_ISSUER_URL, which serves as the account identifier for the Snowflake database.

eg: https://nibywbh-ko24816.snowflakecomputing.com/console/login?fedpreview=true, where nibywbh is the name of the Organization and ko24816, is the name of the  account as shown in the image for Step A(2)

The user should expect to see the Snowflake Sign-in page, which will now display an additional option for Single Sign-On (SSO). This enhancement allows users to choose the SSO option and seamlessly authenticate using the credentials for Active Directory-integrated with OKTA as shown below.

By signing out and testing the authentication using the SAML2_SNOWFLAKE_ISSUER_URL, you can verify the successful setup of the Security integration and ensure that users can authenticate seamlessly with Okta and access the Snowflake database.

C. Creating and Testing the Snowflake Data server connection from Cognos Analytics 11.2.4 

  1. Access Cognos Analytics, using the Active Directory namespace and utilize the same account that was assigned to the Snowflake application created on the Okta provider, as mentioned in step A(3).
  2. Create a new Snowflake data source connection from Data Server Connections
  3. Provide the connection string details under the JDBC URL as provided in the example below :
        jdbc:snowflake://nibywbh-ko24816.snowflakecomputing.com

       4.Under Connection properties provide the following details : 

       oktausername=#$account.personalInfo.email#;authenticator=https://dev68691720.okta.com;role=analyst;db=SNOWFLAKE

    5. 

To configure the Authentication Method, follow these steps:

  1. Choose "Use an external namespace" as the Authentication Method.
  2. Select the Active Directory namespace.
  3. Proceed to test the connection to ensure it is functioning correctly.

This concludes the learning provided in this blog. However, for the sake of completeness, the steps for integrating Okta with Active Directory have been provided in the Additional Details section.

Please note that the steps mentioned for Snowflake and Okta implementation, as well as the integration with OKTA and Active Directory, are provided to illustrate how these components fit together with Cognos Analytics.

 While these topics may be outside the direct scope of Cognos Analytics, they are included to provide a comprehensive understanding of the overall integration and functionality.

Additional Information 

This document does not cover the setup and configuration of a domain controller, as it falls outside the scope of the current topic. Setting up and configuring a domain controller is a separate technical task that requires specific expertise. For information on setting up and configuring a domain controller, please refer to other relevant resources or consult with appropriate technical documentation or experts.

For the scenario mentioned in this document, the user created in Active Directory for testing purposes is referred to as "djokta@frivoled.ibm.com."

Setting up integration between Okta and Active Directory

1. Create an Okta account for your company or organization. 

2. Log into your Okta account as a user with administrator privileges

3. Navigate to Directory--àDirectory Integrations and click on Add Directory

4. Click on the option ‘Add Active Directory’ and select Set Up Active Directory

5. Download the Okta Active Directory agent on the server hosting the domain controller and complete the installation process.

6. After completing the installation process, the user will encounter a popup screen that displays information related to the domain controller
7. Allow the Okta agent to create a dedicated user account for managing the agent service and AD user synchronization with Okta, you will need to delegate the necessary tasks to the user account created by Okta
8. After completing the setup process, you should be able to verify the Okta Active Directory Agent running on the domain controller.
9. After completing the necessary steps, return to the Okta console and navigate to the "Directory Integration" section. From there, initiate the import process to bring the user and groups from Active Directory into Okta.
   
10. After successfully importing users and groups from Active Directory, you should be able to view the user created on the Active Directory domain controller in the Okta admin portal under "Directory" -> "People" section. The user should appear among the imported user accounts, along with other relevant information associated with the user, such as their username, email address, and group memberships.
Important Note: Make sure the User created in Active Directory includes an email address. Email addresses are required to map the users in Okta with the corresponding users in Snowflake.
0 comments
44 views

Permalink