How do I resolve this issue. I didn't use to get this message but now I do. Cannot really find a clear definition on what to do.


ERROR: 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

I would start by checking the certificate and seeing if it has expired. Then I would have to use a packet sniffer and watch the exchange and troubleshoot from there.

Agree with John here.
Also I would follow below approach:
1. If DB allows insecure access (just temporarily), setup for non TLS access. Then test connection.
This is to establish that non TLS connection works.
2. Next set DB in SSL mode.
3. Add DB's server cert to Breeze's WAS trust-store. Add Breeze's server cert to DB's trust-store.
4. If the connection does not work yet, check logs on DB end. (You generally find a lot more details in DB connection logs on DB end)
5. Also you could enable SSL handshake to see where exactly the handshake fails (If that's the reason.)

Regards,
Prasanna

prasanna.kulkarni wrote:Agree with John here.
Also I would follow below approach:
1. If DB allows insecure access (just temporarily), setup for non TLS access. Then test connection.
This is to establish that non TLS connection works.
2. Next set DB in SSL mode.
https://breakfasthoursofficial.com/dunkin-donuts-breakfast-hours
https://breakfasthoursofficial.com/mcdonalds-breakfast-hours
https://breakfasthoursofficial.com/taco-bell-lunch
3. Add DB's server cert to Breeze's WAS trust-store. Add Breeze's server cert to DB's trust-store.
4. If the connection does not work yet, check logs on DB end. (You generally find a lot more details in DB connection logs on DB end)
5. Also you could enable SSL handshake to see where exactly the handshake fails (If that's the reason.)

Regards,
Prasanna

thanks for the awesome information.

Ok I have some new information on this. It is partially fixed and partially still broken and i am not sure how to resolve it.

We have 5 Breeze Servers on 1 VM Farm same subnet in 1 Breeze Cluster.

Cluster Global Parameter for All Connections other than SIP = TLSv1.2

If I try to connect using the TLS checkbox from my JDBC Source to a MS SQL Server with jdbc url - jdbc:sqlserver://Servername:Port;databaseName=database; , all 5 servers fail and are trying to use TLS 1.0 according to my server side traces.

In my JDBC settings regardless if i check or uncheck the TLS Checkbox(This checkbox does nothing) using jdbc url - jdbc:sqlserver://Servername:Port;databaseName=database; all 5 servers fail and are trying to use TLS 1.0 according to my server side traces.


But if I add the parameter sslProtocol=TLSv1.2 to my jdbc url - jdbc:sqlserver://Servername:Port;databaseName=database;sslProtocol=TLSv1.2; ,3 out of the 5 servers get a connection test successful. My traces on the sql server show the unsuccessful servers are still trying to use 1.0 still and the successful ones are using 1.2 and actually connecting to SQL.


What is the problem and why do all 5 servers not care about what is set from the cluster level and also why do only 3 of 5 servers connect using tls 1.2 when the jdbc url is shared by all 5 servers.

While we wait for expert help to assist in this my approach would be draconian – if rebooting the problem nodes doesn’t fix it, delete the two problem nodes and clone one of the working ones to replace them. They have either cached something they shouldn’t have and are stuck with the out of date TLS version knowledge, or ?? but starting afresh with new nodes in place of the problem ones with the starting point of something that is working would be simplest and hopefully would allow you to move forward somewhat smoothly while we research this.

As to why the nodes are (mostly) ignoring the breeze config information... we need an expert. We are working to locate one.

Hi Marcus,

Please see my response and next steps added to your post below:

"
Ok I have some new information on this. It is partially fixed and partially still broken and i am not sure how to resolve it.
We have 5 Breeze Servers on 1 VM Farm same subnet in 1 Breeze Cluster.
Cluster Global Parameter for All Connections other than SIP = TLSv1.2
>> This configuration governs all breeze internal components like what's the TLS version to set for "incoming" HTTP/Websocket/JDBC connections. Using this property Breeze configures all it's internal software.
Database servers are external and they've their own configuration for SSL hence cluster level setting does not affect them. (Because Breeze is acing as a client here and cannot force it's own TLS version to server.)
I'll make sure that we add developer documentation to clarify this in better manner - sorry for the confusion on this configuration.

If I try to connect using the TLS checkbox from my JDBC Source to a MS SQL Server with jdbc url - jdbc:sqlserver://Servername:Port;databaseName=database; , all 5 servers fail and are trying to use TLS 1.0 according to my server side traces.
In my JDBC settings regardless if i check or uncheck the TLS Checkbox(This checkbox does nothing) using jdbc url - jdbc:sqlserver://Servername:Port;databaseName=database; all 5 servers fail and are trying to use TLS 1.0 according to my server side traces.
>>
This tells that - Database server is not accepting any non TLS connections at all. When TLS checkbox is unchecked you get TLS error is the indication of it.
Now why it uses TLSv1.0 by default? It's because when protocol is not specified, that's the default protocol set by JDBC driver. (Please refer com.microsoft.sqlserver.jdbc.SQLServerDriverPropertyInfo)
When you tick TLS checkbox, it simply tells Breeze that far end DB server accepts TLS connections, so please make id certificate available if it's asked.
How the incoming connection is treated is entirely up to database server and it's JDBC driver.


But if I add the parameter sslProtocol=TLSv1.2 to my jdbc url - jdbc:sqlserver://Servername:Port;databaseName=database;sslProtocol=TLSv1.2; ,3 out of the 5 servers get a connection test successful. My traces on the sql server show the unsuccessful servers are still trying to use 1.0 still and the successful ones are using 1.2 and actually connecting to SQL.
>>
This means database server only accepts v1.2 traffic, totally reasonable.
What is the problem and why do all 5 servers not care about what is set from the cluster level and also why do only 3 of 5 servers connect using tls 1.2 when the jdbc url is shared by all 5 servers.
>>
Now to real problem - this should not happen, all five servers from node should be able to connect to the database when TLSv1.2 is set.
Can you follow below debug steps and we can try to figure out what's going wrong.
1. First thing that comes to mind is, on two servers where connection is faiing, data replication(DRS) may not be happening.
I.e. The URL is updated in SMGR DB but breeze side copy is not updated. Can you please confirm below
a. DRS between breeze node and SMGR is successful.
b. Once DRS is successful, login to breeze node and run following query from mgmtia
"select * from zr_ext_jdbc_datasource"
and check the "url" field - it should contail URL with sslProtocol=TLSv1.2 string.
If so, then restart the node.
2. Let's say DRS, URL all is good, and you're still facing this problem then that means for some reason datasource created in application server is not getting updated.
So please go to the JDBC provider snap-in that's created for the provider that you've created. E.g. if you've created a JDBC provider called "MSSQL64" then there'd be a snap-in installed on cluster with that name.
Please go to that snap-in from "Breeze->services" page on SMGR and reinstall this snap-in on two failing nodes, then restart the nodes.
3. If this also does not fix the issue then we may need to dig further by going into admin console of the application server, but I think one of the above should fix the issue.

Thanks for your patience,
Prasanna
"

Do you have instructions on how to do this?

1. First thing that comes to mind is, on two servers where connection is faiing, data replication(DRS) may not be happening.
I.e. The URL is updated in SMGR DB but breeze side copy is not updated. Can you please confirm below
a. DRS between breeze node and SMGR is successful.
b. Once DRS is successful, login to breeze node and run following query from mgmtia
"select * from zr_ext_jdbc_datasource"
and check the "url" field - it should contail URL with sslProtocol=TLSv1.2 string.

[root@breezenode cust]# mgmtia
asm-> select * from zr_ext_jdbc_datasource


I get nothing from this command if this is how it is run.

disregard, figured it out

Went through the suggestions, both a working node and non working node had same config in the database for the jdbc url when i ran the query. I re-installed the service on the 2 non working nodes, denied service, rebooted them, put them back in service. Re-ran test and they both failed the test while the other 3 passed.