Skip to main content
A
Ascherer1716-Pearl
16-Pearl
March 15, 2024
Solved

Kerberos Authentication for Database Connection

  • March 15, 2024
  • 2 replies
  • 1737 views

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.

Best answer by Ascherer17

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.

2 replies

V
24-Ruby III
VladimirN24-Ruby III
24-Ruby III
March 15, 2024

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

A
Ascherer1716-PearlAuthorAnswer
16-Pearl
April 26, 2024

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.
    Terms & ConditionsCookie settingsAccessibility statement