Connect with Hive2 (0.12) which is kerberos authenticated.

[debashishsaha004]

http://kb.tableau.com/articles/knowledgebase/connecting-to-hive-server-2-in-secure-mode

The above link is exactly what my requirement  as well as my environment setup.I want to achieve it from Jboss DV.

Using Kerberos for Datasource Authentication

Here I have found one more thread that is to a great extent like my problem statement.But he did not mention about the MIT client which is needed to get the ticket.

1.I have MIT client to get a ticket for a particular principal.the system  which has MIT client is within the kerberos domain.

2.I have successfully loaded hive driver.

3.Created one security domain as follows:

<security-domain name="abc" cache-type="default">

                    <authentication>

                        <login-module code="Kerberos" flag="required">

                            <module-option name="storeKey" value="true"/>

                            <module-option name="useKeyTab" value="true"/>

                            <module-option name="principal" value="host/[e-mail]"/>

                            <module-option name="keyTab" value="...path to /a3000053.keytab"/>

                            <module-option name="doNotPrompt" value="true"/>

                            <module-option name="debug" value="false"/>

                        </login-module>

                    </authentication>

                </security-domain>

4.From new Import teiid connection>>source model I have used hive driver,url and instead of username-password I have used Security Domain name(here  its  "abc").

5.As you can see, in the last step I am getting error.

6.I have tried with min pool =1 but same error was there also.

attached: server log.

Please let me know if I am going to the right direction as per the problem statement.

and any help to resolve the access denied issue is appreciated.

Thank you in advance.

[debashishsaha004]

rareddy shawkins can you guys please give a solution on this .its badly needed.

[shawkins]

Also can you turn up the logging for the org.jboss.security.negotiation context to trace and set the module option debug to true - that should give a clear picture of what is happening.

[shawkins]

Also can you show your datasource configuration?

[debashishsaha004]

shawkins Thank you  for the reply .

<security-domain name="host" cache-type="default">

                    <authentication>

<login-module code="Kerberos" flag="required">

                            <module-option name="storeKey" value="true"/>

                            <module-option name="useKeyTab" value="true"/>

                            <module-option name="principal" value="host/[e-mail]"/>

                            <module-option name="keyTab" value="...path to /a3000053.keytab"/>

                            <module-option name="doNotPrompt" value="true"/>

                              <module-option name="debug" value="true"/>

</login-module>

                    </authentication>

                </security-domain>

                <security-domain name="teiid-security" cache-type="default">

                    <authentication>

                        <login-module code="RealmDirect" flag="sufficient">

                            <module-option name="password-stacking" value="useFirstPass"/>

                        </login-module>

                    </authentication>

                </security-domain>

                <security-domain name="MYCOMPANY.COM" cache-type="default">

                    <authentication>

                        <login-module code="SPNEGO" flag="requisite">

                            <module-option name="password-stacking" value="useFirstPass"/>

                            <module-option name="serverSecurityDomain" value="host"/>

                              <module-option name="debug" value="true"/>

                        </login-module>

                        <login-module code="UserRoles" flag="requisite">

                            <module-option name="password-stacking" value="useFirstPass"/>

                            <module-option name="rolesProperties" value="spnego-roles.properties"/>

                            <module-option name="usersProperties" value="spnego-users.properties"/>

                        </login-module>

                    </authentication>

                </security-domain>

here is the security Domain config in my standalone xml and I have changed the debug =true as you said.the changes are Bold ,ittalic and underlined.

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

  </datasource>

                <datasource jndi-name="java:/HiveDB" pool-name="HiveDB" enabled="true">

                    <connection-url>jdbc:hive2://<myIP>:10010/default;principal=hive/[e-mail]?hive.fetch.task.conversion=none;hive.execution.engine=tez</connection-url>

                    <driver>hivedriver</driver>

                    <security>

                        <security-domain>JCI.COM</security-domain>

                    </security>

                </datasource>

                <drivers>

                    <driver name="hivedriver" module="org.apache.hadoop.hive12">

                        <driver-class>org.apache.hive.jdbc.HiveDriver</driver-class>

                    </driver>

                    <driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                    </driver>

                    <driver name="modeshape" module="org.modeshape.jdbc">

                        <driver-class>org.modeshape.jdbc.LocalJcrDriver</driver-class>

                    </driver>

                    <driver name="teiid" module="org.jboss.teiid.client">

                        <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

                        <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

                    </driver>

                    <driver name="teiid-local" module="org.jboss.teiid">

                        <driver-class>org.teiid.jdbc.TeiidDriver</driver-class>

                        <xa-datasource-class>org.teiid.jdbc.TeiidDataSource</xa-datasource-class>

                    </driver>

                </drivers>

            </datasources>

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

Here is the Datasources configuration in standalone.xml.

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

12:26:16,014 INFO  [org.jboss.as] (Controller Boot Thread) JBAS015961: Http management interface listening on http://127.0.0.1:9990/management

12:26:16,014 INFO  [org.jboss.as] (Controller Boot Thread) JBAS015951: Admin console listening on http://127.0.0.1:9990

12:26:16,014 INFO  [org.jboss.as] (Controller Boot Thread) JBAS015874: JBoss Red Hat JBoss Data Virtualization 6.3.0 (AS 7.5.9.Final-redhat-2) started in 19576ms - Started 931 of 973 services (104 services are lazy, passive or on-demand)

..............

12:37:13,149 INFO  [org.jboss.as.repository] (management-handler-thread - 11) JBAS014900: Content added at location C:\Users\csahad\EAP-6.4.0\standalone\data\content\d4\8c897073522d5de46d34d8a92c362aef80473d\content

