Pentaho

 View Only
  • 1.  Problems upgrading MSSQL's JDBC to 11.2 and how to set properly encrypt=false in the JDBC URL

    Posted 08-23-2022 13:41
    Hi,

    I am upgrading MSSQL's JDBC to the latest version (currently 11.2) but after copying the file into the lib folder I am getting the following error:
    The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:8563cdf7-79c9-4979-891a-07ee415b1ffe
    The problem is related to a breaking change introduced with version 10.2:
    https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver16#previous-releases

    Default encrypt to true BREAKING CHANGE TLS encryption is enabled by default.

    From Pentaho GUI is not not possible to disable the TLS encryption... Well there is a hack, by adding ;encrypt=false to the database name. Like this:

    This hack will create a proper JDBC URL connection:

    And therefore the connection will succeed. But it is a cowboy job :P

    Another option is to use JNDI connections. eg

    JNDI_JDBC_TEST/type=javax.sql.DataSource
    JNDI_JDBC_TEST/driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
    JNDI_JDBC_TEST/url=jdbc:sqlserver://SERVER:1443;databaseName=DATABASE;integratedSecurity=false;encrypt=false;

    Both options work OK but the hacky one is just ... hacky and the JNDI approach will require a lot of work in order to migrate all the connections.

    Any other option I am missing?

    Thanks for your time

    Best regards





    ------------------------------
    Juan Sierra Pons
    Systems Engineer
    Juan Sierra Pons
    ------------------------------


  • 2.  RE: Problems upgrading MSSQL's JDBC to 11.2 and how to set properly encrypt=false in the JDBC URL
    Best Answer

    Posted 08-23-2022 19:11
    Hi Juan

    You could add "encrypt=false" to the connection Options dialog which has the same effect but is neater, or construct a full JDBC string and use a Generic Database connection instead.

    The more complex one is to find the code for the connector plugin jar in the pentaho git repo, adjust it yourself and recompile it.

    ------------------------------
    Andrew Cave
    Systems Engineer
    BizCubed Pty Ltd
    Australia
    ------------------------------



  • 3.  RE: Problems upgrading MSSQL's JDBC to 11.2 and how to set properly encrypt=false in the JDBC URL

    Posted 08-24-2022 02:22
    Hi @Andrew Cave,

    That was exactly what I was looking for. I tested it and works like a charm :)

    Thanks for your time


    ------------------------------
    Juan Sierra Pons
    Systems Engineer
    Juan Sierra Pons
    ------------------------------



  • 4.  RE: Problems upgrading MSSQL's JDBC to 11.2 and how to set properly encrypt=false in the JDBC URL

    Posted 08-24-2022 07:40
    @Andrew Cave,

    Do you know if is it possible to set the encrypt=false as the default value for all connections strings?
    This way I will modify and set encrypt=true to the ones that support it

    Thanks for your time

    Best regards


    ------------------------------
    Juan Sierra Pons
    Systems Engineer
    Juan Sierra Pons
    ------------------------------



  • 5.  RE: Problems upgrading MSSQL's JDBC to 11.2 and how to set properly encrypt=false in the JDBC URL

    Posted 08-24-2022 20:44
    Alas I don't think there is,

    ------------------------------
    Andrew Cave
    Systems Engineer
    BizCubed Pty Ltd
    Australia
    ------------------------------