Problem connecting from macOS to SQL Azure

[diegolesli]

Hello,

When connecting to a SQL Azure database from mac I am getting the error below running the connectURL example.

mvn -q exec:java -Dexec.mainClass=com.sqlsamples.App -P connectURL

Output

com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed. ClientConnectionId:8de59301-094c-4f5e-bad9-83142d15c249
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2392)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2376)
	at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6680)
	at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7993)
	at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7945)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4375)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3188)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:61)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3151)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1973)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1616)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1447)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:788)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1187)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at connectURL.main(connectURL.java:63)
	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 org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:297)
	at java.lang.Thread.run(Thread.java:745)

I am using macOS 10.12.3, JDK 8 Update 121. Same example from Windows is working fine and I can connect with no problems.

I also tried a connection string different with the following format

jdbc:sqlserver://server.database.windows.net;databaseName=database-name;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

I also tried the parameters in different combinations
-Djsse.enableCBCProtection=false
-Djdk.tls.client.protocols=TLSv1

Attached you can find the output when debugging SSL as suggested in a couple of places, but I could not figure out the root of the problem

output.txt

If I try to connect to a SQL Server deployed in the RDS service of AWS everything works as expected

Any help will be appreciated

[v-nisidh]

Thanks @diegolesli for reach out to us.

From your comments we understood that on macOS you are facing connectivity problems. We will you keep posted.

I am using macOS 10.12.3, JDK 8 Update 121. Same example from Windows is working fine and I can connect with no problems.

[diegolesli]

Thanks, yes it is a problem connecting from macOS to SQL Azure

[Suraiya-Hameed]

Hi @diegolesli, I'm able to connect to Azure SQLServer with the macOS and JDK combination you mentioned even without any additional parameters. Can you try testing with increased loginTimeout just to see if Server responds? also, can you double check if the firewall rule in Azure is setup for the client machine?

[diegolesli]

Hi,

Thanks for your time. Just to be sure I created a new SQL Azure Database from scratch from the same computer I am connecting from and the error is still the same.

So I took another mac same version and I checked with same JDK version and it works. So it is clearly a problem from my computer and not of the driver.

Sorry for making you lose your time. Anyway, if you could help me identify what to look that would be very helpful, because I only have a problem with Azure (to Amazon RDS I can connect with no problem)

I increased the loginTimeout to 60s and after 60s I get the same error. I double checked the default created firewall rule and it works ok since I can connect from the other computer.

I guess it is something related with the certificates I have in my machine, but I cannot figure out what the real problem is.

Thanks,

[Suraiya-Hameed]

@diegolesli no problem, we are happy to help, glad that you were able narrow down the cause to your local machine. Can you try enabling the logs for the level FINEST and see if the driver logs any warning message? The warning message, if any, should be at the end of log.

[ajlam]

Hi @diegolesli, if you do enable logs, please feel free to send them directly to andrela@microsoft.com.

[diegolesli]

Hi,

Attached you can find the trace.log after enabling logs for SQL Server driver.

Thanks for all the help you are providing me!

trace.txt

[xiangyushawn]

@diegolesli Hello, can I ask what version the driver is?

[xiangyushawn]

@diegolesli could you please do us another favor? Could you please run it again on the Mac machine that is working for you, with the same SQL Azure credentials? and give us the log file? We want to compare the 2 log files and see the differences. Thank you very much.

[xiangyushawn]

Hello @diegolesli , have you resolved the issue on your Mac?

[diegolesli]

Hello @v-xiangs sorry for the lack of reply I have been out in a business trip without access to the other computer. The issue still persists. By tomorrow I will attach both files again with the same project.

Sorry for the delay and thanks for all the help

[diegolesli]

trace.txt

Hello,

In the computer it was working it is not working anymore. It is from a different location (I asked a college of mine), different network and different computer, but same JDK and OSX version. Same problem as it is happening with me.

I am going to try a couple of different macs to see if it is working in any of them.

[diegolesli]

trace.txt

Hello,

Attached is a different computer, same OSX and JDK and different network connection. The result is the same. The database has been created newly and from the app SQLPro for MSSQL is working ok, but from the plugin of VS Code the error is the same.

Let me know if I can help with something else. I will try to find a MAC OSX in which is working.

Thanks,

[Suraiya-Hameed]

Hi @diegolesli, we couldn't figure out much with the new trace you shared. We will look into the trace from Sierra that works with Azure, once you share it.

[micahasmith]

we're experiencing the same issue (to the T)-- any steps we can take to produce diagnostic output for you to look at? @v-suhame

[SerhiiKozachenko]

Problem solved with these steps:

open terminal (/Applications/Utilities/Terminal)
run scutil --get HostName (case sensitive!)
if hostname is not set or contains .local, run sudo scutil --set HostName "newname"
try JDBC Azure SQL Connection again - hostname change is effective immediately

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

[diegolesli]

Hello @Grievoushead

That really worked for me. In my case hostname was not even set so I set it to something without the local suffix and it connected perfectly.

Thanks very much for all the help!

[xiangyushawn]

@Grievoushead Thank you very much for providing the solution to this issue !

[JulioTalavera]

For three days i tried to connect to Azure SQL Server, i downloaded a fresh copy of JDBC 6 but I received the same "SQL Server did not return a response" error message. @Grievoushead Thanks for the solution, now it works perfectly!

[Suraiya-Hameed]

Driver uses InetAddress.getLocalHost() to set the property workstationID. As noted in several places online, the macOS Sierra with certain version of JVM takes unusually long time to execute getLocalHost.

PR #324 fixes the need to manually set localhostname. There might be noticeable delay while establishing connection, as driver continues to uses InetAddress.getLocalHost(). To overcome this, either use the solution shared by @Grievoushead or set the connection property workstationID.

PS: The other alternative to InetAddress.getLocalHost() would be to make JNI calls on UNIX OS to get local host, that would introduce new dependencies. So we are sticking with current implementation.

[jameshowe]

Just got stung with the same issue trying to connect to MSSQL via SQuirreLSQL on MacOS - hostname fix worked for me too 👍

[italin40]

https://stackoverflow.com/questions/35918910/ssl-handshake-with-no-server-respone-with-java-1-8

[italin40]

https://stackoverflow.com/questions/35918910/ssl-handshake-with-no-server-respone-with-java-1-8

[victorpalmeida]

Hi, I was facing the same issue when using IBM JDK 8 since it was using TLSv1 connecting to a MS SQL Server installed on Win2019 which was enforcing TLSv1.2. I solved the problem by setting this property at JVM level:
-Dcom.ibm.jsse2.overrideDefaultTLS=true. The SQL jdbc driver version used was v12.2 jre8.