将Pentaho连接到MS SQL Server（本地）

Question

我在使用PENTAHO方面非常新手。我需要将此软件连接到MS SQL Server (Native)。我已经尝试了多次连接，但每次测试连接都失败。 这是每次测试连接时出现的错误消息。

Error connecting to database [sutera] : 
org.pentaho.di.core.exception.KettleDatabaseException: 
Error occured while trying to connect to the database

Error connecting to database: (using class   

com.microsoft.sqlserver.jdbc.SQLServerDriver

The TCP/IP connection to the host 172.16.1.133, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".


org.pentaho.di.core.exception.KettleDatabaseException: 
Error occured while trying to connect to the database

Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver)
The TCP/IP connection to the host 172.16.1.133, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".


at org.pentaho.di.core.database.Database.normalConnect(Database.java:368)
at org.pentaho.di.core.database.Database.connect(Database.java:317)
at org.pentaho.di.core.database.Database.connect(Database.java:279)
at org.pentaho.di.core.database.Database.connect(Database.java:269)
at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:86)
at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2464)
at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:533)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:329)
at org.pentaho.ui.xul.swing.tags.SwingButton$OnClickRunnable.run(SwingButton.java:58)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.WaitDispatchSupport$2.run(Unknown Source)
at java.awt.WaitDispatchSupport$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.WaitDispatchSupport.enter(Unknown Source)
at java.awt.Dialog.show(Unknown Source)
at java.awt.Component.show(Unknown Source)
at java.awt.Component.setVisible(Unknown Source)
at java.awt.Window.setVisible(Unknown Source)
at java.awt.Dialog.setVisible(Unknown Source)
at org.pentaho.ui.xul.swing.tags.SwingDialog.show(SwingDialog.java:234)
at org.pentaho.reporting.ui.datasources.jdbc.ui.XulDatabaseDialog.open(XulDatabaseDialog.java:256)
at org.pentaho.reporting.ui.datasources.jdbc.ui.ConnectionPanel$EditDataSourceAction.actionPerformed(ConnectionPanel.java:162)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.WaitDispatchSupport$2.run(Unknown Source)
at java.awt.WaitDispatchSupport$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.WaitDispatchSupport.enter(Unknown Source)
at java.awt.Dialog.show(Unknown Source)
at java.awt.Component.show(Unknown Source)
at java.awt.Component.setVisible(Unknown Source)
at java.awt.Window.setVisible(Unknown Source)
at java.awt.Dialog.setVisible(Unknown Source)
at org.pentaho.reporting.libraries.designtime.swing.CommonDialog.performEdit(CommonDialog.java:156)
at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcDataSourceDialog.performConfiguration(JdbcDataSourceDialog.java:759)
at org.pentaho.reporting.ui.datasources.jdbc.JdbcDataSourcePlugin.performEdit(JdbcDataSourcePlugin.java:67)
at org.pentaho.reporting.designer.core.actions.report.AddDataFactoryAction.actionPerformed(AddDataFactoryAction.java:79)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.AbstractButton.doClick(Unknown Source)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver)
The TCP/IP connection to the host 172.16.1.133, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:504)
at org.pentaho.di.core.database.Database.normalConnect(Database.java:352)
... 122 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 172.16.1.133, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:104
         9)
at 
    com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833)
at                                          
    com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:478)
... 123 more

有人能帮我解决这个问题吗？我真的需要你的帮助。 谢谢

- crystal

---

要连接到本地模式下的SQL Server，Java必须能够找到sqlauth.dll文件。我通过修改spoon.bat文件并在那里提供sqlauth.dll文件的路径来解决了这个问题。我认为有更好的方法可以做到这一点，但我还没有尝试过。您确定主机和端口是正确的吗？ - Brian.D.Myers

是的，我确定主机和端口是正确的。你能告诉我你是如何解决这个问题的吗？我在使用这个软件方面非常新手。我只学习了大约3天。 - crystal

Answer 1

集成安全设置

要使用集成安全连接数据库，需要执行以下步骤：

• 在https://www.microsoft.com/en-us/download/details.aspx?id=11774上下载Microsoft JDBC驱动程序4.2
• 将包解压缩到临时目录中
• 关闭Spoon

• 将“<temp directory>\sqljdbc_4.2\enu\auth\x86\sqljdbc_auth.dll”复制到“C：\ Program Files（x86）\ Java \ jre1.8.0_73 \ bin”（32位）

• 将“<temp directory>\sqljdbc_4.2\enu\sqljdbc42.jar”复制到“<Kettle installation folder>\data-integration\lib”
• 打开Spoon
• 在Spoon中测试连接
• 删除临时目录

