KB45540: How to setup Single Sign-on access to Teradata (using Kerberos) for MicroStrategy Intelligence Server on Windows

This document provides steps to setup Single Sign-on access to Teradata (using Kerberos) when the Strategy Intelligence Server resides on a Windows operating system.
 
Note: The steps explained below apply to a Teradata database server installed on a Linux operating system.
 

•  Create user on Active Directory and assign the SPN
   
  
1. A user account needs to be on Active Directory for each Teradata node. By convention, that account is given the same name as the Teradata node, but it can be anything.
2. After the user is created, Kerberos delegation should  be added for that user and a Service Principal Name (SPN) should be set. A separate SPN should be created for each Teradata node.
3. The SPN consists of three parts: a service name, the host name of the Teradata node, and the domain/realm for which the service is being registered.
   
1. The service name is always 'TERADATA'
2. The host name is the fully qualified domain name of the Teradata node as indicated by a DNS lookup.
3. The domain/realm in the domain that contains the users
4.     
   Example Teradata SPN: TERADATA/ts-teradata140.microstrategy.com@MICROSTRATEGY.COM
    
   In the above example, the user domain is the same as that for the Teradata node, but that is always not the case. For example, if the Teradata server was in a domain called 'LABS.Strategy.COM' and the users were in a domain called 'Strategy.COM. In this case, the SPN would be: TERADATA/ts-teradata140.labs.microstrategy.com@MICROSTRATEGY.COM
    
   
• Setup Single Sign-on (SSO) with Teradata
   
  
1. Make sure that Kerberos libraries are installed on the Teradata server, including the Kerberos client libraries
2. Create the keytab on the domain controller (Active Directory)
    
   
   
   

   
   ktpass -princ TERADATA/ts-teradata140.Strategy.com@Strategy.COM -mapuser adtera1 -pass <password> -ptype KRB5_NT_PRINCIPAL -out C:\mytst140.keytab

   
   
   
    
   
3. When the ktpass command is run, a keytab gets generated. This keytab file needs to be copied to the Teradata server.
4. This keytab needs to be merged with any existing keytab file that already exists on the Teradata server. This can be done using the ktutil command as shown below. In the command line, enter the command 'ktutil' to invoke the keytab utility shell. With the ktutil shell, the 'rkt' command will read the newly created keytab and the 'wkt' command will write it to the krb5.keytab file located in the /etc/ folder.
    
   
    
   

   
   ktutil: rkt /opt/teradata/tdat/tdgss/site/mytst140.keytab
   ktutil: list
   ktutil: wkt /etc/krb5.keytab

   
   
    
   Note: 
   If the Teradata backend system is Teradata 14.0 or newer, the keys need to be stored in /etc/teradata.keytab and not /etc/krb5.keytab. The 'teradata.keytab' file can be directly created.
    
   If a krb5.keytab has already been created on a Teradata 14.0 or newer system, copy the contents over to a new file called 'teradata.keytab'. 
   
    
   

   
   cp /etc/krb5.keytab /etc/teradata.keytab

   
   
    
   The keytab file should be owned by teradata:tdtrusted.
   
    
   
   

   
   chown teradata:tdtrusted /etc/teradata.keytab

   
   
   
    
   
5.  In addition to the krb5.keytab (or teradata.keytab), krb5.conf file needs to be setup. Refer to Teradata's Security Guide (obtained from "Teradata at your Service" portal for customers) for information on how to configure this file with the required domains. 
    
   
• Test SSO Configuration on the Teradata Server
   
  
1. On the Teradata server, using the Teradata Administrator account, create a user with logon permissions
    
   
    
   

   
   create user adtera1 as perm=1000, password=adtera1;
   GRANT LOGON ON ALL TO adtera1 WITH NULL PASSWORD;

   
   
    
   
2. Enable external authentication for the Teradata Server. By default, external authentication should be enabled for both the dbscontrol and gtwcontrol. This can be verifief by running the following commands and verifying the value seen for 'external authentication':
    
   
   
   

   
   # gtwcontrol -d
   gtwcontrol: gtwversion 13.00.00.21
   gdo version: 5, created on Tue Nov  2 12:28:51 2010systemname: adea3
   number of host groups: 1, number of gateway vprocs 1
   max logfile size: 5000000
   event trace cnt: 500
   gtwglobal logon name: DBC
   Send DBS error to client: no
   TDGSS Library Version..........: 13.0.0.21
   
   hgid: 1
                   assign vprocid: 8192
                   assigntrace: no
                   logons enabled: yes
                   session timeout in minutes: 20
                   connection timeout in seconds: 60
                   keepalive timeout in minutes: 10
                   max sessions per node: 600
                   malloctrace: no
                   iothread check frequency in minutes: 10
                   max io threads per type (msg/net): 50
                   initial io threads per type (msg/net): 5
                   External Authentication: on
                   Append Domain Name: no
                   Allow Deprecated Logons: no
                   Connection trace: no
           vproc id: 8192 status: Online
   
   
   TDExpress14.0_Sles10:/opt/teradata/tdat/tdbms/14.00.01.02/bin # dbscontrol
       Release 14.00.01.02 Version 14.00.01.02
       DBSControl Utility (Dec 99)
   The current DBS Control GDO has been read.
   Enter a command, HELP, or QUIT:
   DISPLAY GENERAL
   DBS Control Record - General Fields:
   26. ExternalAuthentication         = 0           (On)

   
    
    
   
   
    
   
