Cognos Analytics

Introduction

This blog article offers a comprehensive, step-by-step guide to establishing a Microsoft SQL Server JDBC data server connection within Cognos Analytics, utilizing Kerberos authentication utilizing constrained delegation.

This implementation builds upon our existing knowledge base for JDBC Kerberos in Cognos Analytics. This blog emphasizes a practical, hands-on approach with visual aids, detailing the creation of a Kerberos initialization file, Keytab file creation, Kerberos login module configuration, and data server connection setup. The methodology centers on utilizing the IBM-JRE bundled with the Cognos Analytics installation.

The article will illustrate how to use the same service account for single-sign-on authentication with Kerberos constrained delegation assigned to the query service (DQM). This setup allows the DQM to perform Kerberos authentication using the service principal name (SPN) provided in the keytab file.

Overview

Technologies Involved:

• Cognos Analytics11.2.4 Fixpack 2  (Single-Server Installation)

• Active Directory Domain Controller

• Microsoft SQL Server

• Windows Server 2016

Prerequisites

1. Cognos Single Sign-On:IBM Cognos Analytics configured for single sign-on through Microsoft Active Directory.

2. Database Authentication:Microsoft SQL Server Database employing the Kerberos protocol for authentication.

3. User Configuration:Active Directory users directly configured on the database server for streamlined access.

4. Driver Compatibility: Ensure the use of the correct SQL Server JDBC driver supporting Kerberos authentication with constrained delegation.

5. Service Account Setup:Identify and establish appropriate service accounts for applications like Cognos Analytics and relevant servers.

6. Kerberos Expertise:Acquire proficiency in Kerberos authentication mechanisms, SPNs, and delegation configurations.

7. Administrative Access:Obtain adequate administrative privileges within the Active Directory domain for delegation settings management.

Content Overview

    1.Create a Kerberos Initialization File

    2.Configure Kerberos Delegation for Service Type Set to MSSQLSvc

    3.Creating the Keytab File

    4.Configuring the Kerberos Login Module

    5.Verify the Kerberos Configuration

    6.Configure the DQM Service Principal Name 

    7.Configure Microsoft SQL Server Data Server Connections with Kerberos

    8.Test the Microsoft SQL Server Data Server Connection

    9.Retrieving Data From Framework Manager And Conducting Data Validation/Testing

Implementation

Before proceeding with the demonstration, it's crucial to note the following points for clarity:

1. The service account responsible for initiating Cognos Services and executing Kerberos SSO authentication within Cognos Analytics is 'cmuser'.

2. The account designated for the query service, facilitating Kerberos SSO authentication with Microsoft SQL Server, will the same account responsible for starting the Cognos Services 'cmuser'.

Create a Kerberos Initialization File

The krb5.conf file serves as the Kerberos initialization file utilized by the JRE Kerberos protocol implementation. It contains essential configuration details, such as the locations of Kerberos Distribution Centers (KDCs), admin servers for the relevant Kerberos realms, defaults for the current realm, settings for Kerberos applications, and mappings of hostnames to Kerberos realms.

The following is a typical format of how the krb5.conf file might appear, based on the information obtained from the KDC server:

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

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

Example 

• Copy the krb5.conf file under C:\Windows and rename the file to krb5.ini

Configure Kerberos Delegation for Service Type Set to MSSQLSvc

The 'cmuser' account, that was used to perform Single-Sign-On Authentication With Kerberos Constrained Delegation in my other blog must now be granted delegation for service type MSSQLSvc. 

The reason for this specific implementation in our demonstration is due to the Cognos Server being set up as a Single Server install. In this scenario, we're employing a single service account ('cmuser') to initiate both the Cognos Services and IIS App Pool. Additionally the 'cmuser' service account, is exclusively designated for the query service.

Create the Keytab File

After generating the SPN for the 'cmuser' service account demonstrated in the following blog, the next step involves creating a keytab file for this service. This keytab file enables passwordless login for the service.

Procedure to follow:

1. Launch the Windows command line with administrative privileges.

2. Execute the following command to create the keytab file:

  

ktpass -out ibmcognosba.keytab -princ ibmcognosba/cmuser@SQUAD5.SUPPORT.COM -mapUser cmuser@squad5.support.com -mapOp set -pass <password for cmuser> -pType KRB5_NT_PRINCIPAL -crypto AES256-SHA1

The principal name follows the format: serviceType/AD_user@Domain_NAME.

For instance, if the service account user_id demonstrated here is 'cmuser', the principal name during keytab file creation will be specified as ibmcognosba/cmuser@DomainName as depicted below.

3.After creating the  ibmcognosba.keytab, and place it in the Cognos_Installation/configuration folder as shown below.

Configure the Kerberos Login Module

Configure the Kerberos login module to enable the IBM Cognos Analytics query service to log in to the Active Directory domain. To facilitate the login, the Java Authentication and Authorization Service (JAAS) package necessitates a configuration file called the jaas.conf file. There two sample files for JAAS which are provided in the IBM Cognos installation directory located under Cognos_Installation/configuration. The example files are named jaas-ibm.config and jaas-oracle.config.Choose the appropriate file based on the JAVA vendor used by the Cognos Server. In this demonstration, the built-in IBM JRE is utilized, hence we'll use jaas-ibm.config to configure the Kerberos login module.

Procedure to follow:

1.Rename the jaas-ibm.config file to jaas.conf and place it in the location Cognos_Installation_dir\ibm-jre\jre\lib\security 

2.Reference the path to the jaas.conf exactly as shown below in the java.security file located under Cognos_Installation_dir\ibm-jre\jre\lib\security within the section commented as "#login.config.url.1=file:${user.home}/.java.login.config.

3.Configure the jaas.conf file to include the principal name exactly as provided in the ibmcognosba.keytab file created earlier and the location of the keytab file.

To validate the Java Authentication and Authorization Service (JAAS) configuration along with the keytab file, execute the following steps:

1. Launch Command Line as an Administrator.
2. Navigate to the directory: Cognos_Installation_dir\bin64.
3. Execute the kerberosTest.bat utility.

You should anticipate the utility to confirm successful verification of the JAAS configuration and keytab file, resulting in an output similar to the example provided below:

Configure the DQM Service Principal Name and Specify the sAMAccountName of the user running Application Tier Components

To set up the login module for Kerberos with single sign-on (Active Directory):

1. Open Cognos Configuration and navigate to Security > Authentication.
2. Choose the Active Directory namespace.
3. Locate the DQM Service Principal Name property and enter the value exactly as specified in the keytab file for the corresponding service principal name as shown below.

Test the Microsoft SQL Server Data Server Connection

In the Signon section, select external namespace and select the Active Directory namespace from the list and Test the connection 

Retrieving Data From Framework Manager And Conducting Data Validation/Testing