View Only
Expand all | Collapse all

Unable to conect to SQL SERVER 2019 developer

This thread has been viewed 13 times
  • 1.  Unable to conect to SQL SERVER 2019 developer

    Posted 01-26-2023 12:34
    I am new to the community,  it has been a number of years since I last used Pentaho, trying to get back into using Pentaho and doing tutorial to get me up to speed.

    Have downloaded and successfully in installed the latest Pentaho to my laptop

    I intend to use SQL Server 2019 developer version (non encrypted) as my database

    I have followed this blog to setup a SQL server connection

    When I test the connection, I get the following message.

    Connection failed. Verify all connection parameters and confirm that the appropriate driver is installed.

    The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: " PKIX path building failed: unable to find valid certification path to requested target".

    Any suggestions to resolve this issue.

    If this issue has all already been addressed by the community  (can you let me know how to search the community posts)

    Thanking you in advance

    Ross Ecclesfield
    HelpDesk Support
    Pure ETL

  • 2.  RE: Unable to conect to SQL SERVER 2019 developer

    Posted 02-01-2023 05:35
    Hi Ross,

    The error is indicating that the SQL Server is SSL secured, but the Java Virtual Machine does not trust the certificate presented by the SQL Server.

    You will want to download the certificate to a file on the server. You can then open a command line and CD into the \bin folder located in that jre folder. Once in bin, the following command can be used to import your certificate file into the Java keystore:

    keytool -v –import -file <path to your certificate> -keystore ..\lib\security\cacerts

    This will ask for a password to the cacerts file. Unless you have already changed it, the default password is: changeit
    This should then show you the details of the certificate and ask if you want to trust it. Type yes and hit ENTER.
    If it was successfully added, you'll then see a message saying it was added to the keystore.

    Once this is done, you can stop Pentaho on the server and then clear logs and cache files:

    Before starting Pentaho, you will also want to clear the contents of the following folder:

    Then, start Pentaho and see if you get better results.

    If for some reason you still see the error, you could add the ;encrypt=true;trustServerCertificate=true to your JDBC connection strings for Pentaho. You would have to do this on every single connection string in the Pentaho configuration files:
    1) ..\pentaho-server\pentaho-solutions\system\jackrabbit\repository.xml
    2) ..\pentaho-server\tomcat\webapps\pentaho\META-INF\context.xml

    You would then need to clear the logs and cache file as well as clearing the contents of ..\pentaho-server\pentaho-solutions\system\jackrabbit\repository\ as directed above.

    Jagdeesh SS
    Application Architecture Engineering - Senior
    Hitachi Vantara