12:37:13,154 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-8) JBAS015876: Starting deployment of "importVDB-vdb.xml" (runtime-name: "importVDB-vdb.xml")

12:37:13,208 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-8) TEIID40118 VDB importVDB.1 added to the repository

12:37:13,208 INFO  [org.teiid.RUNTIME] (MSC service thread 1-8) TEIID50029 VDB importVDB.1 model "importVDBSrcModel" metadata is currently being loaded. Start Time: 11/11/16 12:37 PM

12:37:13,212 WARN  [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID50036 VDB importVDB.1 model "importVDBSrcModel" metadata failed to load. Reason:TEIID31178 Could not obtain connection for schema importVDBSrcModel, but one is required for metadata load. : org.teiid.translator.TranslatorException: TEIID31178 Could not obtain connection for schema importVDBSrcModel, but one is required for metadata load.

  at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:76) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  at org.teiid.jboss.VDBService$6.run(VDBService.java:395) [teiid-jboss-integration-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  at org.teiid.jboss.VDBService$7.run(VDBService.java:446) [teiid-jboss-integration-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_101]

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_101]

  at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_101]

  at org.jboss.threads.JBossThread.run(JBossThread.java:122)

Caused by: java.lang.SecurityException: PBOX000016: Access denied: authentication failed

  at org.jboss.security.plugins.JBossSecuritySubjectFactory.createSubject(JBossSecuritySubjectFactory.java:84) [picketbox-4.1.2.Final-redhat-1.jar:4.1.2.Final-redhat-1]

  at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getSubject(AbstractConnectionManager.java:721) [ironjacamar-core-impl-1.0.37.Final-redhat-1.jar:1.0.37.Final-redhat-1]

  at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:498) [ironjacamar-core-impl-1.0.37.Final-redhat-1.jar:1.0.37.Final-redhat-1]

  at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:143)

  at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:69) [jboss-as-connector-7.5.9.Final-redhat-2.jar:7.5.9.Final-redhat-2]

  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:270)

  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:68)

  at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:202) [teiid-api-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:72) [teiid-engine-8.12.5.redhat-8.jar:8.12.5.redhat-8]

  ... 8 more

12:37:13,327 INFO  [org.jboss.as.server] (management-handler-thread - 11) JBAS015859: Deployed "importVDB-vdb.xml" (runtime-name : "importVDB-vdb.xml")

12:37:16,146 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-1) TEIID40120 VDB importVDB.1 will be removed from the repository

12:37:16,149 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-1) TEIID40119 VDB importVDB.1 removed from the repository

12:37:16,159 INFO  [org.teiid.RUNTIME] (MSC service thread 1-1) TEIID50026 VDB "importVDB.1[importVDBSrcModel{importVDBSrcModel=importVDBSrcModel, hive, java:/HiveDB}]" undeployed.

12:37:16,162 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-1) TEIID40120 VDB importVDB.1 will be removed from the repository

12:37:16,167 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-8) JBAS015877: Stopped deployment importVDB-vdb.xml (runtime-name: importVDB-vdb.xml) in 22ms

12:37:16,214 INFO  [org.jboss.as.repository] (management-handler-thread - 9) JBAS014901: Content removed from location C:\Users\csahad\EAP-6.4.0\standalone\data\content\d4\8c897073522d5de46d34d8a92c362aef80473d\content

12:37:16,214 INFO  [org.jboss.as.server] (management-handler-thread - 9) JBAS015858: Undeployed "importVDB-vdb.xml" (runtime-name: "importVDB-vdb.xml")

from Debashish Saha

[debashishsaha004]

shawkins May I know what is your thoughts to solve this problem statement.How you are thinking to solve this ?

Till now what I understood is the ticket from MIT client will be  used by SPNEGO while any kerberos call is needed.

But I am not sure about the logic of adding  Userrole module.I have added it because its there in the documents.  

What I think about the solution is that --

1. The ticket what we already have further is required to provide before TGS while asking for a  service i.e TGS ticket granting session which is specifically needed for accessing a particular service.
2. SO for obtaining a session we need to provide CN=??? OU =??? DN=??? which is there in LDAP,moreover there has to be check on whether the principal is authorized to use the particular service or not which is there in LDAP again. if i am not wrong.
3. SO according to me there should be module that mentions about LDAP.

Please let me know if at any point i am wrong

Thank you

Debashish Saha

[shawkins]

Given that you are first authenticating into the security domain, the first thing that is needed is to change the connection url to hive - HiveServer2 Clients - Apache Hive - Apache Software Foundation to include auth=kerberos and kerberosAuthType=fromSubject so that it looks on the Subject for the credential.

[debashishsaha004]

Thank you very much for providing this URL.

So now what changes do you suggest to make in the standalone.xml and in the connection  Url ?

as you can see the security module and data sources module in my reply.

[debashishsaha004]

auth=kerberos;kerberosAuthType=fromSubject

I have concatenated thr above line with the previous connection URL.

but still getting same error

[shawkins]

In the configuration above you have references to host, JCI.COM, and MYCOMPANY.COM - which one is actually securing the data source?

[debashishsaha004]

I forgot to change JCI.COM

anyway instead of JCI.COM read it as MYCOMPANY.COM ...

thats only securing my data sources shawkins

[rareddy]

Do you have instructions show you setup the Hive server to work with Kerberos? I would like setup locally and test this out.

[debashishsaha004]

I have used another data Virtualization tool called donodo.

from that I can easily connect.

will that work if I send you the screenshot of it ?

[rareddy]

Sure, we can double check the configuration. Also are you using HortonWorks or Cloudera ? I would like to setup one of their sandboxes with kerberos and validate it from connecting from Teiid.

[debashishsaha004]

Hortonworks I am using.

thank you in advance