Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Ask a Question
Welcome To Ask or Share your Answers For Others

Categories

java - How to connect to Azure SQL with JDBC

0 votes
157 views
in Technique[技术] by (71.8m points)

java - How to connect to Azure SQL with JDBC

I try to connect to Azure SQL with MS JDBC driver:

import java.sql.*;

public class ExampleSQLJDBC {

    public static void main(String[] args) {

        // Setting.
        String connectionUrl = "jdbc:sqlserver://SERVER.database.windows.net:1433;database=DATABASE;encrypt=true;trustServerCertificate=true;";
        String user = "USER@SERVER";
        String pass = "PASSWORD";

        // Declare the JDBC object.
        Connection conn = null;

        try {
            // Establish the connection.
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(connectionUrl, user, pass);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

But I am getting:

com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Broken pipe ClientConnectionId:742ba7b5-43bb-441b-8e8d-bbb9dc5fa8ce".
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2226)
    at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1756)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1803)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1285)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1131)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at run.ExampleSQLJDBC.main(ExampleSQLJDBC.java:21)
    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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Interestingly, I can connect to the database from the same computer with Navicat. Hence, the problem should not be in the setting of firewalls.

Metadata:

  • Server: Azure V12
  • Driver: 6.0 (sqljdbc42.jar)
  • JRE: 1.8.0_72-b15 (from Oracle)
  • _JAVA_OPTIONS: -Djsse.enableCBCProtection=false
  • security.provider.1: sun.security.provider.Sun
  • OS: OS X 10.11.5

How can I connect to Azure SQL from Java?

Edit:

If I compile the code from Peter Pan with javac 1.8.0_92 on OS X and execute the bytecode with java 1.8.0_92 on OS X, the error does not disappear.

Edit 2: When I debuged the SSL connection details with -Djavax.net.debug=ssl:handshake:verbose:

trigger seeding of SecureRandom
done seeding SecureRandom
Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_CBC_SHA256
Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_RSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384
Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA256
Ignoring unavailable cipher suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
Ignoring unavailable cipher suite: TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
Allow unsafe renegotiation: false
Allow legacy hello messages: true
Is initial handshake: true
Is secure renegotiation: false
Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 for TLSv1
Ignoring unsupported cipher suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_DHE_RSA_WITH_AES_128_CBC_SHA256 for TLSv1.1
Ignoring unsupported cipher suite: TLS_DHE_DSS_WITH_AES_128_CBC_SHA256 for TLSv1.1
%% No cached client session
*** ClientHello, TLSv1.2
RandomCookie:  GMT: 1466081162 bytes = { 238, 135, 254, 162, 132, 164, 75, 240, 96, 107, 17, 103, 60, 109, 229, 149, 31, 110, 218, 87, 49, 29, 121, 114, 115, 6, 44, 32 }
Session ID:  {}
Cipher Suites: [TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256, TLS_RSA_WITH_AES_128_CBC_SHA256, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_DSS_WITH_AES_128_CBC_SHA256, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA, TLS_RSA_WITH_AES_128_CBC_SHA, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_DSS_WITH_AES_128_CBC_SHA, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_DSS_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDH_ECDSA_WITH_3DES_EDE_CBC_SHA, TLS_ECDH_RSA_WITH_3DES_EDE_CBC_SHA, SSL_DHE_RSA_WITH_3DES_EDE_CBC_SHA, SSL_DHE_DSS_WITH_3DES_EDE_CBC_SHA, TLS_EMPTY_RENEGOTIATION_INFO_SCSV]
Compression Methods:  { 0 }
Extension elliptic_curves, curve names: {secp256r1, sect163k1, sect163r2, secp192r1, secp224r1, sect233k1, sect233r1, sect283k1, sect283r1, secp384r1, sect409k1, sect409r1, secp521r1, sect571k1, sect571r1, secp160k1, secp160r1, secp160r2, sect163r1, secp192k1, sect193r1, sect193r2, secp224k1, sect239k1, secp256k1}
Extension ec_point_formats, formats: [uncompressed]
Extension signature_algorithms, signature_algorithms: SHA512withECDSA, SHA512withRSA, SHA384withECDSA, SHA384withRSA, SHA256withECDSA, SHA256withRSA, SHA224withECDSA, SHA224withRSA, SHA1withECDSA, SHA1withRSA, SHA1withDSA
Extension server_name, server_name: [type=host_name (0), value=hlt3qa7607.database.windows.net]
***
main, WRITE: TLSv1.2 Handshake, length = 233
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT:  warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 2
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 did not return a response. The connection has been closed. ClientConnectionId:1eb03958-1566-4844-b842-e020f0c36121
main, called closeSocket()
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 did not return a response. The connection has been closed. ClientConnectionId:1eb03958-1566-4844-b842-e020f0c36121"....

Edit 3: After setting the driver to log all:

Jun 17, 2016 9:26:37 AM com.microsoft.sqlserver.jdbc.SQLServerDriver:1 connect
FINER: ENTRY Arguments not traced.
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:serverName Value:hlt3qa7607.database.windows.net
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:portNumber Value:1433
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:databaseName Value:DATABASE
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:encrypt Value:true
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:trustServerCertificate Value:true
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection <init>
FINE: ConnectionID:1 created by (SQLServerDriver:1)
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINER: ConnectionID:1 Start time: 1466148398058 Time out time: 1466148413058 Timeout Unit Interval: 1200
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINE: ConnectionID:1 This attempt server name: hlt3qa7607.database.windows.net port: 1433 InstanceName: null useParallel: false
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINE: ConnectionID:1 This attempt endtime: 1466148399258
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection login
FINE: ConnectionID:1 This attempt No: 0
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: ConnectionID:1 Connecting with server: hlt3qa7607.database.windows.net port: 1433 Timeout slice: 1194 Timeout Full: 15
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel open
FINER: TDSChannel (ConnectionID:1): Opening TCP socket...
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINER: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Requesting encryption level:ON
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINER: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c ActivityId 866c0053-25be-4eb2-90c7-a40b5b0535a4-1
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel logPacket
FINEST: /192.168.1.186:63328 SPID:0 ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Prelogin request                                         ...
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.TDSChannel logPacket
FINEST: /192.168.1.186:63328 SPID:0 ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Prelogin response
Jun 17, 2016 9:26:38 AM com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
FINE: ConnectionID:1 ClientConnectionId: 1a3032c8-493d-4f99-8641-d0f02211d82c Server returned maj

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The problem was solved with these steps:

  1. Open terminal (/Applications/Utilities/Terminal)
  2. Run scutil --get HostName (case sensitive!)
  3. If hostname is not set or contains .local, run sudo scutil --set HostName "newname"
  4. Try JDBC Azure SQL Connection again - hostname change is effective immediately

Source: https://youtrack.jetbrains.com/issue/JRE-221#comment=27-1826316


与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

Just Browsing Browsing

2.1m questions

2.1m answers

60 comments

57.0k users

Most popular tags

Powered by Question2Answer
Donate
Theme by Q2A Market&&OStack.cn
...