Problem with MS Sql JDBC Connections

[EAwesomeK]

I am running the latest OOB Birt all in one. I have successfully installed and used the MS SQL JDBC 9.4 driver however when I try to update to anything newer I have to add the following to the end of the URL line: encrypt=true;trustservercertificate=true or testing the connection fails with the following error:

org.eclipse.datatools.connectivity.oda.OdaException: Failed to create a connection. Please verify the connection properties.
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$TestInProcessDialog$TestConnectionJob$1.run(JDBCSelectionPageHelper.java:191)
at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:40)
at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:132)
at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:4029)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3645)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:823)
at org.eclipse.jface.window.Window.open(Window.java:799)
at org.eclipse.jface.dialogs.MessageDialog.open(MessageDialog.java:609)
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$8.widgetSelected(JDBCSelectionPageHelper.java:908)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:252)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4256)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1066)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4054)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3642)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:823)
at org.eclipse.jface.window.Window.open(Window.java:799)
at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:109)
at org.eclipse.birt.report.designer.data.ui.providers.EditableDataSourceNodeProvider.performEdit(EditableDataSourceNodeProvider.java:65)
at org.eclipse.birt.report.designer.internal.ui.views.DefaultNodeProvider.performRequest(DefaultNodeProvider.java:414)
at org.eclipse.birt.report.designer.internal.ui.views.actions.EditAction.doAction(EditAction.java:76)
at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:61)
at org.eclipse.birt.report.designer.internal.ui.views.RenameListener.doubleClick(RenameListener.java:210)
at org.eclipse.jface.viewers.StructuredViewer$1.run(StructuredViewer.java:780)
at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:45)
at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:174)
at org.eclipse.jface.viewers.StructuredViewer.fireDoubleClick(StructuredViewer.java:777)
at org.eclipse.jface.viewers.AbstractTreeViewer.handleDoubleSelect(AbstractTreeViewer.java:1542)
at org.eclipse.jface.viewers.StructuredViewer$4.widgetDefaultSelected(StructuredViewer.java:1211)
at org.eclipse.jface.util.OpenStrategy.fireDefaultSelectionEvent(OpenStrategy.java:272)
at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:329)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:89)
at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4256)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1066)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4054)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3642)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$5.run(PartRenderingEngine.java:1155)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1046)
at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:155)
at org.eclipse.ui.internal.Workbench.lambda$3(Workbench.java:643)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:338)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:550)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:171)
at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:152)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:203)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:136)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:402)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:255)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:659)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:596)
at org.eclipse.equinox.launcher.Main.run(Main.java:1467)

If I add the encrypt=true;trustservercertificate=true to the end of the line the connection passes but when I run Birt viewer I get the following error:**

org.eclipse.birt.report.engine.api.EngineException: An exception occurred during processing. Please see the following message for details:
Cannot open the connection for the driver: org.eclipse.birt.report.data.oda.jdbc.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: There is an error in get connection, 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:ca8c2b7f-ac81-442c-a3b2-320eeee ....
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1062)
at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1047)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:83)
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:60)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:45)
at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:45)
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:41)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:62)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:97)
at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:233)
at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:224)
at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:85)
at org.eclipse.birt.report.service.ReportEngineService.runReport(ReportEngineService.java:1086)
at org.eclipse.birt.report.service.BirtViewerReportService.runReport(BirtViewerReportService.java:146)
at org.eclipse.birt.report.service.actionhandler.BirtRunReportActionHandler.__execute(BirtRunReportActionHandler.java:79)
at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.runReport(BirtChangeParameterActionHandler.java:52)
at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:50)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:88)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:45)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:132)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleChangeParameter(BirtDocumentProcessor.java:96)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:107)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:67)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:321)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:420)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:684)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:227)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:517)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:332)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:584)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:115)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1419)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:71)
at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:210)
at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1571)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1383)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1544)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1305)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:149)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:822)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:139)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:933)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1077)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:
Cannot open the connection for the driver: org.eclipse.birt.report.data.oda.jdbc.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: There is an error in get connection, 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:ca8c2b7f-ac81-442c-a3b2-320eeee ....
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:609)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:138)
at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:74)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:254)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1632)
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:73)
... 84 more
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot open the connection for the driver: org.eclipse.birt.report.data.oda.jdbc.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: There is an error in get connection, 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:ca8c2b7f-ac81-442c-a3b2-320eeee ....
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:56)
at org.eclipse.birt.data.engine.odaconsumer.ConnectionManager.openConnection(ConnectionManager.java:169)
at org.eclipse.birt.data.engine.executor.DataSource.newConnection(DataSource.java:210)
at org.eclipse.birt.data.engine.executor.DataSource.open(DataSource.java:199)
at org.eclipse.birt.data.engine.impl.DataSourceRuntime.openOdiDataSource(DataSourceRuntime.java:195)
at org.eclipse.birt.data.engine.impl.QueryExecutor.openDataSource(QueryExecutor.java:418)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:302)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:388)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:164)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:153)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:172)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:137)
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery.populatePreparedQuery(PreparedJointDataSourceQuery.java:468)
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery.populatePreparedQuery(PreparedJointDataSourceQuery.java:418)
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery$JointDataSetQueryExecutor.createOdiQuery(PreparedJointDataSourceQuery.java:499)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:310)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:388)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:164)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:153)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:596)
... 89 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: There is an error in get connection, 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:ca8c2b7f-ac81-442c-a3b2-320eeee ....
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.doConnect(JDBCDriverManager.java:311)
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.getConnection(JDBCDriverManager.java:222)
at org.eclipse.birt.report.data.oda.jdbc.Connection.connectByUrl(Connection.java:220)
at org.eclipse.birt.report.data.oda.jdbc.Connection.open(Connection.java:142)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaConnection.open(OdaConnection.java:250)
at org.eclipse.birt.data.engine.odaconsumer.ConnectionManager.openConnection(ConnectionManager.java:160)
... 107 more

How do I get the newer drivers to work properly?

[wimjongman]

The new drivers check for encryption certificates. You must install a trusted certificate on the SQL server or import your manually generated certificate into the java trust store.

[EAwesomeK]

Thanks. Any recommendations or steps on how to do this? Sent from my mobile device, please ignore any spelling errors

…

-------- Original message -------- From: Wim Jongman ***@***.***> Date: 1/31/23 4:21 AM (GMT-05:00) To: eclipse/birt ***@***.***> Cc: Evan Kontos ***@***.***>, Author ***@***.***> Subject: Re: [eclipse/birt] Problem with MS Sql JDBC Connections (Discussion #1199) The new drivers check for encryption certificates. You must install a trusted certificate on the SQL server or import your manually generated certificate into the java trust store. — Reply to this email directly, view it on GitHub<#1199 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AI2QCYK7DXX6VDPEWUPOQ43WVDKPTANCNFSM6AAAAAAULVWFWM>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[wimjongman]

Ask Google. It is not a BIRT issue.

[hvbtup]

... And if you find a solution, please post it (or a pointer to it) here to help others.

[basisdude65]

See link above by wimjongman ;)

[hvbtup]

There's still a difference between a generic approach and how to set this up for the BIRT IDE and the BIRT runtime. While personally I don't use MSSQL, there are probably lots of BIRT users who will face the same issue. Thus I think that while asking Google will surely point into the right direction, it's nevertheless worth noting the exact steps necessary to set this up for BIRT.