Hi Rob, I can't really speak to your specific connection issues, but just like you, I too often time revert to a manual JDBC connection string. And you got it sorted out anyway.
Regarding your second point, there's a known issue that Hop sometimes adds double squared brackets around the schema or table name (depending on how you selected the table). This is on the developers' radar for 2.6. Meanwhile, I'd say just review the SQL code manually and remove the unneeded brackets: Cheers, Gert > On 2023-08-21 9:50 PM EDT Rob Burgess <[email protected]> wrote: > > > Hi > > I'm unsure if this is a bug or a user configuration error. > > In PDI 9.3 I can connect to a SQL Server 2022 database using the Database > Connection form and the standard fields like hostname, database name, port, > username and password. > > In Hop 2.5.0 (2023-06-08 07.58.35) on Windows server 2022 I get the > following errors when trying to connect to the same database using the > standard fields in the Relational Database Connection form. > > Error connecting to database [DatabaseName] > :org.apache.hop.core.exception.HopDatabaseException: > Error occurred while trying to connect to the database > > Error connecting to database: (using class > com.microsoft.sqlserver.jdbc.SQLServerDriver) > 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:0655e885-79ce-4f33-a8fb-4b9feebace16 > > org.apache.hop.core.exception.HopDatabaseException: > Error occurred while trying to connect to the database > > Error connecting to database: (using class > com.microsoft.sqlserver.jdbc.SQLServerDriver) > 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:0655e885-79ce-4f33-a8fb-4b9feebace16 > > > I managed to to connect to the database using a mixture of the standard > fields in the Relational Database Connection form and the following > connection string in the Manual Connection URL field: > > jdbc:sqlserver://HostName:1433;database=DatabaseName;encrypt=true;trustServerCertificate=true; > > > Also, after successfully connecting to the SQL Server database when I try > exploring the database, I get the following error when previewing the first > 100 rows of the table: > > InvocationTargetException: Couldn't find any rows because of an error > :org.apache.hop.core.exception.HopDatabaseException: > An error occurred executing SQL: > SELECT * FROM [[dbo]].DATABASE > Unclosed quotation mark after the character string '[dbo].DATABASE'. > Root cause: SQLServerException: Unclosed quotation mark after the character > string '[dbo].DATABASE'. > > Kind regards > > Rob > >
