SSL Handshake Error when Connecting SQL Server

[yong...@xbrain.team]

I am trying to connect to SQL Server 2017 from dataproc, but  I have trouble with passing SSL handshake. I created SQL Server 2017 using VM instance, and in dataproc, I am using jdbc6.2.2 and SQL Server Driver 13.

Connection works fine with sqlalchemy & pyodbc, but when using spark and jdbc driver, socket closes when trying ssl handshake. 

I tried 1) changing options for connection uri(trustCertificate, encrypt) 2) specifying truststore location(/etc/ssl/certs/java/cacerts -- i am not sure whether this is the right certificates that I need) 

Here is the error that I get. Any help on solving the issue? 

py4j.protocol.Py4JJavaError: An error occurred while calling o94.load. : com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. 

Error: "Socket is closed". ClientConnectionId:43d4f0fe-5c31-4c69-b434-1ffd1b16a4a4 

at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2435) 

at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1816) 

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2022) 

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687) 

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528) 

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866) 

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569) 

at org.apache.spark.sql.execution.datasources.jdbc.DriverWrapper.connect(DriverWrapper.scala:45) 

at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:61) 

at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:52) 

at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:58) 

at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:113)

at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:47) 

at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:306) 

at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178) 

at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146) 

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498) 

at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244) 

at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357) 

at py4j.Gateway.invoke(Gateway.java:280) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132) 

at py4j.commands.CallCommand.execute(CallCommand.java:79) 

at py4j.GatewayConnection.run(GatewayConnection.java:214) 

at java.lang.Thread.run(Thread.java:748) 

Caused by: java.net.SocketException: Socket is closed at org.conscrypt.SslWrapper.doHandshake(SslWrapper.java:368) 

at org.conscrypt.ConscryptFileDescriptorSocket.startHandshake(ConscryptFileDescriptorSocket.java:211) 

at org.conscrypt.Java8SocketWrapper.startHandshake(Java8SocketWrapper.java:324) 

at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1753) ... 25 more

[Ramakrishna Prasad]

Hi We are observing the above issue while using Sqoop with sqljdbc and it is blocking our development.

Does someone know what is the resolution for this? Or any workaround? Need the help asap. Thanks.

[Ramakrishna Prasad]

2018-08-23 10:34:54,348 WARN [main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.lang.RuntimeException: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Socket is closed". ClientConnectionId:dfb0a22f-2863-4ae7-8fa2-add4d46ccec5
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:167)
	at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:77)
	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:137)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:757)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:177)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1886)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:171)
Caused by: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Socket is closed". ClientConnectionId:dfb0a22f-2863-4ae7-8fa2-add4d46ccec5
	at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:220)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:165)
	... 9 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Socket is closed". ClientConnectionId:dfb0a22f-2863-4ae7-8fa2-add4d46ccec5
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2435)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1816)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2022)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at org.apache.sqoop.mapreduce.db.DBConfiguration.getConnection(DBConfiguration.java:302)
	at org.apache.sqoop.mapreduce.db.DBInputFormat.getConnection(DBInputFormat.java:213)
	... 10 more
Caused by: java.net.SocketException: Socket is closed
	at org.conscrypt.NativeSsl.doHandshake(NativeSsl.java:390)
	at org.conscrypt.ConscryptFileDescriptorSocket.startHandshake(ConscryptFileDescriptorSocket.java:225)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1753)
	... 19 more

[Guy Cobb]

If you have a support contract then please open a support ticket with details about the clusters and jobs affected. That would be the fastest way to get someone looking at the problem closely. Please feel free to provide the support ticket number here so we have more visibility into your project and can better assist you.

If you want to contact us with non-public details that you think might be relevant (steps to reproduce the issue, cluster and job ids, logs, etc.) then you can reach us at dataproc...@google.com

[Ravi Choudhary]

I was getting same error with dataproc cluster, able to solve problem by adding below properties while creating dataproc cluster.

gcloud dataproc clusters create <cluster-name> --properties dataproc:dataproc.conscrypt.provider.enable=false

Refer : https://kb.informatica.com/solution/23/pages/67/549737.aspx