Pehtaho DI v9.3 can't connect to MS SQL Server DB 19

Stas 0
Viewed 33

I have a problem with the connection to SQL Server via Pentaho DI. I downloaded the latest versions of Pentaho 9.3, JDK 18.0.2.1, and JRE 1.8.0_341

Then I downloaded and extracted "sqljdbc_11.2" drivers for connection to SQL Server and copied file 'mssql-jdbc-11.2.0.jre8.jar' into Pentaho folder '...PentahoDI\data-integration\lib' and trying to connect through a new connection in Spoon, but getting an error:

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: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException:
unable to find valid certification path to requested target". ClientConnectionId:134f753-c55f-266d-9081-235aec6fa4a0'

And there is no answer for me on how to solve this problem on Pentaho. Maybe someone knows this issue?

pentaho java sql-server sql
2 Answers

Possibly related to the change in 10.2 that uses Certificate Validation even when encrypt is false. Depending on how your MSSQL server is configured. If it's set to use SSL then Pentaho will try and validate the SSL certificate against your Java trust store on the Penaho server. You may have to add the SSL certificate to the cacerts trust store. Older drivers wouldn't have that requirement and that's why the older driver would work.

DougieT0

Microsoft JDBC Driver 6.0 for SQL Server - this driver helped and worked excellently with Pentaho 9.3. but not the latest one from the Microsoft site. Hope it will be helpful for someone.

Stas0
Related