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.
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:
- Sign out from the Snowflake database.
- 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
- 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).
- Create a new Snowflake data source connection from Data Server Connections
- 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
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."
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
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.