3. Create a user on the Operating System of the Teradata database server so that single sign-on can be tested on that machine using Teradata's utility called 'bteq'. 'bteq' is run using the operating system user account that matches the database account. The command below will also create the user's home directory.
    
   
   
   

   
   useradd -m adtera1

   
   
   
    
   
4. Test the configuration by using the 'kinit' and 'klist' utilities. Knit will try to authenticate against the domain and create a ticket. The 'klist' command will show the ticket and the keytab file being used. Login as the OS user created in the step above and run the commands as shown below.
    
   

   
   $ kinit
   Password for adtera1@LABS.Strategy.COM:

   
   
   
   

   
   $ klist -c
   Ticket cache: FILE:/tmp/krb5cc_10000
   Default principal: adtera1@LABS.Strategy.COM
   Valid starting     Expires            Service principal
   10/03/13 14:18:47  10/04/13 00:18:56  krbtgt/LABS.Strategy.COM@LABS.Strategy.COM
   
           renew until 10/04/13 14:18:47
   Kerberos 4 ticket cache: /tmp/tkt10000
   klist: You have no tickets cached

   
   

   
   $ kvno TERADATA/ts-teradata140.labs.Strategy.com@LABS.Strategy.COM
   TERADATA/ts-teradata140.labs.Strategy.com@LABS.Strategy.COM: kvno = 4

   
   
   
    
   
5. Still logged in as the same OS user, run 'bteq' to verify SSO. When prompted for the userID and password when using this test utility, users can just hit enter without providing any information.
    
   
   
   

   
   $ bteq
   Teradata BTEQ 14.00.00.02 for LINUX.
   Copyright 1984-2011, Teradata Corporation. ALL RIGHTS RESERVED.
   
   Enter your logon or BTEQ command:
   
   .logon tst140/,,
   
   UserId:<just hit return>
   Password: <just hit return>
   
   
   
   Logon successful.

   
   
   
    
   
• Configure Strategy Intelligence Server and Database Instance/Connection
   
  
1. Update the database object in the Strategy metadata
    
   
1. From a Strategy Desktop (Strategy Developer 9.4.1.) installation, find the DATABASE.PDS file (found under the Program Files(x86)\Common Files\Strategy folder). Create a backup of this file before making any changes.
2. Edit this file and under the required DBMS type (for example: <DSSOBJECT TYPE="DBMS" NAME="Teradata 13.10" ID="@dbms119">), under <PROPERTYSET NAME="System SQL Templates">, the following text needs to be added. Save and close the file after making the required changes.
    
   
    
   

   
   <PROPERTY NAME="SupportsKerberos" VALUE="1"/>

   
   
    
   
3. Update the database object in the Strategy metadata using the steps from the following Strategy Knowledge Base document:
    
   KB5779: How to update the database type in MicroStrategy 9.4.x and 10.x?
1. Setup Kerberos (integrated) authentication for Strategy Intelligence Server 9.x. running on Windows. Refer to the following Strategy Knowledge Base document for details on setting this up:
    
   KB19580: How to setup Kerberos (Integrated) authentication for the MicroStrategy Intelligence Server 9.x - 10.x on Microsoft Windows operating systems.
•  Finish the configuration to setup Kerberos with Teradata and Strategy
   
  
• For the required Strategy user, a trusted user ID should be added as shown below.
  

• Ensure that the database login in the Database Instance is using Windows authentication as shown below.
  
  

• To use database pass-through i.e., for the credentials to be passed to the database for report executions, select the metadata authentication type to 'Kerberos' from the project configuration editor, as shown below. Select the database instance that is connecting to the Teradata warehouse and is configured to use windows authentication (that is seen in step 2 above).
  
  

After the setup is complete, login to the client machine using the Kerberos user defined in Active Directory (adtera1). The user should be able to login to the Strategy Intelligence Server without being prompted for credentials and the same credentials should be passed on to the Teradata database server for report execution as well.