Kerberos Authentication for Database Connection

[Ascherer17]

Has anyone else been able to connect Thingworx to a database using Kerberos authentication and provide any tips or high level steps on what files must be created and where?

I'm trying to connect to a Hive database using a Persistence Provider with the "GenericJdbcPersistenceProviderPackage".  I've also tried connecting using a "Database" Thing with no luck.

[VladimirN]

From "Help Center" - "Kerberos Authentication Configuration": https://support.ptc.com/help/thingworx_hc/thingworx_analytics_8/index.html#page/analytics/hdfs_authenticated_config.html 

[Ascherer17 · Accepted solution]

After learning more about how Kerberos works and the Hive JDBC driver (since I'm connecting to Hive DB), I was able to connect to our Hive instance. I'm using ThingWorx 9.4.1 on RHEL, for reference.

Here are the high level steps it took to get there:

• Placed the relevant Hive JDBC driver jar file in Tomcat lib/ folder.  Restarted Thingworx to load the jar.
• Created a principal for our ThingWorx server in Kerberos.
• Created a truststore with SSL certificates for Kerberos instance and placed on ThingWorx server.
• Created a keytab file on ThingWorx server for the principal representing ThingWorx server.
• Ran kinit against the keytab file manually to authenticate server with Kerberos.
• Created a "Persistence Provider" entity in Composer using "GenericJdbcPersistenceProviderPackage".
  • JDBC URL (Includes driver logging for debugging): jdbc:hive2://<Hive Hostname>:<Hive Port>/<Database Name>;SSL=1;SSLTrustStore=</Path/To/Trust/Store/File.jks>;SSLTrustStorePwd=<Password for the Trust Store>;httpPath=cliservice;AuthMech=1;transportMode=http;krbRealm=<KERBEROS.REALM.IN.CAPS>;KrbHostFQDN=<KerverosHostFQDN>;KrbAuthType=2;KrbServiceName=<Kerberos service principal name>;LogLevel=6;LogPath=</path/for/logs>;
  • Driver Class: com.cloudera.hive.jdbc.HS2DataSource
  • SSL Connection Mode: Require
• Created a SQLThing with persistence provider value set to the above created persistence provider.
  • Create a SQL based Service to connect and run a query;  "show tables;" is one of the simplest to confirm access.