Kerberos Authentication for Database Connection
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Kerberos Authentication for Database Connection
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.
Solved! Go to Solution.
- Labels:
-
Connectivity
-
Examples
-
Security
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
From "Help Center" - "Kerberos Authentication Configuration": https://support.ptc.com/help/thingworx_hc/thingworx_analytics_8/index.html#page/analytics/hdfs_authenticated_config.html
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.