I am getting the following error when connecting to a SQL Server database using version the Microsoft JDBC Driver:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server returned an incomplete response. The connection has been closed. ClientConnectionId:98d0b6f4-f3ca-4683-939e-7c0a0fca5931".
We recently upgraded our applications from Java 6 & Java 7 to Java 8. All systems running Java are running SUSE Linux Enterprise Server 11 (x86_64), VERSION = 11, PATCHLEVEL = 3.
Here are the facts I have collected with a Java program that I wrote which simply sequentially opens and closes 1,000 database connections.
The thing that makes my observations unique on this compared to others on the web is that although the problem happens ONLY on Java 8, I cannot get the problem to occur on one of the seemingly identical Linux servers that is running the same Java 8 JVM. Other folks have seen this problem on earlier versions of Java as well, but that has not been our experience.
Any input, suggestions, or observations you may have are appreciated.
Encryption of JDBC connection is managed by parameters passed to the third party JDBC client jars that are supplied by the JDBC provider. You can use the IBM® Integration Bus JDBCProviders configurable service or a vendor-specific configuration file to pass the parameters.
Check if the connection is encrypted You can query the sys. dm_exec_connections dynamic management view (DMV) to see if the connections to your SQL Server is encrypted or not. If the value of encrypt_option is "TRUE" then your connection is encrypted.
In order to fix this error just remove microsoft from URL. Correct JDBC URL format to connect SQL SERVER is "jdbc:sqlserver://localhost:1433";. No need to worry about CLASSPATH, because if the SQLJDBC4. jar is not present then it will give you a different error, something like java.
I turned on SSL logging in the Java 8 JVM on a Linux instance which reproduces the problem. SSL logging is turned on using -Djavax.net.debug=ssl:handshake:verbose
. This revealed some useful information.
The workaround that we are using in production and has proven to work for us is to set this parameter on the JVM:
-Djdk.tls.client.protocols=TLSv1
If you want more details, please read on.
On a server where the problem can be reproduced (again, only 5-10% of the time), I observed the following:
*** ClientHello, TLSv1.2 --- 8<-- SNIP ----- main, WRITE: TLSv1.2 Handshake, length = 195 main, READ: TLSv1.2 Handshake, length = 1130 *** ServerHello, TLSv1.2 --- 8<-- SNIP ----- %% Initialized: [Session-79, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256] ** TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 --- 8<-- SNIP ----- Algorithm: [SHA1withRSA] --- 8<-- SNIP ----- *** Diffie-Hellman ServerKeyExchange --- 8<-- SNIP ----- *** ServerHelloDone *** ClientKeyExchange, DH --- 8<-- SNIP ----- main, WRITE: TLSv1.2 Handshake, length = 133 --- 8<-- SNIP ----- main, WRITE: TLSv1.2 Change Cipher Spec, length = 1 *** Finished verify_data: { 108, 116, 29, 115, 13, 26, 154, 198, 17, 125, 114, 166 } *** main, WRITE: TLSv1.2 Handshake, length = 40 main, called close() main, called closeInternal(true) main, SEND TLSv1.2 ALERT: warning, description = close_notify main, WRITE: TLSv1.2 Alert, length = 26 main, called closeSocket(true) main, waiting for close_notify or alert: state 5 main, received EOFException: ignored main, called closeInternal(false) main, close invoked again; state = 5 main, handling exception: java.io.IOException: SQL Server returned an incomplete response. The connection has been closed. ClientConnectionId:12a722b3-d61d-4ce4-8319-af049a0a4415
Notice that TLSv1.2 is selected by the database server and used in this exchange. I've observed that, when connections fail from the problematic linux service, TLSv1.2 is ALWAYS the level which was selected. However, connections do not ALWAYS fail when TLSv1.2 is used. They only fail 5-10% of the time.
Now here is an exchange from a server that does NOT have the problem. Everything else is equal. I.e., connecting to the same database, same version of the JVM (Java 1.8.0_60), same JDBC driver, etc. Notice that, here, TLSv1 is selected by the database server instead of TLSv1.2 as in the faulty server's case.
*** ClientHello, TLSv1.2 --- 8<-- SNIP ----- main, WRITE: TLSv1.2 Handshake, length = 207 main, READ: TLSv1 Handshake, length = 604 *** ServerHello, TLSv1 --- 8<-- SNIP ----- Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA --- 8<-- SNIP ----- %% Initialized: [Session-79, TLS_RSA_WITH_AES_128_CBC_SHA] ** TLS_RSA_WITH_AES_128_CBC_SHA --- 8<-- SNIP ----- Algorithm: [SHA1withRSA] --- 8<-- SNIP ----- *** *** ServerHelloDone *** ClientKeyExchange, RSA PreMasterSecret, TLSv1 --- 8<-- SNIP ----- main, WRITE: TLSv1 Handshake, length = 134 main, WRITE: TLSv1 Change Cipher Spec, length = 1 *** Finished verify_data: { 26, 155, 166, 89, 229, 193, 126, 39, 103, 206, 126, 21 } *** main, WRITE: TLSv1 Handshake, length = 48 main, READ: TLSv1 Change Cipher Spec, length = 1 main, READ: TLSv1 Handshake, length = 48 *** Finished
So, when TLSv1 is negotiated between the Linux JVM and the SQL Server, connections are ALWAYS successful. When TLSv1.2 is negotiated, we get sporadic connection failures.
(Note: Java 7 (1.7.0_51) always negotiates TLSv1, which is why the problem never occurred for us with a Java 7 JVM.)
The open questions we still have are:
Update 6/10/2017: This posting from Microsoft describes the problem and their proposed solution.
Resources:
http://www.infoworld.com/article/2849292/operating-systems/more-patch-problems-reported-with-the-ms14-066-kb-2992611-winshock-mess.html
http://www.infoworld.com/article/2849292/operating-systems/more-patch-problems-reported-with-the-ms14-066-kb-2992611-winshock-mess.html
http://blogs.msdn.com/b/jdbcteam/archive/2008/09/09/the-driver-could-not-establish-a-secure-connection-to-sql-server-by-using-secure-sockets-layer-ssl-encryption.aspx
Java 8 , JCE Unlimited Strength Policy and SSL Handshake over TLS
http://blogs.msdn.com/b/saponsqlserver/archive/2013/05/10/analyzing-jdbc-connection-issues.aspx
https://docs.oracle.com/javase/8/docs/technotes/guides/security/jsse/JSSERefGuide.html#descPhase2
https://blogs.oracle.com/java-platform-group/entry/java_8_will_use_tls
Your url should be like below and add sql sqljdbc42.jar. This will resolve your issue
url = "jdbc:sqlserver://" +serverName + ":1433;DatabaseName=" + dbName + ";encrypt=true;trustServerCertificate=true;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With