如果升级Kettle、Java、JDBC驱动程序至更高版本，则必须重复上述步骤。

- Bart VdA

---

在一个独立于版本的文件夹中安装Java可以避免每个Java更新都复制文件。 - Bart VdA

也适用于 Microsoft JDBC Driver for SQL Server 6 和 8，只是文件夹结构略有不同。https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 - Bart VdA

我认为 'sqljdbc_auth.dll' 不需要放在'C：\ Program Files（x86）\ Java \ jre1.8.0_73 \ bin'。我认为它需要放在 Pentaho 的文件夹中，即：'...\ data-integration \ libswt \ win64'。 - Der U

Answer 2

以下是我为您翻译的内容，涉及到IT技术：

以下是我使用的方法将Pentaho PDI连接到Microsoft SQL Server：

1. 从此处下载官方Microsoft JDBC Driver for SQL Server（v4.0）。

2. 它附带两个jar文件，将文件“sqljdbc4.jar”复制到路径data-integration\lib中。

3. 重新启动Pentaho并尝试再次连接MS SQL server(Native)。

注意：我使用这种方法来处理Pentaho PDI（Kettle），但它应该适用于套件中的其余产品。

注意2：即使您使用Windows，您也可以下载“.tar.gz”文件而不是“.exe”，因为您只是用它来处理Pentaho。

- Watchmaker

Answer 3

因为以上没有一个完全匹配，所以我提供我的解决方案。按照这篇文章的建议，我获取了sqljdbc_auth.dll并将其放入data-integration\libswt\win64目录中，然后启动Spoon。之后我成功进行了认证。

- Daniel Hudsky

Answer 4

若要使SQL Server的"Inregratedsecurity"身份验证功能正常工作，您必须将sqljdbc_auth.dll复制到BA服务器的"~\tomcat\bin"位置，并重新启动BA服务器。

希望这能帮到您！

- armin

Answer 5

我不知道您是否已经按照这些步骤进行操作，但我想要提到的是：

1>首先在tomcat->lib中添加ms sql server的jdbc驱动程序。 sqljdbc4.jar

请查看下面的图片

希望对您有所帮助。

- Avyaan

---

我已经按照你展示的步骤进行了操作，但是仍然连接失败。你有其他建议吗？ - crystal

我需要在服务器上安装Tomcat吗？ - crystal

Answer 6

将"sqljdbc41.jar"添加到\Pentaho\java\bin，并将"sqljdbc_auth"（32位和64位版本的差异）添加到\Pentaho\design-tools\data-integration\lib

- Raj Kamuni

Answer 7

我在Mac上遇到了这个问题。

默认情况下，当您解压缩存档文件时，“根”目录是data-integration。我把它移到了一个名为“Pentaho”的目录下 - 这是一件小事，但不是很清楚 - 您希望JDBC驱动程序位于Pentaho使用的任何ROOT目录的lib子目录中。

我使用了最新的JDBC驱动程序 - sqljdbc42.jar - 它很好用 - 在AWS RDS上的Aurora DB使用mariadb-java-client-2.1.0.jar也很好用。

- JFM

Answer 8

按照Bart VdA的步骤操作，然后进入Pentaho，选择文件、新建、转换。进入预览选项卡，选择转换，点击连接，新建连接。按照下面图片中显示的默认SQL服务器设置进行输入。

- Alexandre Neukirchen

Answer 9

好的，这让我忙了一段时间。以下是对我有效的方法：

系统信息：

• win10
• PDI社区版9.4版本
• SQL Server 2019
• Java安装1：jre1.8.0_361
• Java安装2：jdk1.8.0_361

下载：

• 驱动程序包“sqljdbc_6.0.8112.200_enu.tar.gz”来自 https://www.microsoft.com/en-us/download/details.aspx?id=11774 (随着时间的推移，确切的名称可能会更改，但URL似乎不变)

操作：

1. 关闭Pentaho/Spoon。
2. 解压缩文件“sqljdbc_6.0.8112.200_enu.tar.gz”。这将给您一个带有子文件夹和各种文件的文件夹。
3. 将该文件： “sqljdbc_6.0.8112.200_enu.tar\sqljdbc_6.0\enu\auth\x64\sqljdbc_auth.dll” 复制到此位置： “...\data-integration\libswt\win64”
4. 将该文件： “sqljdbc_6.0.8112.200_enu.tar\sqljdbc_6.0\enu\jre8\sqljdbc42.jar” 复制到此位置： “...\data-integration\lib”
5. 启动Spoon。

希望这对您有帮助。

感谢其他发布者。

- Der U