KB289333: Connecting to Impala on a Secured Cloudera CDH 5.5 server in MicroStrategy 10.3 and later

This TN explains in detailed steps how to connect to a Secured Cloudera CDH cluster using Kerberos authentication. This TN applies to Strategy 10.3 and later versions only.
  
 

Connecting to Impala on Secured Cloudera CDH cluster when Strategy Intelligence Server is installed on Windows

Creating a DSN

• Login to Windows as a user who is in your Active Directory and is authorized to use Impala. In this case, the example user is named "krbusera".
• Make sure that a ticket generated for this user is listed when running "klist" in a command prompt.
• Create the DSN through the Windows ODBC Administrator using the Strategy Impala OBDC driver.
  

• DSN Screenshot:
  

•  
  Note: Make sure that Authentication Mechanism is set to Kerberos and Service Name = "impala", and that the values for Realm and HostFQDN are entered correctly (consult the Kerberos administrator to obtain these values if necessary).
  
• Click the "Test..." button to ensure that the test connection is successful.
  
• Open a DB Query tool session and you should be able to connect and run queries.

Connecting to Impala in Strategy Web (DSN-less connectivity):

• Start the Intelligence server as the Active Directory user ("krbusera" in this example) and not the Local System account.
  

• Open the MSTR Web Interface and select the desired project→Add External Data→ Hadoop→ Impala
• Add a new DSN-less Data source. Select Show connection string and edit the connection string. Add the following parameters
  
• AuthMech=1
• KrbFQDN=e.g quickstart.cloudera
• KrbRealm=e.g LABS.Strategy.COM
• KrbServiceName=impala
• DSN-less Screenshot:
  

• Sample Connection String: DRIVER={Strategy Impala ODBC Driver}; Host=10.27.10.109; Port=21050;SCHEMA=wh2parquet;AuthMech=1;KrbFQDN=quickstart.cloudera;KrbRealm=LABS.Strategy.COM;KrbServiceName=impala;
  
• Save the connection and click on it to load the tables.
• Tables should load and you can then create a query, publish a cube, etc
   

Connecting to Impala on Secured Cloudera CDH cluster when Strategy Intelligence Server is installed on Linux

Creating a DSN to Impala on Secured Cloudera CDH cluster on Linux

• Login to the Linux OS as a user who is in your Active Directory and is authorized to use Impala. In this case, the example user is named "krbuserb".  You can use the sudo su <domain>\\krbuserb statement to switch to the kerberos user from the terminal.
• Verify the location of the krb5.conf file on the Linux OS or modify the krb5.conf as instructed by your administrator.  Strategy ships an empty krb5.conf file in the installation directory that can also be used.  An example of the contents below:
  

•  While logged in as the Kerberos user, set the KRB5_CONFIG environment variable to the location of the krb5.conf file.  For example,

export KRB5_CONFIG=/etc/krb5.conf 

export KRB5_CONFIG=/opt/MicroStrategy/Strategy-Linux/krb5.conf

• Verify that the Kerberos user can successfully create a ticket using the kinit command.  For example,

• 
  -sh-4.1$ kinit krbuserb@LABS.Strategy.COM
  Password for krbuserb@LABS.Strategy.COM:
  -sh-4.1$ klist
  Ticket cache: FILE:/tmp/krb5cc_126431806
  Default principal: krbuserb@LABS.Strategy.COM
  Valid starting Expires Service principal
  03/07/16 07:57:37 03/07/16 17:57:47 krbtgt/LABS.Strategy.COM@LABS.Strategy.COM
  renew until 03/14/16 08:57:37
  -sh-4.1$

  
  

