How to connect to Sybase ASE using JDBC driver and SSL connection

Question

I'm trying to establish an SSL connection to a Sybase ASE 15.7 using JDBC driver with no luck. I tried the following options:

1. Using JTDS 1.25 driver (jtds-1.2.5.jar)

   With the following connection string: jdbc:jtds:sybase://host:port;databaseName=dbname;ssl=request

   I got Network error IOException: Connection refused

2. Using Jconnect 4 (jconn4.jar)

   with the following connection string:

   jdbc:sybase:Tds:host:port/dbname?ENABLE_SSL=true

   I got java.sql.SQLException: JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s) ... java.sql.SQLException: I/O Error: DB server closed connection.

   I checked the Sybase log see the following error:

   kernel SSL or Crypto Error Message: 'The SSL handshake failed. Root error: error:140760FC:SSL routines:SSL23_GET_CLIENT_HELLO:unknown protocol'.

   it looks like the Sybase server expects SSL connection but the java client still not using SSL (although connection string property marks ssl=true).

tried searching Sybase documentation with not much luck, neither for this error nor for SSL with JConnect.

Any answer will be much appreciated - i'm flexible with the type of driver and the configuration.

Thanks

Answer 1

After much investigation, I found a solution. 2 actually.

1. Using trust all certificate JDBC connection string parameter: if you don't mind to trust all certificates (do this only if you entirely trust the network you're working in, especially not anything going on the public internet), you may add a connection string indicating the SSLSocketFactory creating the connection to trust all certificates. The connection string will look as follows: jdbc:sybase:Tds:host:port/dbname?ENABLE_SSL=true&SSL_TRUST_ALL_CERTS=true
2. Using the sybase certificate: the certificate needs to be imported to the java application trust store. in case you're not working with a designated trust store, it may be imported to the Java default trust store found under $JAVA_HOME\jreX\lib\security\cacerts. The certificate may be imported using keytool as explained here.

Answer 2

Although zuckermanori's answer provides some key details, adding more steps below which are required -

1. Provide right jdbc jar which supports ssl while creating spark session. I was earlier using jconn3-6.0.0.jar which doesnt support ssl. Later, I used jconnect-16.0_SP02.jar which worked fine. Example pyspark command to pass the driver jar would be -

pyspark --jars /path/to/your/jdbc/driver/jar

2. Provide additional java args as follows in the same command to provide trust store location(which has the certificates) -

--conf spark.driver.extraJavaOptions="-Djavax.net.ssl.trustStore=/path/to/truststore -Djavax.net.ssl.trustStorePassword=your_truststore_password"​ ​ 3. Use right string to load driver in option. Earlier I was using 'com.sybase.jdbc4.jdbc.SybDriver' which didnt work. Then below worked for me - .option("driver", "com.sybase.jdbc4.jdbc.SybDriver") ​ 4. Use right connection string to provide additional ssl options as per the driver. E.g. - .option("url", "jdbc:sybase:Tds:host_name:ssl_port/database_name?ENABLE_SSL=true&SSL_TRUST_ALL_CERTS=true&ssl=request")

5. Additional options required -

.option("ssl", True).option("sslmode", "require")

To sum it up, this is how your pyspark command should look like(if you are using yarn mode, then truststore should be accessible on all nodes. Below is the example of spark local mode) -

pyspark --jars /path/to/your/jdbc/driver/jar --conf spark.driver.extraJavaOptions="-Djavax.net.ssl.trustStore=/path/to/truststore -Djavax.net.ssl.trustStorePassword=your_truststore_password"​ --master local

And this is how your jdbc read look like -

spark.read.format("jdbc").option("url", "jdbc:sybase:Tds:host_name:ssl_port/database_name?ENABLE_SSL=true&SSL_TRUST_ALL_CERTS=true&ssl=request").option("driver", "com.sybase.jdbc4.jdbc.SybDriver").option("ssl", True).option("sslmode", "require").option("user", "your_user_name").option("password", "your_password").option("dbtable", "db.dbo.table_name").load().show(5)