Kerberos Connection using Teradata Studio

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Kerberos Connection using Teradata Studio

This article will help you create a connection in Teradata Studio (or Teradata Studio Express) Windows client to a Kerberos enabled Hadoop cluster.

 

Installing Teradata Studio

Before you install Teradata Studio, make sure your Windows client has been configured to be used within the Kerberos domain. You should first locate your krb5.ini file typically found in your C:\Windows directory. Otherwise, you will need to create the krb5.ini. 

Here is an example for a "sample.com" domain:

[libdefaults]
ticket_lifetime = 6000
default_realm = SAMPLE.COM
clockskew = 13000
default_tkt_enctypes = des-cbc-crc des-cbc-md5 rc4-hmac
default_tgs_enctypes = des-cbc-crc des-cbc-md5 rc4-hmac
checksum_type=2

[realms]
SAMPLE.COM = {
kdc = sample.com:88
default_domain = sampledom
}

[domain_realm]
sampledom = {
.sampledom = SAMPLE.COM
sampledom = SAMPLE.COM

This file must have domain-realm mappings that coincide with that of the KDC (Kerberos Key Distribution Center).

 

Now that you have your krb5.ini file, you are ready to install Teradata Studio or Teradata Studio Express. During the install, be sure to check the box for Kerberos and browse to the location of your krb5.ini file.

 

 KerberosInstall.JPG

 

As the check box indicates, the Studio install will modify your Windows Registry to allow the session key for the TGT (Ticket Granting Ticket) to be accessible to the Java runtime.

 

For Windows XP and Windows 2000, the registry key and value will be changed as follows:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos
Value Name: allowtgtsessionkey
Value Type: REG_DWORD
Value: 0x01

For Windows 2003 and Windows Vista, Windows 7 (and later versions of Windows), the registry key and value will be changed as follows:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
Value Name: allowtgtsessionkey
Value Type: REG_DWORD
Value: 0x01  

 

The Studio install will also copy a TeraJDBC.config file to the Studio install directory. The TeraJDBC.config file is used by Teradata when Kerberos (KRB5) is chosen as the Teradata authentication mechanism in the Studio connection profile wizard. Below is a sample TeraJDBC.config file. 

com.sun.security.jgss.initiate
{
com.sun.security.auth.module.Krb5LoginModule sufficient useTicketCache=true doNotPrompt=true debug=true;
};
other
{
com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true debug=true;
};

In addition, the Studio install will also modify the TeradataStudio.ini or TeradataStudioExpress.ini to include additional parameters needed for Kerberos authentication. Here is an example of the TeradataStudio.ini file:

studioini.png

 

Because the registry has been modified, the Studio install will prompt you to restart your windows client machine. This is important for the changes to take effect.

 

As a Windows client within your Kerberos domain, the client should have a Kerberos key or TGT. This means that ‘kinit’ or some form of Kerberos authentication was run to request a TGT from the KDC. You can run the ‘klist’ command to confirm that the TGT exists. If you don't see your Kerberos TGT, contact your Kerberos administrator or Hadoop support.

 

You can now launch Studio and create your Hadoop connection.

 

Create a connection profile to your Kerberos enabled Hadoop system

From the Data Source Explorer or Navigator, you can press the New Connection Profile icon to launch the New Connection Profile wizard. Select Cloudera or Hortonworks as your Hadoop provider and check the Kerberos box.

kerberosconnection.png

Next, choose a Connection Service. All Hadoop connections services are supported except for TDCH. (TDCH support will be provided in a future release.) Depending on what Hadoop connection service is selected, the wizard will prompt the user for the required Kerberos information. 

If Hive or Impala is selected, the connection profile wizard will prompt for the Kerberos realm, which is required as part of the JDBC URL.

kerberoshive.png

If Presto is selected, the wizard will prompt the user for the Java SSL TrustStore Path and Password which is required as part of the JDBC URL.

The TrustStore contains the SSL certificates to use during authentication.

 

kerberosPrestoTrustKey.png

Press Next to enter the WebHCat information for your Hadoop system. Press the Test Connection to verify the connection information.

kerberosWebhcatPing.png

In this example, the Presto JDBC option was chosen as the Connection Service. Press Next and enter the Presto JDBC Connection Properties for your system. Press the Test Connection to verify the connection information.

kerberosprestoping.png

Press Finish to create the Hadoop connection in the Navigator or Data Source Explorer.

 

You are now ready to explore your Hadoop system, run queries, or copy data.

 

Troubleshooting Hadoop Kerberos connection problems

One of the variables added to the .ini file sets the Kerberos debug logging on.

“-Dsun.security.krb5.debug=true”

You can redirect the output of Studio or Studio Express to an output file and view the Kerberos debug information.

kerberosDosRedirect.png

Below is a sample output:

kerberosDosoutput.pngIf you see the following in standard output, “unsupported key type found.. 18” that means your installed Java JRE does not have the unlimited strength JCE_Policy Jars required for Kerberos. You will need to to the following:

Also note when troubleshooting a Kerberos connection problem:

  • the .ini file is case sensitive. Be careful when making any modifications.
  • Properly enabling Kerberos for Hadoop is a complex task. If a JDBC connection is failing, it is very possible there is an issue with configuration on the Hadoop cluster, not necessarily an issue with Studio.
  • The Hadoop JDBC driver does not return detailed error messages, so finding the root cause of an error might require that the Hadoop owner or administrator look at a variety of logs on the Hadoop cluster.

Some example ‘smokecreen’ tests the owner or administrator of a Hadoop Cluster can run to help confirm the Hadoop cluster is configured correctly:

  • run a WebHcat / WebHdfs  curl test

kerberoscurltest.png

  • run a Hive/Impala Beeline test on the Hadoop cluster

kerberoshivetest.png

 

1 REPLY
Tourist

Re: Kerberos Connection using Teradata Studio

Fgrimmer,

 

It is a nice post. I did configure Teradata Studio Version: 16.0.2.201703141245 to Connect Cloudera using Kerberos.

 

Problem Description:

 

1. Authentication to CDH with Kerberos is success

2. I am able to list all the databases and related objects in the database explorer

3. Problem is happening when i am trying to query. It is erroring out with not havaing sufficient privileges.

I have all access to my id. 

 

Executed as Single statement. Failed [0 : HY000] AuthorizationException: User 'USER1@MYRELM.COM' does not have privileges to execute 'SELECT' on: demo.simple_parquet_table

 

I checked further and it looks like userid@realm.com is being converted to uppercase 

I would like to see user should be passed as : user1@MYRELM.COM instead. In unix all the user ids are in lower case.

 

I am not sure where these parsing is happening. But it is not consistant. I had created another connection where the user id passed as lower case so i could able to query the tables but after some time it started passing user again as uppercase. Then my query failed with access violation.