• Edit the Strategy-provided odbc.ini file to add the DSN for Impala.  Use the example DSN found in the odbc.ini.example file that is also shipped with Strategy.  The Kerberos-related modifications are highlighted below.
  

  
  [ODBC Data Sources]
  Impala Driver=Strategy Impala ODBC Driver
  
  [Impala Driver]
  Driver=/opt/MicroStrategy/Strategy-Linux/install/lib/libimpalaodbc_sb64.so
  Description=Strategy Impala ODBC Driver
  Host=ts-cdh55
  Port=21050
  Schema=default
  AuthMech=1
  UseSASL=0
  KrbFQDN=quickstart.cloudera
  KrbRealm=LABS.Strategy.COM
  KrbServiceName=impala
  SSL=0
  CAIssuedCertNamesMismatch=1
  TrustedCerts=/opt/MicroStrategy/Strategy-Linux/install/lib/cacerts.pem
  TSaslTransportBufSize=1000
  RowsFetchedPerBlock=10000
  SocketTimeout=0
  StringColumnLength=32767
  UseNativeQuery=0

  
  
• Add an entry to the /etc/hosts file with the IP address, hostname, and FQDN of the Impala machine to connect to.  For example:
   
  

  
  192.168.0.109 quickstart.cloudera quickstart.cloudera.labs.Strategy.com ts-cdh55

  
   
   
  

Verify DSN using the Strategy DB Query tool

• Follow the steps in the previous section "Creating a DSN to Impala on Secured Cloudera CDH cluster on Linux" to verify the Kerberos user can obtain a ticket and create the DSN.
• As the Kerberos user, verify the Impala DSN can be used to connect to CDH/Impala using the Strategy DB Query tool.  When connecting to the DSN do not provide a uid/pwd.

-sh-4.1$ whoami
TECH_SERVICES\krbuserb
-sh-4.1$ cd /opt/MicroStrategy/Strategy-Linux/bin
-sh-4.1$ ./mstrdbquerytool

Configure the Strategy Intelligence Server process to run as the Kerberos user

To make Kerberos secured warehouse connections to CDH/Impala the Strategy Intelligence Server process must run under the Kerberos user's account

• As the Kerberos user, verify that a Kerberos ticket can be obtained successfully.  See "Creating a DSN to Impala on Secured Cloudera CDH cluster on Linux" for more details.
• As the Kerberos user, launch the Strategy Configuration Wizard.

-sh-4.1$ whoami
TECH_SERVICES\krbuserb
-sh-4.1$ cd /opt/MicroStrategy/Strategy-Linux/bin
-sh-4.1$ ./mstrcfgwiz

• Configure the Intelligence Server as detailed in the Strategy documentation.  
• When the process completes verify that the Strategy Intelligence Server process is running as the Kerberos user.

# ps -ef | grep MSTRSvr
root 6890 6873 0 08:47 pts/4 00:00:00 grep MSTRSvr
krbuserb 21173 19473 0 Mar04 ? 00:02:42 /opt/MicroStrategy/Strategy-Linux/install/IntelligenceServer/bin/MSTRSvr -w /opt/MicroStrategy/Strategy-Linux//IntelligenceServer -t status-iserver.xml /opt/MicroStrategy/Strategy-Linux/install/lib/libMSTRSvr2.so -n CastorServer

Creating a secured DSNless connection with the Strategy Impala ODBC driver in Strategy Web

• While logged into an OS as the Kerberos user, open a connection to Strategy Web
• Connect to the Strategy project hosted by the Kerberos-enabled Intelligence Server process
• Open the MSTR Web Interface and go into the desired Project→Add External Data→ Hadoop→ Impala
• Create a DSN-less connection using a modified connection string to enable Kerberos.  The Kerberos-related changes are highlighted below in the sample connection string and the screenshot

Sample Connection String: 
 

DRIVER={Strategy Impala ODBC Driver}; Host=192.168.0.109; Port=21050;SCHEMA=wh2parquet;AuthMech=1;KrbFQDN=quickstart.cloudera;KrbRealm=LABS.Strategy.COM;KrbServiceName=impala;

• Note that when creating the DSN-less connection in Web the user must provide a user name and password.  This information will not be used in the actual connection to the warehouse.  

KB289333