Cognos Analytics

 View Only

Cognos Analytics: Configuring Kerberos Constrained Delegation for Microsoft SQL Server JDBC Data Server Connections

By Dhruva J Mazumdar posted Wed December 20, 2023 04:06 AM



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.


Technologies Involved:

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

  • Active Directory Domain Controller

  • Microsoft SQL Server

  • Windows Server 2016


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



    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:


    default = C:/krblogs/krb5libs.log
    kdc = C:/krblogs/krb5kdc.log
    admin_server = C:/krblogs/kadmind.log
    default_realm = <Default Kerberos realm for the client>
    dns_lookup_realm = false
    dns_lookup_kdc = false
    ticket_lifetime = 24h
    renew_lifetime = 7d
    <Name of a Kerberos realm> = {
    kdc = <IP Address of the Kerberos realm aka the kerberos server>
    admin_server = <IP Address of the Kerberos admin server>
    default_domain = <Name of a Kerberos realm>
    .<<Name of a Kerberos realm> = <Default Kerberos realm for the client>



    default = C:/krblogs/krb5libs.log
    kdc = C:/krblogs/krb5kdc.log
    admin_server = C:/krblogs/kadmind.log
    default_realm = SQUAD5.SUPPORT.COM
    dns_lookup_realm = false
    dns_lookup_kdc = false
    ticket_lifetime = 24h
    renew_lifetime = 7d
    kdc = 
    admin_server =
    default_domain =
    [domain_realm] = SQUAD5.SUPPORT.COM
    Note:The displayed IP addresses for the KDC and admin_server services are fabricated for confidentiality. However, the purpose of their display is to indicate that these services share identical IP addresses. 
    After creating the krb5.conf file the following are the next steps: 
    • As this is a single-server setup, position the Krb5.conf file under CA_INSTALLS\ibm-jre\jre\lib\security, given that we are utilizing the IBM JRE bundled with the Cognos installation.

    • 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 -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 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.

    Verify the Kerberos Configuration

    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.


    5.Locate the the property 'Application Tier Components sAMAccountName' and enter 'cmuser' which is the account designated to account designated for the query service and facilitating Kerberos SSO authentication with Microsoft SQL Server.
    Configure Microsoft SQL Server Data Server Connections with Kerberos
    Configure the connection string for Microsoft SQL Server with the respect to the available SQl Server host , port and database name along with the Kerberos connection properties 'integratedSecurity=true;ibmcognos.authentication=java_krb5;authenticationScheme=JavaKerberos;' as provided 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