Problem with creating a connection: The SQL Server login requires an SSL connection.

SubashC
SubashC E
edited February 11, 2022 in Analytics #1

I am trying to create a connection to MS SQL server database from Analytics Designer.
I provided the Hostname, portnumber, username and password.
Connection URL - jdbc:actuate:sqlserver://XXXXXXXXXXXXXXXXXXXX:1433;DatabaseName=XXXXXXXXXXXXX

The test connection was not successful and the following error was returned.
java.sql.SQLNonTransientConnectionException: [ActuateDD][SQLServer JDBC Driver]The SQL Server login requires an SSL connection.
at com.actuate.jdbc.sqlserverbase.ddcw.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.ddcw.a(Unknown Source)
at com.actuate.jdbc.sqlserverbase.ddcv.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.ddcv.a(Unknown Source)
at com.actuate.jdbc.sqlserver.tds.ddc.(Unknown Source)
at com.actuate.jdbc.sqlserver.SQLServerImplConnection.f(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.k(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.a(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseDriver.connect(Unknown Source)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
at com.actuate.data.oda.jdbc.dbprofile.impl.MsftSqlServerConnectionFactory.createConnection(MsftSqlServerConnectionFactory.java:48)
at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

I also tried with the settings EncryptionMethod=SSL; ValidateServerCertificate=False;
For which i am getting the below error.
java.sql.SQLNonTransientConnectionException: [ActuateDD][SQLServer JDBC Driver]SSL handshake failed: Remote host closed connection during handshake
at com.actuate.jdbc.sqlserverbase.ddcw.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.ddcw.a(Unknown Source)
at com.actuate.jdbc.sqlserverbase.ddcv.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.ddcv.a(Unknown Source)
at com.actuate.jdbc.sqlserver.tds.ddc.(Unknown Source)
at com.actuate.jdbc.sqlserver.SQLServerImplConnection.f(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.k(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.b(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseConnection.a(Unknown Source)
at com.actuate.jdbc.sqlserverbase.BaseDriver.connect(Unknown Source)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:328)
at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:96)
at com.actuate.data.oda.jdbc.dbprofile.impl.MsftSqlServerConnectionFactory.createConnection(MsftSqlServerConnectionFactory.java:48)
at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

Can anyone please help.
Thanks in Advance.

Regards,
Subash

Best Answer

  • SubashC
    SubashC E
    #2 Answer ✓

    Hi,

    Thank you for the suggestions.

    The issue was in the SSL settings on the windows server where DB was installed.
    After enabling the Ciphers (Triple DES 168), Hashers (MD5 and SHA) and Key Exchanges (Diffie-Hellman) in the windows server where the DB was installed, the connection was successful.

    Regards,
    Subash

Answers

  • Chad Montgomery
    Chad Montgomery E
    #3

    I'm not sure if ValidateServerCertificate is case-sensitive, but it's worth a shot. Try using "false" instead of "False".
    Barring that, I think it should work, I'd maybe follow-up with your DBA as they will be familiar with the DB setup and how to properly connect to it.

    Warning No formatter is installed for the format ipb
  • Steve_S
    Steve_S E
    #4

    Hi Subash,

    It looks that you have the Force protocol encryption feature enabled on the SQL Server. The options that you have selected in the JDBC driver should intiate the SSL handshake and then not worry about the Server certificate that it gets in the response. Since the handshake is failing at the remote server, either it is expecting a client certificate from the Designer, or the initial request from the client is failing (maybe it's asking for a encryption method that is not supported. Here's some things to try:

    1) Login to the server with SQL Management Console using the same credentials and checking the "encyrpt connection" option in Management Console. See sqlservermart.com/HowTo/MSSQL_Over_SSL.aspx

    2) There some nice network tools available at the following location: https://progress.com/support/evaluation/download-resources/download-tools. If you want to see what is being transmitted in the connection, use Snoop.

    If you think you need some deeper help, go ahead and open a ticket at "My Support."

    Steve_S

    Stephen S.
    Customer Support Engineer
    Opentext

  • SubashC
    SubashC E
    #5 Answer ✓

    Hi,

    Thank you for the suggestions.

    The issue was in the SSL settings on the windows server where DB was installed.
    After enabling the Ciphers (Triple DES 168), Hashers (MD5 and SHA) and Key Exchanges (Diffie-Hellman) in the windows server where the DB was installed, the connection was successful.

    Regards,
    Subash

Categories

image
OpenText CE Products
Magellan Text Mining
Docs & APIs