Pentaho DI Connection driver issues

Question

I'm having issues adding a MSSQL SERVER connection to Pentaho Data Intergration.

I tried the following:

I have downloaded the latest jdbc drivers (sqljdbc42) and moved them to the lib folder
I have created a new user in SQL SERVER and gave it all rights...didn't help.
very sure about username/password and login settings
reinstalled pentaho multiple times, reinstalled the jdbc drivers

I'm have a mac but use windows on parallels, and I work from this environment.

This is the error that I'm getting:

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

Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver) Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8

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

Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver) Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8

at org.pentaho.di.core.database.Database.normalConnect(Database.java:472) at org.pentaho.di.core.database.Database.connect(Database.java:370) at org.pentaho.di.core.database.Database.connect(Database.java:341) at org.pentaho.di.core.database.Database.connect(Database.java:331) at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80) at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2783) at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:597) 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:313) at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157) at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141) at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43) at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:137) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.eclipse.jface.window.Window.runEventLoop(Window.java:820) at org.eclipse.jface.window.Window.open(Window.java:796) at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:80) at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47) at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116) at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:60) at org.pentaho.di.ui.repository.repositoryexplorer.controllers.ConnectionsController.editConnection(ConnectionsController.java:363) 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:313) at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157) at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141) at org.pentaho.ui.xul.swt.tags.SwtButton.access$300(SwtButton.java:43) at org.pentaho.ui.xul.swt.tags.SwtButton$2.mouseUp(SwtButton.java:103) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.eclipse.jface.window.Window.runEventLoop(Window.java:820) at org.eclipse.jface.window.Window.open(Window.java:796) at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:80) at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47) at org.pentaho.di.ui.repository.repositoryexplorer.RepositoryExplorer.show(RepositoryExplorer.java:146) at org.pentaho.di.ui.spoon.Spoon$24$1.run(Spoon.java:4045) at org.eclipse.swt.widgets.RunnableLock.run(Unknown Source) at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Unknown Source) at org.eclipse.swt.widgets.Display.runAsyncMessages(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1366) at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:8022) at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9277) at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:692) 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.commons.launcher.Launcher.main(Launcher.java:92) Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver) Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:585) at org.pentaho.di.core.database.Database.normalConnect(Database.java:456) ... 62 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'DBklaartje'. ClientConnectionId:33c3d571-4128-4c2d-b5a6-1290e06438e8 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4346) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:561) ... 63 more

Hostname :DESKTOP-LM88EPQ Port :1433 Database name :Velo

score 1 · Accepted Answer · answered Oct 13 '17 at 09:02

Apparently this issue was caused because of an authentication issue with the driver. If you download your drivers (sqljdbc), also copy the file 'sqljbdc_auth.dll' to %PENTAHO_HOME%\design-tools\data-integration\libswt\win64

This will make sure your users and logins get the proper authorisation.

AlainD · Answer 2 · 2017-10-10T18:35:45.397

0

On the Database connection definition, between the Test and the Explore button, you have a Feature List which gives you all the parameters used by the PDI for that connection.

Check the the driver class on line 20 (sometime you have more than one on your classpath).

Then drop the url on line 21, in your SQL-Developer or equivalent.

You can also write your own java class ; replace the driver, url, username, password by the walue ayou are using; compile it; run it; it tell you what went wrong.

import java.sql.DriverManager;
public class TestConnection {
    public static void main(String[] args) throws Exception{
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        DriverManager.getConnection("url", "username", "password");
        System.out.println("Ok");
    }    
}

edited Oct 10 '17 at 18:35

answered Oct 10 '17 at 08:15

AlainD

6,187
3
17
31

The driver class is com.microsoft.sqlserver.jdbc.SQLServerDriver which I assume is correct. How, what or where do I drop the url? – Claire Oct 10 '17 at 09:19
Anywhere you can use a JDBC. You should have one in your SSMS studio. You have one in SQL-Developer or Squirrel-sql. Or you may write your own java class. Frankly, I usually use PDI to test connections, but it seams to be what you are cross-checking. – AlainD Oct 10 '17 at 18:23
problem is, I'm not familiar with JDBC at all. I just want to make a connection with Pentaho and my sql server so I can learn to make a data warehouse. – Claire Oct 11 '17 at 07:51
All I can say with the log you provided is that the issue is NOT coming from the PDI (nor the JDBC) but from your MS-SQL installation which does not want to authenticate the user DBklaartje (wrong password, not defined on the environment you think you did, firewall,...). – AlainD Oct 11 '17 at 08:07
Can you use the SQL-Server studio and issue a `select * from someTable` ? Then, you'll have to go through the doc here: https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server. However, to save you some reading, do you know how to compile and run the suggested java program? – AlainD Oct 11 '17 at 08:24
I was able to get over this error, and another error appeared ! `Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver) The TCP/IP connection to the host DESKTOP-LM88EPQ, port 1433 has failed. Error: "Connection refused: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".` I have setup my ports correctly I think...so not sure what's up :s – Claire Oct 12 '17 at 08:55
You are progressing, although we could have wished a little more precise info from Windows. Basically, microsoft thinks the issue comes from one of the three reason I mentioned 3 comments above. Boger. – AlainD Oct 12 '17 at 14:06
How do you connect to SQL-Server **without** PDI ? I guess you have a way to do a select or a create table somehow. – AlainD Oct 12 '17 at 14:09
I use SQL Server Manager – Claire Oct 13 '17 at 08:59
And I guess (stupid question but impotant to check) that you can connect to your database with SSMS. But, unless I am mislead, MMSM does not use the JDBC. – AlainD Oct 13 '17 at 19:29
Is it possible for you to test the java program to test the JDBC connection? – AlainD Oct 13 '17 at 19:31

Pentaho DI Connection driver issues

2 Answers2