JDBC Driver

["Ravi Venugopal (C)" <Ra...@cheniere.com>]

Hi

I am trying to POC drill for a customer and I am working on connecting the JDBC driver to RDS on Aws for Oracle.

Here is the Certificate of security on the TNS names, I do not see a syntx / kvp for the json to add this cert path (Cert info hidden)


(SECURITY = (SSL_SERVER_CERT_DN = "C=US,ST=Somewhere,L=Cityname,O=Amazon.com,OU=RDS,CN=xxxxxxxx.yyyyyyyyy.us-ABCD-1.rds.amazonaws.com")))


PS: ODBC is not having the option to enable this as well.

Can someone help please.

This e-mail and any attachments are for the sole use of the intended recipient(s) and may contain information that is legally privileged and/or confidential information. If you are not the intended recipient(s) and have received this e-mail in error, please immediately notify the sender by return e-mail and delete this e-mail from your computer. Any distribution, disclosure or the taking of any other action by anyone other than the named recipient is strictly prohibited.

Re: JDBC Driver

[Arjun kr <ar...@outlook.com>]

Hi Ravi,


Looking at the AWS documentation, it seems it can be specified in connection URL. I would suggest to try it using a standalone java application before trying it with Drill in case you have not tried it. As per doc, below are the steps involved. You may try it as given below (I have not tried it though).


1) Add Amazon RDS root CA certificate to truststore -  You can add this truststore used by Drill process. Add to cacerts if custom truststore is not being used.


2) Set connection string as given below - Try setting connection URL in storage plugin as given below. Not sure if there could be any parsing error. You may pass user and password in the connection URL if separate properties for username and password in plugin definition does not work.


final String connectionString = String.format( "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=%s)(PORT=%d))" +

"(CONNECT_DATA=(SID=%s))" +

"(SECURITY = (SSL_SERVER_CERT_DN = \"C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=%s\")))", DB_SERVER_NAME, SSL_PORT, DB_SID, DB_SERVER_NAME);


https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.SSL.html#Appendix.Oracle.Options.SSL.JDBC


Thanks,


Arjun


________________________________
From: Sorabh Hamirwasia <sh...@mapr.com>
Sent: Tuesday, April 3, 2018 12:21 AM
To: user@drill.apache.org
Subject: Re: JDBC Driver

Hi Ravi,

I just want to confirm that you are using Drill's JDBC storage plugin to connect to RDS on Aws for Oracle ? You can look into [1] to see the usage.

Currently JDBC plugin only supports four parameters: driver/url/username/password, there doesn't seem to be support for any separate parameters for SSL configuration. But having said that, probably you can check the JDBC driver which you are using if it allows to pass these SSL parameters as key-value pair within URL or not.


Also not sure what you mean by ODBC driver not having the option to enable this, since Drill doesn't support any ODBC storage plugin.


[1] : https://drill.apache.org/docs/rdbms-storage-plugin/
RDBMS Storage Plugin - Apache Drill<https://drill.apache.org/docs/rdbms-storage-plugin/>
drill.apache.org
Apache Drill supports querying a number of RDBMS instances. This allows you to connect your traditional databases to your Drill cluster so you can have a single view of both your relational and NoSQL datasources in a single system. As with any source, Drill supports joins within and between all ...





P.S. Please post these questions on users group only not on dev group, since there you will have wider audience which can answer it. For now I have removed dev group from To list.


Thanks,
Sorabh


________________________________
From: Ravi Venugopal (C) <Ra...@cheniere.com>
Sent: Friday, March 30, 2018 8:01 AM
To: dev@drill.apache.org; user@drill.apache.org
Subject: JDBC Driver

Hi

I am trying to POC drill for a customer and I am working on connecting the JDBC driver to RDS on Aws for Oracle.

Here is the Certificate of security on the TNS names, I do not see a syntx / kvp for the json to add this cert path (Cert info hidden)


(SECURITY = (SSL_SERVER_CERT_DN = "C=US,ST=Somewhere,L=Cityname,O=Amazon.com,OU=RDS,CN=xxxxxxxx.yyyyyyyyy.us-ABCD-1.rds.amazonaws.com")))


PS: ODBC is not having the option to enable this as well.

Can someone help please.

This e-mail and any attachments are for the sole use of the intended recipient(s) and may contain information that is legally privileged and/or confidential information. If you are not the intended recipient(s) and have received this e-mail in error, please immediately notify the sender by return e-mail and delete this e-mail from your computer. Any distribution, disclosure or the taking of any other action by anyone other than the named recipient is strictly prohibited.

Re: JDBC Driver

[Sorabh Hamirwasia <sh...@mapr.com>]

Hi Ravi,

I just want to confirm that you are using Drill's JDBC storage plugin to connect to RDS on Aws for Oracle ? You can look into [1] to see the usage.

Currently JDBC plugin only supports four parameters: driver/url/username/password, there doesn't seem to be support for any separate parameters for SSL configuration. But having said that, probably you can check the JDBC driver which you are using if it allows to pass these SSL parameters as key-value pair within URL or not.


Also not sure what you mean by ODBC driver not having the option to enable this, since Drill doesn't support any ODBC storage plugin.


[1] : https://drill.apache.org/docs/rdbms-storage-plugin/


P.S. Please post these questions on users group only not on dev group, since there you will have wider audience which can answer it. For now I have removed dev group from To list.


Thanks,
Sorabh


________________________________
From: Ravi Venugopal (C) <Ra...@cheniere.com>
Sent: Friday, March 30, 2018 8:01 AM
To: dev@drill.apache.org; user@drill.apache.org
Subject: JDBC Driver

Hi

I am trying to POC drill for a customer and I am working on connecting the JDBC driver to RDS on Aws for Oracle.

Here is the Certificate of security on the TNS names, I do not see a syntx / kvp for the json to add this cert path (Cert info hidden)


(SECURITY = (SSL_SERVER_CERT_DN = "C=US,ST=Somewhere,L=Cityname,O=Amazon.com,OU=RDS,CN=xxxxxxxx.yyyyyyyyy.us-ABCD-1.rds.amazonaws.com")))


PS: ODBC is not having the option to enable this as well.

Can someone help please.

This e-mail and any attachments are for the sole use of the intended recipient(s) and may contain information that is legally privileged and/or confidential information. If you are not the intended recipient(s) and have received this e-mail in error, please immediately notify the sender by return e-mail and delete this e-mail from your computer. Any distribution, disclosure or the taking of any other action by anyone other than the named recipient is strictly prohibited.