Connect Pentaho to MS SQL Server (Native)

Question

I am very new in using PENTAHO. I need to connect this software to MS SQL Server (Native). I already try to connect it many time but every time I test the connection, failed. This is the error message that occur every time I test the connection.

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

Can anyone help me to solve this problem? I really need your help. TQ

Answer 1

Integrated Security Setup

To connect to database with integrated security, following steps should be done:

• Download Microsoft JDBC Drivers 4.2 on https://www.microsoft.com/en-us/download/details.aspx?id=11774
• Unzip the package in a temp directory
• Close Spoon

• Copy '<temp directory>\sqljdbc_4.2\enu\auth\x86\sqljdbc_auth.dll' to 'C:\Program Files (x86)\Java\jre1.8.0_73\bin' (32-bit)

• Copy '<temp directory>\sqljdbc_4.2\enu\sqljdbc42.jar' to '<Kettle installation folder>\data-integration\lib'
• Open Spoon
• Test connection in spoon
• Delete temp directory

If you upgrade Kettle, Java, JDBC Driver to a higher version, you have to repeat the above steps

Answer 2

Here is what worked for me to connect Pentaho PDI to a Microsoft SQL Server:

1. Download the official Microsoft JDBC Driver for SQL Server (v4.0) from here.

2. It comes bundled with two jar files, copy the file "sqljdbc4.jar" into your path data-integration\lib.

3. Restart Pentaho and try the connection MS SQL server(Native) again.

Note: I used this method for Pentaho PDI (Kettle), but it should work for the rest of products of the suite.

Note2: Even if you are using windows you can download the ".tar.gz" file instead of the ".exe" because you are using it just for Pentaho.

Answer 3

I dont know whether you have followed these steps or not.but what i want to mention ::

1>first add jdbc driver for ms sql server add in tomcat->lib. sqljdbc4.jar

find the below image

I hope it is helpful.